Get Specific Columns Using “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 relation...

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

And in my post model I have a belongsTo 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?

Colorist

This might be obvious to everyone else but me, but on all these answers, you NEED to include the relationship's primary key (usually id) as well as any other column, or else your relationship will just return null. Took me a minute to figure this one out. – Imp

Answer 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.*

Colorist

That's odd, I couldn't get this to work. As soon as I added in $query->select('id','username');, I was getting Trying to get property of non-object – Heave

@Heave Do you have defined relations hasmany and belongsto in your model?? – Colorist

Weird! still returns all the fields of the user. @AwaisQarni – Popery

@Popery share your query – Colorist

@AwaisQarni i will give you the laravel forum click here – Popery

Thank you for sharing this. Where did you discover this possibility? I didn't find this in the Laravel docs. – Disfavor

@SymenTimmermans Just trying different possibilities of Eloquent. :-) – Colorist

For 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 – Emaemaciate

This 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 ? – Midtown

@jasa You can refer to a column from the second table ('users' in this example) like this: $result->user->id; – Glut

@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...) – Arris

I 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! – Planimetry

You can do it with a single command in Laravel 5 see my answer https://mcmap.net/q/98064/-get-specific-columns-using-with-function-in-laravel-eloquent – Circumcision

great, just need to add foreign key, over here its post_id otherwise result will be empty. Mentioning Foreign key is important here. Thanks :) – Coadjutor

Just like @HashaamAhmed I needed to include the foreign key too. If I don't, nothing is returned. – Trichloride

It is good but, this keys also sent in API response in JSON how to avoid – Alleyne

Important!!! needs to include the foreign key (assuming it is user_id in this example). Otherwise it will show null array. – Feuillant

It still causes problems when trying to select columns from the original model. Using joins solves this and joins are always faster than ORM – Stella

Answer 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.

Circumcision

Don'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. – Coadjutor

This should really be the selected answer. Works like a charm :) – Bostow

@Adam, This will restrict columns in the child table, how can I restrict columns from table Parent table along with child table? – Hammel

@GauravGenius everything you want to restrict from parant belogns in the get() method Post::with('user:id,username')->get(['age', 'color']); – Circumcision

@Kamlesh Post::with(['user:id,username,team_id', 'othertable:id,horse']) – Circumcision

@Circumcision 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. – Leandro

@Leandro then go with Post::with(['user', 'othertable:id,horse']) – Circumcision

@Circumcision Post::with('user:id,username')->get(['age', 'color']); returns me post with age and color columns but user object is null – Zealot

@Zealot whats the primary key on your users table? Maybe Post::with('user:id,username')->get(['age', 'color', 'id']); works? – Circumcision

yes, basically "user_id" was missing. It works now: Post::with('user:id,username')->get(['age', 'color', 'user_id']); – Zealot

I find this as a better solution, but, note that if you have space in between like this: Post::with('user:id, username, post_id')->get(); it will throw error. – Pasticcio

Also, this doesn't work on hasManyThrough or hasOneThrough relationships – Pasticcio

Answer 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

Heave

But 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 – Colorist

Then you must use the query builder. – Heave

This should be the accepted answer. This is the correct way to do it. – Ceramics

Is 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 – Postboy

It's been a while, but you could add a $fields parameter. – Chong

@g3mini could you help write an answer on how that would work calling like $model->user without invocation? – Swob

Without invocation? So, $model->relation->get(['field1', 'field2']); That should work – Chong

It 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. – Jessalin

can I put specific conditions like this return $this->belongsTo('User')->select(['id', 'username'])->where('id', '=', 1); or something like this – Julie

Answer 4

When going the other way (hasMany):

User::with(['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.

Cur

Yes, exactly, "Don't forget to include the foreign key (assuming it is user_id in this example)" – Thornberry

you should include column that defines your relationship in select fields, in this case user_id – Predella

Nice catch brother. – Transistorize

awesome 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! – Coadjutor

This code work for me thank you. – Huda

aha, i missed that important foreign key , and now this code is working – Starstudded

Answer 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.

Bevy

don't forget to add the foreign key field – Bevy

Dont 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 – Arioso

Answer 6

If you want to get specific columns using with() in laravel eloquent then you can use code as below which is originally answered by @Adam in his answer here in response of this same question, the answer's main code is as below :

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

So i have used it in my code but it was giving me error of 1052: Column 'id' in field list is ambiguous, so if you guys are also facing same problem

Then for solving it you have to specify table name before the id column in with() method as below code:

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

Clod

This 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. – Freese

yeah @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. – Clod

@Leandro Post::with('user:user.id,username')->get(); this code will return all fields of post model or table and only id and username field from the users table but keep in mind before using with() 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. – Clod

Answer 7

I came across this issue but with a second layer of related objects. @Awais Qarni's answer holds up with the inclusion of the appropriate foreign key in the nested select statement. Just as an id is required in the first nested select statement to reference the related model, the foreign key is required to reference the second degree of related models; in this example the Company model.

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

Additionally, if you want to select specific columns from the Post model you would need to include the user_id column in the select statement in order to reference it.

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

Merlynmermaid

wow, great @jwarshaw, this is what actually I was looking for, this "user_id" really works for me. – Zealot

Answer 8

In your Post model:

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

Now you can use $post->userWithName

Effrontery

Really? For me this just returns nothing, i.e. it breaks it? – Criminality

you deserve a hug ! – Aeriform

@Criminality you need to add the foreign key in the select as well – Karttikeya

Answer 9

There is another alternative you can eager load specific columns

public function show(Post $post)
{
    $posts = $post->has('user')->with('user:id,name,email,picture')->findOrFail($post->id);
    return view('your_blade_file_path',compact('posts);
}

In your Post model you should have user relationship also

public function user()
{
    return $this->belongsTo( User::class, 'user_id')->withDefault();
}

Note: It is mentioned in Laravel docs.

https://laravel.com/docs/8.x/eloquent-relationships#eager-loading-specific-columns

Feltie

can 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']); – Zealot

@Zealot because you returning name only in get(['name']). do this $product = Product::with('user:id,name')->where('id', $p_id)->get(); – Feltie

yes 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. – Zealot

@Zealot ok great. – Feltie

Answer 10

Note that if you only need one column from the table then using 'lists' is quite nice. In my case i am retrieving a user's favourite articles but i only want the article id's:

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

Returns an array of ids, eg:

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

Topdrawer

it return a collection! – Villiers

if you want an array then call toArray() !!! $user->favourites->lists('id')->toArray(); – Villiers

The 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! – Infirmary

-1 $user->favourites will be return Collection with all fields selecteds. The correct use is: $user->favourites()->lists('id'). – Annoyance

Selecting 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 and Collection::lists method. – Annoyance

Answer 11

If you use PHP 7.4 or later you can also do it using arrow function so it looks cleaner:

Post::with(['user' => fn ($query) => $query->select('id','username')])->get();

Redstart

Answer 12

I faced the same issue while using belongsToMany relationship with my user model (Laravel 8.x.x).

After a long search and trial and test method. I found out this answer

You have to make sure you are selecting the id's and any foreign keys that would be needed for the relationship from either side of that relationship. This allows Eloquent to match up parents to their children.

Original credit goes to https://mcmap.net/q/100367/-laravel-select-specific-columns-with-eloquent-while-eager-loading

So I included

Groups::select('groupid')
...

And it worked like a charm. Although now I want to know how to hide the groupid field after fetching. I know I can simply loop through the array and remove it. But is there any other method? potentially a simpler and better one.

Swaine

oh thanks bro :) , you really really saved my time – Bobby

Answer 13

You can also specify columns on related model at the time of accessing it.

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

Supply

Answer 14

You can try this code . It is tested in laravel 6 version.

Controller code

 public function getSection(Request $request)
{

  Section::with(['sectionType' => function($q) {
      $q->select('id', 'name');
  }])->where('position',1)->orderBy('serial_no', 'asc')->get(['id','name','','description']);
  return response()->json($getSection);
}

Model code

public function sectionType(){
    return $this->belongsTo(Section_Type::class, 'type_id');
}

Wedge

Answer 15

Be careful that if you don't add the key column(s) it won't return anything. If you want to show only the username without the id you could instead define the $visible/$hidden properties within the Model, like so:

app/Models/User.php

protected $visible = ['username'];

Then it will retrieve only username column with:

Post::with('user')->get();

Hiding the key columns:

Alternatively you could hide the key column(s) and then retrieve only the columns you wish.

app/Models/User.php

protected $hidden = ['id'];

Specify which columns you want including the key or else it won't return anything, but this will actually only return the username, because id is $hidden.

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

Dasie

Answer 16

Now you can use the pluckmethod on a Collection instance:

This will return only the uuid attribute of the Post model

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

Villiers

Answer 17

Try with conditions.

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

Off

Answer 18

So, similar to other solutions here is mine:

// For example you have this relation defined with "user()" method
public function user()
{
    return $this->belongsTo('User');
}
// Just make another one defined with "user_frontend()" method
public function user_frontend()
{
    return $this->belongsTo('User')->select(array('id', 'username'));
}

// Then use it later like this
$thing = new Thing();
$thing->with('user_frontend');

// This way, you get only id and username, 
// and if you want all fields you can do this

$thing = new Thing();
$thing->with('user');

Simon

Answer 19

Sometimes, when creating a small application based on (a subset of) an existing database, it may be convenient to have all models fetch only the fields you need; even when loaded by with(). It may ease debugging and avoid confusion, to name an advantage. The '$visible' attribute (mentioned above) can only be used when the model is converted to array or JSON; see the Laravel docs. Otherwise, you could use a combination of local scopes and a 'public static $fields' property, as in the simplified 'User Class' example below.

class User extends Model
{
    public static $fields = [ 'id', 'full_name', 'image' ];
    
    public function scopeFields(Builder $query) : void {
        $query->select(self::$fields);
    }
    
    public function posts(): hasMany {
        return $this->hasMany(Post::class)->select(Post::$fields);
    }
}

Let's use the well-known class Post too:

class Post extends Model
{
    public static $fields = [ 'id', 'user_id', 'text', 'created_at' ];
    
    public function scopeFields(Builder $query) : void {
        $query->select(self::$fields);
    }

    public function user(): HasOne {
        return $this->hasOne(User::class)->select(User::$fields);
    }
}

As you can see, the functions scopeFields are identical, so you should put that in an in-between class. Anyway, after this you can use the models like:

User::fields()->with('posts')->get();
Post::fields()->with('user')->get();

All the returned objects will only contain the attributes that are listed in $fields. Tested with Laravel 10.

Hexapla

Answer 20

So this was my old code which is working fine but not as expected

$query = Book::withCount('bookReviews')
            ->withAvg('bookReviews', 'rating')
            ->with('createdBy:id,name')
            ->orderBy($sortField, $sortOrder);

meaning i am getting result like this

..................
{
    "id": 31,
    .....
    "created_by": {
        "id": 12,
        "name": "Darwin Grady"
    },
    .....
},
..................

But i only wanted to get the name of the created by user so i had to change it to

$query = Book::withCount('bookReviews')
            ->withAvg('bookReviews', 'rating')
            ->addSelect(['createdBy' => User::select('name')->whereColumn('id', 'books.created_by')])
            ->orderBy($sortField, $sortOrder);

Now this is the expected result

..................
{
    "id": 31,
    .........
    "createdBy": "Darwin Grady"
},
..................

Check this out : Advanced Subqueries

Cartomancy

Answer 21

you must add foreign key in select

for example:

   public function expert(): HasOne
{
    return $this->hasOne(User::class, 'id', 'expert_id')
        ->select([
            'id',
            'name',
            'family',
            'image',
        ]);
}

Monge

Answer 22

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

Poulenc

This is the same syntax as in this answer (just different names for model and column) – Natatorial