Load specific columns from eager loaded laravel collection

Question

Consider books and authors relationship. Every book belongsTo an author and 1 author hasMany books. Books table has fields like (Id,Title,Version etc) and authors have fields like (Id,Name,Address etc). Note DB columns do not follow the default laravel naming convention.

Now; I want to fetch some fields from both tables. More speficic, I want title,version,name and address fields. This is desired output:

    {
        Title: "Oxford Maths",
        Version: "2.5",
        author: "John Doe",
    }

1st trial:

return Book::with('author')->get()->map(function ($book) {
    return collect($book)->only(
        [
            'Title',
            'Version',
            "author"
        ]);
});

1st Trial Output:

    {
        Title: "Oxford Maths",
        Version: "2.5",
        author: {
            Id: 1,
            Name: "John Doe",
            Address: "Tanzania",
            deleted_at: null
        }
    }

2nd trial: Tried this;

return Book::with([
        'author' => function($query){
            $query->select('Name','Id');
        }
    ])->get()->map(function ($data) {
    return collect($data)->only(
        [
            'Title',
            'Version',
            "author"
        ]);
});

And this;

return Book::with('authority:name,Id')->get()->map(function ($data) {
    return collect($data)->only(
        [
            'Title',
            'Version',
            "author"
        ]);
});

2nd Trial output:

    {
        Title: "Oxford Maths",
        Version: "2.5",
        author: {
            Name: "John Doe",
            Id: 1
        }
    }

3rd Trial:

return Book::with([
        'author' => function($query){
            $query->select('Name'); // *Removed foreignKey*
        }
    ])->get()->map(function ($data) {
    return collect($data)->only(
        [
            'Title',
            'Version',
            "author"
        ]);
});

3rd Trial output:

    {
        Title: "Oxford Maths",
        Version: "2.5",
        author: null
    }

What can I do to get the desired output?

Book::with('author : title, version, name , address, author_id')->get(); Does this help ? — PSA, Jul 21 '21 at 08:22
https://laravel.com/docs/8.x/eloquent-relationships#eager-loading-specific-columns — PSA, Jul 21 '21 at 08:37

shaedrich · Answer 1 · 2021-07-21T09:19:48.527

3

Of course, you can do it afterwards on the collection but it makes more sense to do it directly on the query. You try to reflect that in your 3rd trial. However, in my opinion that's were Laravel behaves a bit odd. In order to do the eager loading, you need your foreign key. Even though, that's logical, it's human to forget about that nevertheless. But if you forget to include the foreign key, Laravel doesn't tell you this and instead returns null. So make sure, all necessary primary and foreign keys are always included in your relation.

return Book::with([
    'author' => function($query){
        $query->select('id', 'Name'); // *Removed foreignKey*
    }
])->get([ 'title' , 'version' ]);

One thing, I like to do when a relation should mostly return only one of its values:

class Author extends Model
{
    public function __toString()
    {
        return $this->Name;
    }
}

To make this automatically serialized this way, you can change __toString() to jsonSerialize()

class Author extends Model implements JsonSerializable
{
    public function jsonSerialize()
    {
        return $this->Name;
    }
}

edited Jul 21 '21 at 09:19

answered Jul 21 '21 at 08:28

shaedrich

5,457
3
26
42

I have tried this. However am getting exactly 3rd trial output. How can I add author_name here? ```->get([ 'title' , 'version' ]);``` – Mussa Moses Jul 21 '21 at 08:38
Is `author_name` a column or an accessor or none yet? – shaedrich Jul 21 '21 at 08:49
I meant the name of the author as in desired output. **author_name** is not a column rather the **name** is a column in authors table. – Mussa Moses Jul 21 '21 at 09:04
For some reasons am getting the same result when using this approach. I decided to go with lazy loading approach rather than eager-loading. See my answer. – Mussa Moses Jul 21 '21 at 10:11

score 2 · Answer 2 · edited Jul 21 '21 at 09:20

Maybe it will help you

in Model

protected $appends = [
    'author_name', 'address'
];

protected $fillable = [
    'title',
    'version',
];

public function author(){
    return $this->belongsTo(Author::class);
}

public function getAuthorNameAttribute(){
    return $this->author->name;
}

public function getAddressAttribute(){
    return $this->author->address;
}

and in controller use this

return Book::query()->with('author')->first()->makeHidden(['author','author_id']);

Output:

{
  "id": 1,
  "title": "book",
  "version": "v1",
  "created_at": null,
  "updated_at": null,
  "author_name": "komeyl",
  "address": "Iran"
}

Thanks for your contribution but could you please add code as text and not as an image to ensure that your answer is still helpful even if the image is deleted? — shaedrich, Jul 21 '21 at 09:10

score 0 · Answer 3 · answered Jul 21 '21 at 10:03

I ended up using trick suggested by @komeyl-dev. This is sample code:

//Book.php
class Book extends Model
{
    protected $appends = ['author_name']; // appended so I can access it as json

    public function author(){
        return $this->belongsTo(Author::class,'author_id');
    }
    
    public function getAuthorNameAttribute(){
        return $this->author->name;
    }
}

//Testing in web.php (Note: No eager loading here)
Route::get('url', function () {
    return Book::get()->map(function ($data) {
        return collect($data)->only(
            [
                'Title',
                'Version',
                "author_name"
            ]);
    });
});

Thank you @shaedrich and @komeyl-dev

Load specific columns from eager loaded laravel collection

3 Answers3