Laravel Eloquent: how to get only specific columns from joined tables

Question

I have two joined tables in Eloquent, namely themes and users.

theme model:

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

user model:

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

My Eloquent api call is as follows:

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

Returns all columns from the topic (this is fine) and all columns from the user (not really). I only need the "username" column from the user model, how can I limit this query?

Answer 1

Modify your model to indicate which columns you want to select:

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

And don't forget to include the column you are entering.

Answer 2

For Laravel> = 5.2

Use the method -> pluck ()

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

If you want to get an array containing the values ​​of a single column, you can use the pluck method

---

For Laravel <= 5.1

Use the method -> lists ()

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

This method will return an array of role names. You can also specify your own key column for the returned array:

Answer 3

You can specify an array of fields in the get parameter like this:

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

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

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

Answer 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')); 

Answer 5

Another option is to use the $hidden property in the model to hide columns that you do not want to display. You can define this property on the fly or set default values ​​for your model.

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

Now the user password will be hidden when returning a JSON response.

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

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

Answer 6

This is how i do it

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

User first answer2317976 did not work for me, I am using laravel 5.1

Answer 7

Paginated Use

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

Answer 8

user2317976 introduced a great static way to select columns of related tables.

Here is a dynamic trick I found so that 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 that this trick also works well with load (). It is very convenient.

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

Make sure you also include the key of the target table, otherwise it will not be able to find it.

Answer 9

This way:

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

Answer 10

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

Transofrmer is the layer between the actual result of the database query and the controller. This makes it easy to control and change what will be displayed to the user or consumer of the API.

I recommend Fractal as a solid foundation for your output conversion level. You can read the documentation here .

Answer 11

In Laravel 4, you can hide certain fields from returning by adding the following to your model.

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

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

Answer 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',...]); 

Answer 13

If I understand well, then that returns is fine if you want to see only one column. If so, then this should be much simpler below:

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

Answer 14

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

You should be able to determine which columns you do not want to display in the api.