Get Specific Columns Using “With()” Function in La

问题:

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?

回答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::with(array('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) is necessary in the $query->select() to actually retrieve the necessary results.*

回答2:

In your Post model

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

Original credit goes to Laravel Eager Loading - Load only specific columns

回答3:

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.

回答4:

You can do it like this in Laravel 5.5:

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

Care for the id field as stated in the docs:

When using this feature, you should always include the id column in the list of columns you wish to retrieve.

回答5:

In Laravel 5.6 you can call specific field like this

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

回答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

回答7:

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
)

回答8:

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",
     ],
   }

回答9:

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

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