Get specific columns using the "With ()" function in Laravel Eloquent

Question

I have two tables: User and Post . One User can have many posts and one post belongs to only one user .

In my User model, I have a hasMany relationship ...

 public function post(){ return $this->hasmany('post'); } 

And in my post model, I have belongsTo relation ...

 public function user(){ return $this->belongsTo('user'); } 

Now I want to combine these two tables using Eloquent with() but I want the specific columns from the second table. I know that I can use Query Builder, but I do not want to.

When in the Post model I write ...

 public function getAllPosts() { return Post::with('user')->get(); } 

It runs the following queries ...

 select * from 'posts' select * from 'users' where 'users'.'id' in (<1>, <2>) 

But what I want is ...

 select * from 'posts' select id,username from 'users' where 'users'.'id' in (<1>, <2>) 

When i use ...

 Post::with('user')->get(array('columns'....)); 

Returns only the column from the first table. I want specific columns using with() from the second table. How can i do this?

Answer 1

Well, I found a solution. This can be done by passing the closure function to with() as the second index of the array, for example

 Post::with(array('user'=>function($query){ $query->select('id','username'); }))->get(); 

It will select only id and username from another table. I hope this helps others.

---

Remember that the primary key (in this case, the identifier) ​​must be the first parameter in $ query-> select () to actually get the necessary results. *

Answer 2

In your Post model

 public function user() { return $this->belongsTo('User')->select(array('id', 'username')); } 

Original credit sent by Laravel Eager Loading - Loading only certain columns

Answer 3

You can do it like this with Laravel 5.5:

 Post::with('user:id,username')->get(); 

Take care of the id field and foreign keys as indicated in the docs :

When using this function, you should always include the id column and any corresponding foreign key columns in the list of columns that you want to get.

For example, if the user belongs to the team and has team_id as the foreign key column, then $post->user->team will be empty if you do not specify team_id

 Post::with('user:id,username,team_id')->get(); 

Answer 4

When going the other way (hasMany):

 User::with(array('post'=>function($query){ $query->select('id','user_id'); }))->get(); 

Remember to include the foreign key (assuming that in this example it is user_id) in order to resolve the relationship, otherwise you will get a null result for your relationship.

Answer 5

In Laravel 5.7, you can invoke a specific field as follows

 $users = App\Book::with('author:id,name')->get(); 

It is important to add the foreign_key field to the selection.

Answer 6

In your Post model:

 public function userWithName() { return $this->belongsTo('User')->select(array('id', 'first_name', 'last_name')); } 

Now you can use $post->userWithName

Answer 7

I came across this problem, but with a second layer of related objects. @Awais Qarni's answer comes with the inclusion of the corresponding foreign key in the nested select statement. Just as an identifier is required in the first nested select statement to reference a related model, a foreign key is needed to refer to the second degree of related models; in this example, a company model.

 Post::with(['user' => function ($query) { $query->select('id','company_id', 'username'); }, 'user.company' => function ($query) { $query->select('id', 'name'); }])->get(); 

In addition, if you want to select specific columns from the Post model, you will need to include the user_id column in the select statement to reference it.

 Post::with(['user' => function ($query) { $query->select('id', 'username'); }]) ->select('title', 'content', 'user_id') ->get(); 

Answer 8

Note that if you only need one column from the table, then using lists is good. In my case, I get the user's favorite articles, but I only need the article IDs:

 $favourites = $user->favourites->lists('id'); 

Returns an array of identifiers, for example:

 Array ( [0] => 3 [1] => 7 [2] => 8 ) 

Answer 9

You can also specify columns on a linked model while accessing it.

Post::first()->user()->get(['columns....']);

Answer 10

Now you can use the pluck method for the Collection instance:

This will only return the uuid Post model attribute

 App\Models\User::find(2)->posts->pluck('uuid') => Illuminate\Support\Collection {#983 all: [ "1", "2", "3", ], } 

Answer 11

Try with the conditions.

 $id = 1; Post::with(array('user'=>function($query) use ($id){ $query->where('id','=',$id); $query->select('id','username'); }))->get(); 

Answer 12

When you establish the relationship in the model, try this

 $postdata = Post::with('user')->get(); $posdata = $postdata->map(function(posts){ return collection([ ............ ........... 'user' => $posts->user->map(function($user){ 'id' => $user->id, 'username' => $user->username }) ]) }) 

Answer 13

 EmployeeGatePassStatus::with('user:id,name')->get();