Laravel能言善辩:如何只从已连接的表中获得某些列

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

我有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:

我雄辩的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?

返回主题的所有列(很好)和用户的所有列(不好)。我只需要用户模型中的“用户名”列，如何限制查询?

15 个解决方案

#1

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.

别忘了写你要加入的专栏。

#2

For Laravel >= 5.2

Use the ->pluck() method

使用- >拔()方法

$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

如果希望检索包含单个列的值的数组，可以使用fetch方法

---

For Laravel <= 5.1

Use the ->lists() method

使用- >列表()方法

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

该方法将返回一个角色标题数组。还可以为返回的数组指定自定义键列:

#3

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)，您可以这样做:

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

#4

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

我知道，你需要口才，但你可以用流利的查询生成器。

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

#5

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

#6

Another option is to make use of the $hidden property 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.

您还可以以类似的方式将其动态设置为on the fly。

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

#7

Using with pagination

使用分页

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

#8

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.

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

#9

This Way:

这种方式:

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

#10

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 Fractal as a solid foundation of your output transformation layer. You can read the documentation here.

我建议将分形作为输出转换层的坚实基础。您可以在这里阅读文档。

#11

In Laravel 4 you can hide certain fields from being returned by adding the following in your model.

在Laravel 4中，可以通过在模型中添加以下内容来隐藏某些字段以避免返回。

protected $hidden = array('password','secret_field');

http://laravel.com/docs/eloquent#converting-to-arrays-or-json

http://laravel.com/docs/eloquent converting-to-arrays-or-json

#12

Using Model:

使用模式:

Model::where('column','value')->get(['column1','column2','column3',...]);

Using Query Builder:

使用查询构建器:

DB::table('table_name')->where('column','value')->get(['column1','column2','column3',...]);

#13

On Laravel 5.5, the cleanest way to do this is:

在Laravel 5.5上，最干净的方式是:

Theme::with('user:userid,name,address')->get()

You add a colon and the fields you wish to select separated by a comma and without a space between them.

您将添加一个冒号和您希望使用逗号分隔的字段，并且在它们之间没有空格。

#14

If I good understood this what is returned is fine except you want to see only one column. If so this below should be much simpler:

如果我很好地理解了这一点，那么返回的内容是可以的，除非您希望只看到一列。如果是这样，下面这个应该简单得多:

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

#15

Check out, http://laravel.com/docs/database/eloquent#to-array

看看http://laravel.com/docs/database/eloquent #数组

You should be able to define which columns you do not want displayed in your api.

您应该能够定义不希望在api中显示哪些列。