ChatGPT解决这个技术问题 Extra ChatGPT

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?

I am working the similar task, may i know if I using Response what kind of class that I need to import?

B
Bas Peeters

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.


great answer, also the tip about including the join column saved me a ton of time!
What if in some places I need only id instead of both id and username? Still have to select both?
Hi does select function have same function in the model query builder?
I dont think it is a good approach. It will be sort of hard coding the column names. All the places will return only these columns. In some other api, i may need some more columns, so that wont work here. I think using QueryBuilder is the option here.
This is a dramatic approach as it will occur every time you make the call moving forward. I believe Sajjad Ashraf's answer is the one most people will be interested in.
J
Javi Stolz

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:


This isn't really an answer to the question (which was specifically about joins/relationships).
M
Melvin

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

I tried this and it shows an error SQLSTATE[42S22]: Column not found and it's SQL doesn't include the table in the with. The sql that it appears on the error is "SELECT fk_table.column1 FROM main_table".
It's "users.username" (not "user.username"). The code in the answer will work if you have the table and column names matching your actual database. That's the downside with this solution, it's building an SQL query rather than using Eloquent, so the names have to match your actual database tables and columns.
a
aykut

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

S
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


Up to date answer = useful!
Thanks. It works fine for me. I must add the foreign key in the select statement like you did with 'id' otherwise it returns null.
However, this still retrieves a "pivot" field for each result, containing both ids (post_id and category_id) right? How can I get ride of it?
@mayid you can try adding pivot to $hidden array of Post Model protected $hidden = ['pivot']
This is the best solution (assuming you're using a fairly modern version of Laravel)
N
Nufayl

Using with pagination

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

J
Jason Lewis

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.2 I'm getting 'Cannot access protected property User::$hidden' when trying to set this dynamically.
it shouldn't be static.
@StackOverflowed, I take it you didn't take any notice of how old this question/answer is and that it related to Laravel 3.
K
KinoP

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.


S
Specs

This Way:

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

Please always explain your answer.
Had a similar problem, this is the smartest way so far! This answer is underrated!
This is the same as the answer that was already previously submitted by Sajjad Ashraf.
A
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.

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.


J
John Veldboom

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


J
JoeGalind

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.


Do you know how can we fetch specific column of related table when using pagination.
@Kamlesh , if you don't specify the select() statement on the Theme table, you will get all the fields.
Thanks for your reply. But I think you did not get my requirement which is "I want all the fields of first table and also wants to fetch some fields of joined tables." as per my last comment. Thanks again.
So I guess you want 2 tables? Then something like this: Theme::with(['user:userid,name,address', 'table2'])->get() This will get all fields from the themes table, all fields from table2 and some fields of users
M
Md. Saidur Rahman Milon

Using Model:

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

Using Query Builder:

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

K
Kornel

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

P
Pradeep Kumar

#You can get selected columns from two or three different tables

$users= DB::Table('profiles')->select('users.name','users.status','users.avatar','users.phone','profiles.user_id','profiles.full_name','profiles.email','profiles.experience','profiles.gender','profiles.profession','profiles.dob',)->join('users','profiles.user_id','=','users.id')
            ->paginate(10);

i
ipixelsmediaworks

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.