php 在 Laravel Eloquent 中使用“With()”函数获取特定列

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

提问by Awais Qarni

I have two tables, Userand Post. One Usercan have many postsand one postbelongs to only one user.

我有两张桌子，User和Post。一个User可以有多个posts，一个post只属于一个user。

In my Usermodel I have a hasManyrelation...

在我的User模型中，我有一个hasMany关系......

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

And in my postmodel I have a belongsTorelation...

在我的post模型中，我有一个belongsTo关系......

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.

现在我想使用Eloquent with()但想要第二个表中的特定列来连接这两个表。我知道我可以使用查询生成器，但我不想。

When in the Postmodel I write...

当Post我在模型中写...

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?

它只返回第一个表中的列。我想要使​​用with()第二个表中的特定列。我怎样才能做到这一点？

回答by Awais Qarni

Well I found the solution. It can be done one by passing a closurefunction in with()as second index of array like

好吧，我找到了解决方案。它可以通过传递一个closure函数with()作为数组的第二个索引来完成，例如

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

It will only select idand usernamefrom other table. I hope this will help others.

它只会从其他表中选择id和username。我希望这会帮助其他人。

---

---

Remember that the primary key (id in this case) needs to be the first paramin the $query->select() to actually retrieve the necessary results.*

请记住，主键（在本例中为 id）需要是$query->select() 中的第一个参数才能实际检索必要的结果。*

回答by Adam

You can do it like this since Laravel 5.5:

从 Laravel 5.5 开始，你可以这样做：

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

Care for the idfield and foreign keysas stated in the docs:

关心该id领域并foreign keys如文档中所述：

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.

使用此功能时，您应该始终在要检索的列列表中包含 id 列和任何相关的外键列。

For example, if the user belongs to a team and has a team_idas a foreign key column, then $post->user->teamis empty if you don't specifiy team_id

例如，如果用户属于一个团队并且有一个team_id作为外键的列，那么$post->user->team如果不指定则为空 team_id

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

回答by user1669496

In your Postmodel

在你的Post模型中

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

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

原始功劳归于 Laravel Eager Loading - 仅加载特定列

回答by Thijs

When going the other way (hasMany):

当走另一条路时（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）来解析关系，否则您的关系将得到零结果。

回答by hendra1

In Laravel 5.7 you can call specific field like this

在 Laravel 5.7 中，您可以像这样调用特定字段

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

It is important to add foreign_keyfield in the selection.

foreign_key在选择中添加字段很重要。

回答by Duy Hoang

In your Postmodel:

在您的Post模型中：

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

Now you can use $post->userWithName

现在你可以使用 $post->userWithName

回答by jwarshaw

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.

我遇到了这个问题，但有第二层相关对象。@Awais Qarni 的回答支持在嵌套的 select 语句中包含适当的外键。正如在第一个嵌套的 select 语句中需要 id 来引用相关模型一样，外键也需要引用第二级相关模型；在本例中为公司模型。

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 模型中选择特定的列，您需要在 select 语句中包含 user_id 列以引用它。

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

回答by Haritsinh Gohil

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 herein response of this same question :

如果你想使用得到特定的列with()在laravel口若悬河，那么你可以使用代码如下它最初是由@Adam在他的回答回答这里在这同一个问题的回答：

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

所以我在我的代码中使用了它，但它给了我错误1052: Column 'id' in field list is ambiguous，所以如果你们也面临同样的问题

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

然后为了解决它，您必须在with()方法中的 id 列之前指定表名，如下代码：

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

回答by omar j

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:

请注意，如果您只需要表格中的一列，那么使用“列表”非常好。就我而言，我正在检索用户最喜欢的文章，但我只想要文章 ID：

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

Returns an array of ids, eg:

返回一个 id 数组，例如：

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

回答by Shreyansh Panchal

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

您还可以在访问时指定相关模型上的列。

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

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