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

I have got 2 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 looks as below:

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?

This question is related to php laravel eloquent

The answer is


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

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

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

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

Using with pagination

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

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

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.

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


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

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.


Using Model:

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

Using Query Builder:

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

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


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:


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

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.

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.

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


This Way:

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

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.


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

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


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


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.

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

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

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

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

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

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

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


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

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to eloquent

Eloquent: find() and where() usage laravel How to select specific columns in laravel eloquent Laravel Eloquent where field is X or null Laravel Eloquent limit and offset laravel collection to array Eloquent get only one column as an array Laravel 5.2 - Use a String as a Custom Primary Key for Eloquent Table becomes 0 Laravel 5.2 - pluck() method returns array Eloquent ORM laravel 5 Get Array of ids eloquent laravel: How to get a row count from a ->get()