select specific columns from first table with eager loading in Laravel

Question

I have checked many solutions to get specific columns from first model with eager loading but nothing works in my case.

for example: I want specific columns from model User and want to get relationship data with eager loading without any join.

$users= User::select('name') // get one column from user table
->with(array('role_user' => function($query){
    $query->select('role_name'); // and select one column from pivot table
}))
->paginate(3);

when I don't use User::select('name), it returns relationship data with eager load when I use select, it returns empty array.

How I can get specific columns from both tables using eager loading

You should always also select the primary and foreign keys when using eager loading — JEJ
– JEJ, Commented Jul 27, 2021 at 7:44

shaedrich · Accepted Answer · 2021-07-27 07:49:58Z

3

I don't exactly know, how you defined your relationship but Laravel has some weird behavior: You should always also select the primary and foreign keys:

$users= User::select(['id', 'name']) // get one column from user table
->with(array('role_user' => function($query){
    $query->select(['id', 'role_name']); // and select one column from pivot table
}))
->paginate(3);

You can simplify it to this:

$users= User::select(['id', 'name']) // get one column from user table
->with(['role_user:id,user_id,role_name']) // and select one column from pivot table
->paginate(3);

edited Jul 27, 2021 at 7:49

answered Jul 27, 2021 at 7:43

shaedrich

5,7633 gold badges31 silver badges47 bronze badges

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

4 Comments

Sajjad Ali Over a year ago

it is also returning empty array in relations but (id, name) columns from User model

shaedrich Over a year ago

Can you edit your question and add the schema of the two tables plus the relationship from the model? You probably need to add user_id to the select() inside with()

Imran Hossain Over a year ago

@shaedrich thanks! it worked. foreign key is required

Jaquarh Over a year ago

If you're reading this in the future, checkout the weird behaviour. This answer addresses this issue well and resolved my issue.

JEJ · Accepted Answer · 2021-07-27 07:56:21Z

0

We Never Know the schemas and the relationships of your project.. But I assume You This Should Work

$users = User::select('id','name') 
               ->with(['role_user:id,user_id,role_name'])
               ->paginate(3);

In your User Model Relationships must be

public function role_user(){
return $this->belongsToMany(Role::class, 'role_user'); // pivot table name
}

In your Role Model

 public function users(){
return $this->belongsToMany(User::class, 'role_user'); // pivot table name
}

answered Jul 27, 2021 at 7:56

JEJ

9248 silver badges22 bronze badges

Collectives™ on Stack Overflow

select specific columns from first table with eager loading in Laravel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related