Get Specific Columns Using “With()” Function in Laravel Eloquent
Solution 1
Well I found the solution. It can be done one by passing a closure
function in with()
as second index of array like
Post::query()
->with(['user' => function ($query) {
$query->select('id', 'username');
}])
->get()
It will only select id
and username
from other table. I hope this will help others.
Remember that the primary key (id in this case) needs to be the first param in the
$query->select()
to actually retrieve the necessary results.*
Solution 2
You can do it like this since Laravel 5.5:
Post::with('user:id,username')->get();
Care for the id
field and foreign keys
as stated in the docs:
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.
For example, if the user belongs to a team and has a team_id
as a foreign key column, then $post->user->team
is empty if you don't specifiy team_id
Post::with('user:id,username,team_id')->get();
Also, if the user belongs to the post (i.e. there is a column post_id
in the users
table), then you need to specify it like this:
Post::with('user:id,username,post_id')->get();
Otherwise $post->user
will be empty.
Solution 3
For loading models with specific column, though not eager loading, you could:
In your Post
model
public function user()
{
return $this->belongsTo('User')->select(['id', 'username']);
}
Original credit goes to Laravel Eager Loading - Load only specific columns
Solution 4
When going the other way (hasMany):
User::with(array('post'=>function($query){
$query->select('id','user_id');
}))->get();
Don't forget to include the foreign key (assuming it is user_id in this example) to resolve the relationship, otherwise you'll get zero results for your relation.
Solution 5
In Laravel 5.7 you can call specific field like this
$users = App\Book::with('author:id,name')->get();
It is important to add foreign_key
field in the selection.
Related videos on Youtube
![Awais Qarni](https://i.stack.imgur.com/B4V8I.jpg?s=256&g=1)
Awais Qarni
Full stack web developer having experience in multiple technologies and frameworks including PHP (Zend, Laravel, CakePHP), Ruby (ROR), Javascript (jQuery, Backbone.JS, Ember.JS).
Updated on December 23, 2021Comments
-
Awais Qarni over 2 years
I have two tables,
User
andPost
. OneUser
can have manyposts
and onepost
belongs to only oneuser
.In my
User
model I have ahasMany
relation...public function post(){ return $this->hasmany('post'); }
And in my
post
model I have abelongsTo
relation...public function user(){ return $this->belongsTo('user'); }
Now I want to join these two tables using
Eloquent with()
but want specific columns from the second table. I know I can use the Query Builder but I don't 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'....));
It only returns the column from the first table. I want specific columns using
with()
from the second table. How can I do that? -
Awais Qarni over 10 yearsBut this relation will make it just like hardcoded. In all condition it will always return me these two fields. It may happen that I need more fields in some other situations
-
user1669496 over 10 yearsThen you must use the query builder.
-
user1669496 over 10 yearsThat's odd, I couldn't get this to work. As soon as I added in
$query->select('id','username');
, I was gettingTrying to get property of non-object
-
Awais Qarni over 10 years@user1669496 Do you have defined relations
hasmany
andbelongsto
in your model?? -
justin over 10 yearsWeird! still returns all the fields of the user. @AwaisQarni
-
Awais Qarni over 10 years@justin share your query
-
justin over 10 years@AwaisQarni i will give you the laravel forum click here
-
Symen Timmermans about 10 yearsThank you for sharing this. Where did you discover this possibility? I didn't find this in the Laravel docs.
-
Awais Qarni about 10 years@SymenTimmermans Just trying different possibilities of Eloquent. :-)
-
Azirius about 10 yearsFor those who see this, remember that the primary key (
id
in this case) is necessary in the$query->select()
to actually retrieve the necessary results. I omitted this in my code and couldn't figure out why it wouldn't find any results. Silly me! Hope this helps anyone else with the same problem -
Jorge over 9 yearsThis is a very basic question, but how do I refer to a column from the second table if: $result = Post::with('user')->find(1), then how do I retrieve the user id? ---- $result->user.id ?
-
Amr over 9 years@jasa You can refer to a column from the second table ('users' in this example) like this: $result->user->id;
-
scipilot over 9 years@SymenTimmermans it's under laravel.com/docs/4.2/eloquent#eager-loading constraints. the API docs are pretty poor describing these parameters, and the examples are sometimes hard to find amongst the tutorial style documentation. (I much prefer reference style...)
-
Giovanni Far over 8 yearsit return a collection!
-
Giovanni Far over 8 yearsif you want an array then call
toArray()
!!!$user->favourites->lists('id')->toArray();
-
ErvTheDev over 8 yearsThe query will still get the other columns because the list() method just changes the results array ,so if you just need the 'id' from that table you may want to specify it in the query. It is always a good habit to keep performance in mind when doing queries. Enjoy coding!
-
aqingsao about 8 yearsYes, exactly, "Don't forget to include the foreign key (assuming it is user_id in this example)"
-
Sjwdavies almost 8 yearsReally? For me this just returns nothing, i.e. it breaks it?
-
BugHunterUK over 7 yearsThis should be the accepted answer. This is the correct way to do it.
-
Wallace Vizerra over 7 years-1
$user->favourites
will be returnCollection
with all fields selecteds. The correct use is:$user->favourites()->lists('id')
. -
Wallace Vizerra over 7 yearsSelecting everything to later use PHP filtering is a bad idea. It is best to use only the needed fields in Query. It's important to know the difference between
Query\Builder::lists
method andCollection::lists
method. -
Andre F. over 7 yearsIs there a way to do this in the Laravel 5.3 version? I think they changed it... again.... and now it returns null when I try with this approach
-
JordyvD over 7 yearsIt's been a while, but you could add a
$fields
parameter. -
danronmoon about 7 years@g3mini could you help write an answer on how that would work calling like
$model->user
without invocation? -
JordyvD about 7 yearsWithout invocation? So,
$model->relation->get(['field1', 'field2']);
That should work -
alimfazeli about 7 yearsyou should include column that defines your relationship in select fields, in this case user_id
-
Fenix Lam over 6 yearsI love this solution! I get trouble when I need to get a device detail by using specific device_id, and device activity in specific start time and end time, it works perfectly as I need!
-
Adam over 6 yearsYou can do it with a single command in Laravel 5 see my answer stackoverflow.com/a/47238258/2311074
-
hendra1 over 6 yearsdon't forget to add the foreign key field
-
Shahrukh Anwar almost 6 yearsNice catch brother.
-
Rajesh Vishnani over 5 yearsDont forget to notice @hendra1's comment, all foreign key fields are also necessary along with the primary key, otherwise you will get blank collection. Dude saved my time. Thanks
-
Hashaam Ahmed over 5 yearsawesome bro, that use of foreign key is really important, lucky i saw your ans otherwise I would have scratched my head for hours lol. Thanks man!
-
Hashaam Ahmed over 5 yearsDon't forget to include the foreign key (assuming it is post_id here) to resolve the relationship, otherwise you will get empty results for your relation.
-
Hashaam Ahmed over 5 yearsgreat, just need to add foreign key, over here its post_id otherwise result will be empty. Mentioning Foreign key is important here. Thanks :)
-
barbsan about 5 yearsThis is the same syntax as in this answer (just different names for model and column)
-
Graham over 4 yearsThis should really be the selected answer. Works like a charm :)
-
Clemorphy over 4 yearsJust like @HashaamAhmed I needed to include the foreign key too. If I don't, nothing is returned.
-
Sodium over 4 years@Adam, This will restrict columns in the child table, how can I restrict columns from table Parent table along with child table?
-
Adam over 4 years@GauravGenius everything you want to restrict from parant belogns in the
get()
methodPost::with('user:id,username')->get(['age', 'color']);
-
pankaj kumar over 4 yearsIt is not Good idea because if any day if you want full column then you can not get form this. every time you have to modify this.
-
Adsy2010 about 4 yearsThis returns null for me but i didn't know this was possible before! Ok, so I posted prematurley but I did find a result. To use this method (looks much cleaner) you must include the relationship link, for example the ID column when pulling the with data. Without this specifier, you get a null return.
-
Haritsinh Gohil about 4 yearsyeah @Adsy2010, for getting related relationship data you also have to get id column or the primary key or whichever id is responsible for that relationship.
-
Lokendra Parihar about 3 yearsIt is good but, this keys also sent in API response in JSON how to avoid
-
Adam about 3 years@Kamlesh
Post::with(['user:id,username,team_id', 'othertable:id,horse'])
-
Haritsinh Gohil about 3 years@Kamlesh
Post::with('user:user.id,username')->get();
this code will return all fields of post model or table and onlyid
andusername
field from theusers
table but keep in mind before usingwith()
function you should have relationship to relevant table in your model, if you don't have relationship then you can use join from the query builder. -
Kamlesh about 3 years@Adam its weird answer for me because I want to fetch all the records of user table and need 2 fields of other table. I do not want to write all the fields in query as you suggested in your answer. Thanks dear.
-
Adam about 3 years@Kamlesh then go with
Post::with(['user', 'othertable:id,horse'])
-
Shailendra about 3 yearscan I put specific conditions like this return $this->belongsTo('User')->select(['id', 'username'])->where('id', '=', 1); or something like this
-
Diego Favero almost 3 yearsyou deserve a hug !
-
Waleed Khaled almost 3 yearsoh thanks bro :) , you really really saved my time
-
Nipun Tharuksha over 2 years@Azirius YOU ARE A LIFE SAVE.Thanks
-
S. Farooq over 2 years@Sjwdavies you need to add the foreign key in the select as well
-
Imran_Developer over 2 yearscan you help with this, it returns me product name but not "user" object: $product = Product::where('id', $p_id)->with('user:id,name')->get(['name']);
-
Imran_Developer over 2 years@Adam
Post::with('user:id,username')->get(['age', 'color']);
returns me post with age and color columns but user object is null -
Imran_Developer over 2 yearswow, great @jwarshaw, this is what actually I was looking for, this "user_id" really works for me.
-
Neeraj Tangariya over 2 years@Imran_Developer because you returning name only in get(['name']). do this $product = Product::with('user:id,name')->where('id', $p_id)->get();
-
Imran_Developer over 2 yearsyes dear @Neeraj , but it returns me user object with all columns of product instead of 1 or 2. but I have solved this by using:
$product = Product::with('user:id,name')->where('id', $p_id)->select('title', 'content', 'user_id')->get();
. Here "user_id" is important for reference. -
Adam over 2 years@Imran_Developer whats the primary key on your users table? Maybe
Post::with('user:id,username')->get(['age', 'color', 'id']);
works? -
Imran_Developer over 2 yearsyes, basically "user_id" was missing. It works now:
Post::with('user:id,username')->get(['age', 'color', 'user_id']);
-
Neeraj Tangariya over 2 years@Imran_Developer ok great.