php Laravel Eloquent：如何仅从连接表中获取某些列

Laravel Eloquent: How to get only certain columns from joined tables

提问by ralu

I have got 2 joined tables in Eloquent namely themes and users.

我在 Eloquent 中有 2 个连接表，即主题和用户。

theme model:

主题模型：

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

user model:

用户模型：

public function themes() {
  return $this->has_many('Theme');
}

My Eloquent api call looks as below:

我的 Eloquent api 调用如下所示：

return Response::eloquent(Theme::with('user')->get());

Which returns all columns from theme (that's fine), and all columns from user (not fine). I only need the 'username' column from the user model, how can I limit the query to that?

它返回主题中的所有列（很好）和用户的所有列（不好）。我只需要用户模型中的“用户名”列，如何将查询限制在此范围内？

回答by user2317976

Change your model to specify what columns you want selected:

更改模型以指定要选择的列：

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

And don't forget to include the column you're joining on.

并且不要忘记包括您要加入的专栏。

回答by Javi Stolz

For Laravel >= 5.2

对于 Laravel >= 5.2

Use the ->pluck()method

使用->pluck()方法

$roles = DB::table('roles')->pluck('title');

If you would like to retrieve an array containing the values of a single column, you may use the pluck method

如果您想检索包含单列值的数组，您可以使用 pluck 方法

---

---

For Laravel <= 5.1

对于 Laravel <= 5.1

Use the ->lists()method

使用->lists()方法

$roles = DB::table('roles')->lists('title');

This method will return an array of role titles. You may also specify a custom key column for the returned array:

此方法将返回一个角色头衔数组。您还可以为返回的数组指定自定义键列：

回答by Melvin

You can supply an array of fields in the get parameter like so:

您可以在 get 参数中提供一组字段，如下所示：

return Response::eloquent(Theme::with('user')->get(array('user.username'));

UPDATE (for Laravel 5.2) From the docs, you can do this:

更新（适用于 Laravel 5.2）从docs，您可以执行以下操作：

$response = DB::table('themes')
    ->select('themes.*', 'users.username')
    ->join('users', 'users.id', '=', 'themes.user_id')
    ->get();

回答by aykut

I know, you ask for Eloquent but you can do it with Fluent Query Builder

我知道，您要求 Eloquent，但您可以使用Fluent Query Builder

$data = DB::table('themes')
    ->join('users', 'users.id', '=', 'themes.user_id')
    ->get(array('themes.*', 'users.username'));

回答by Sajjad Ashraf

This is how i do it

这就是我的做法

$posts = Post::with(['category' => function($query){
        $query->select('id', 'name');
      }])->get();

First answer by user2317976 did not work for me, i am using laravel 5.1

user2317976 的第一个回答对我不起作用，我使用的是 laravel 5.1

回答by Jason Lewis

Another option is to make use of the $hiddenproperty on the model to hide the columns you don't want to display. You can define this property on the fly or set defaults on your model.

另一种选择是利用$hidden模型上的属性来隐藏您不想显示的列。您可以动态定义此属性或在模型上设置默认值。

public static $hidden = array('password');

Now the users password will be hidden when you return the JSON response.

现在，当您返回 JSON 响应时，用户密码将被隐藏。

You can also set it on the fly in a similar manner.

您也可以以类似的方式即时设置它。

User::$hidden = array('password');

回答by Nufayl

Using with pagination

与分页一起使用

$data = DB::table('themes')
->join('users', 'users.id', '=', 'themes.user_id')
->select('themes.*', 'users.username')
->paginate(6);

回答by Specs

This Way:

这边走：

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

回答by KinoP

user2317976 has introduced a great static way of selecting related tables' columns.

user2317976 引入了一种很好的静态方法来选择相关表的列。

Here is a dynamic trick I've found so you can get whatever you want when using the model:

这是我发现的一个动态技巧，因此您可以在使用模型时获得所需的任何内容：

return Response::eloquent(Theme::with(array('user' => function ($q) {
    $q->addSelect(array('id','username'))
}))->get();

I just found this trick also works well with load() too. This is very convenient.

我刚刚发现这个技巧也适用于 load() 。这是非常方便的。

$queriedTheme->load(array('user'=>function($q){$q->addSelect(..)});

Make sure you also include target table's key otherwise it won't be able to find it.

确保您还包含目标表的键，否则将无法找到它。

回答by Armen Markossyan

I know that this is an old question, but if you are building an API, as the author of the question does, use output transformers to perform such tasks.

我知道这是一个老问题，但是如果您正在构建 API，正如问题的作者所做的那样，请使用输出转换器来执行此类任务。

Transofrmer is a layer between your actual database query result and a controller. It allows to easily control and modify what is going to be output to a user or an API consumer.

Transofrmer 是您的实际数据库查询结果和控制器之间的一层。它允许轻松控制和修改将要输出给用户或 API 消费者的内容。

I recommend Fractalas a solid foundation of your output transformation layer. You can read the documentation here.

我推荐Fractal作为输出转换层的坚实基础。您可以在此处阅读文档。