使用Laravel的“with()”函数获取特定的列

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

我有两个表格用户，一个用户有很多帖子，一个帖子只属于一个用户。

In my User Model I have hasMany relation like

在我的用户模型中，我有许多类似的关系

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

And in my post model I have belongsTo relation like

在我的post模型中，我有归属感

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

Now I want join of these two tables using Eloquent with() but want specific columns from second table. I know I can use query Builder but I don't wat to use that. When In post model I write

现在，我希望使用雄辩的with()来连接这两个表，但希望从第二个表中获得特定的列。我知道我可以使用query Builder，但我不会使用它。当我写后模型

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

It runs following queires

它运行后queires

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

But I want

但我希望

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 column from first table. I want specific columns using with() from second table. How can I do that?

它只从第一个表返回列。我想要从第二个表中使用with()的特定列。我怎么做呢?

8 个解决方案

#1

194

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.

它将只从其他表中选择id和用户名。我希望这能帮助别人。

Remember that the primary key (id in this case) is necessary in the $query->select() to actually retrieve the necessary results.

记住，在$query->select()中，主键(在本例中是id)是必需的，以便实际检索必要的结果。

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

不要忘记包含外键(在本例中假设它是user_id)来解析关系，否则您的关系将得到零结果。

#4

You can do it like this in Laravel 5.5:

你可以在Laravel 5.5中这样做:

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

Care for the id field as stated in the docs:

如文件所述，请注意id字段:

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

在使用此特性时，应该始终在希望检索的列列表中包含id列。

#5

In your Post model:

在你的模型:

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

Now you can use $post->userWithName

现在您可以使用$post->userWithName。

#6

In Laravel 5.6 you can call specific field like this

在Laravel 5.6中，可以像这样调用特定的字段

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

#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:

注意，如果您只需要表中的一列，那么使用“list”就相当不错了。在我的例子中，我正在检索用户最喜欢的文章，但我只想要文章id:

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

Returns an array of ids, eg:

返回一个id数组，例如:

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

#8

Now you can use the pluckmethod on a Collection instance:

现在您可以在一个集合实例上使用pluckmethod:

This will return only the uuid attribute of the Post model

这将只返回Post模型的uuid属性

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

#1

194