phplaraveleloquentlaravel-query-builder

Get Specific Columns Using “With()” Function in Laravel Eloquent


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?


Solution

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