Laravel - Fetching selected columns in relationships [duplicate]

Question

I've been trying to fetch only selected columns using laravel relationship. Main tables:

posts
- id
- author_id
- content
post_media
- id
- post_id
- link
- caption

In my laravel Post model, I've defined relationship like,

public function postMedia()
{
    return $this->hasMany(PostMedia::class);
}

And while fetching data, the query is:

Post::select('id', 'content')
 ->with('postMedia:id,link')
 ->where('author_id', '=', auth()->user()->id)
 ->paginate();

But the query returns empty post_media array, even after replacing paginate() with get() I also tried,

Post::select('id', 'content')
 ->with(['postMedia' => function ($query) {
  $query->select('id', 'link')
 })
 ->where('author_id', '=', auth()->user()->id)
 ->paginate();

But still didn't work. Am I missing something? Thank you.

lagbox · Accepted Answer · 2019-11-30 06:01:58Z

2

You need to be selecting the key the relationship uses or it won't be able to match the records back to the parent, in this case you need to also select post_id, the foreign key:

Post::select('id', 'content')
    ->with('postMedia:id,link,post_id')
    ->where('author_id', '=', auth()->user()->id)
    ->paginate();

"When using this feature, you should always include the id column and any relevant foreign key columns in the list of columns you wish to retrieve."

Laravel 6.x Docs - Eloquent - Relationships - Eager Loading - Eager Loading Specific Columns

answered Nov 30, 2019 at 6:01

lagbox

50.7k8 gold badges76 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Swapnil Bhikule Over a year ago

This trick did work for me, except without short form like post_media:id,post_id,link, this one gave me an SQL error saying unknown column post_media.post_id. So I had to use the long-form of using the closure to select columns.

lagbox Over a year ago

what was the name of the foreign key on post_media?

Amit Senjaliya · Accepted Answer · 2019-11-30 05:22:23Z

-1

Your query should be like this:

$posts = Post::with(['postMedia' => function($query) {
             return $query->select(['id', 'link']);
         }])
         ->where('author_id', auth()->user()->id)
         ->select('id', 'content')
         ->get();

dd($posts)

edited Nov 30, 2019 at 5:22

answered Nov 30, 2019 at 5:01

Amit Senjaliya

2,9651 gold badge12 silver badges24 bronze badges

3 Comments

Swapnil Bhikule Over a year ago

foreign_key will be automatically evaluated based on the table name. If I'm taking all values using ->with('postMedia'), it works, but it doesn't work when I try to fetch specific columns from that table.

Amit Senjaliya Over a year ago

@SwapnilBhikule Ok sure. Any error happens when try to fetch specific columns?

Amit Senjaliya Over a year ago

@SwapnilBhikule I have updated answer please check it.

Collectives™ on Stack Overflow

Laravel - Fetching selected columns in relationships [duplicate]

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related