Laravel: Load relationships and specific columns

Question

I have two tables User and Car. One user may have a car.

I have a relation set like this:

public function car()
{
    return $this->hasOne('App\Car');
}

I want to fetch the users with a car, but only select the name of the user and the color of the car.

If I try

App\User::with('car:color')->select('name')->first()->toArray();

then I do not get the car color. That is the result:

array:1 [▼
  0 => array:2 [▼
    "name" => "Max Mustermann"
    "car" => []
  ]
]

Is it possible to only get the name of the user and the color of the car?

Christoph Winkler · Accepted Answer · 2017-10-11T08:13:27.203

1

It fails because the User Table has no color Column. You have to access the relation to get the color.

$user = User::has('car')->first();

print $user->car->color;

AFAIK you won't be able to fetch just the two fields with Eloquent Relations. One way to achieve this would be using Query Builder:

DB::table('users')
    ->join('cars', 'users.id', '=', 'cars.user_id')
    ->select('users.name', 'cars.color')
    ->get();

edited Oct 11 '17 at 08:13

answered Oct 10 '17 at 09:32

Christoph Winkler

This will only fetch first row from user table. – Himanshu Upadhyay Oct 10 '17 at 09:36
I understand that you approach works, but this returns all fiends of the user. I only want my user object to have 2 attributes, in this case only `$user->name` and `$user->car->color` no need to load all the other fields from the two tables. – Adam Oct 10 '17 at 20:26

score 1 · Answer 2 · answered Oct 10 '17 at 09:35

1

You can try like this:

User :: Select("*")->with("car")->get();

answered Oct 10 '17 at 09:35

Himanshu Upadhyay

Thanks, your approach works. But is there a query so that the returned user object only has 2 attributes, in this case only `$user->name` and `$user->car->color`? There is no need for my application to load all the other fields from the two tables. When I wrote raw SQL commands, I never used `SELECT * FROM` ... to gain speed – Adam Oct 10 '17 at 20:37
@Adam, I am glad that you got a solution with help of my answer, it will be good if you accept this answer and vote it up. And for your concern about speed, I would say, this is ORM, it will not take time as a normal query takes to fetch with `select * from table` so don't worry. This won't make any huge difference. – Himanshu Upadhyay Oct 11 '17 at 04:41

score 1 · Answer 3 · answered Oct 10 '17 at 10:15

1

This is the correct way.

$users = User::with('car.color')->select('name')->get();

and then you can display them as:

foreach($users as $user){
     echo $user->name . " owns a " . $user->car->color . " car.";
}

answered Oct 10 '17 at 10:15

Richie

3 Answers3