Select specific columns from Eloquent relations

Question

I have the following models in my application:

User.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    public function customer_details()
    {
        return $this->hasOne(CustomerDetails::class);
    }
}

CustomerDetails.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CustomerDetails extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

Now I am trying to run the following query:

$user = User::select('email', 'phone')
    ->where('id', Auth::user()->id)
    ->with('customer_details')
    ->first();

Now what I want it to do is to select only the email and phone number from my users table and first_name, last_name from my customer_details table but whenever I try this it always returns customer_details as null.

In your User.php file you missed to specify localKey to compare with foreign key: `return $this->hasOne('App\Models\CustomerDetails', 'user_id');` It should be : `return $this->hasOne('App\Models\CustomerDetails', 'user_id','id');` — Punit Patel, May 12 '21 at 17:08
Note for a 7 year old question: the general question has value, but the specific problem could have been avoided with `$user = Auth::user()->load("customer_details");` — miken32, Aug 07 '22 at 02:33

Josh · Accepted Answer · 2015-09-23T15:23:28.010

13

You need to include the id in your select, otherwise the eager loading has nothing to match against. I just tested this against my own project and ran into the same issue, adding the id to the select fixes it.

$user = User::select('id', 'email', 'phone')->where('id', Auth::user()->id)->with('customer_details')->first();

To further explain this, eager loading works by making your parent call first (in this case, the call to users) and then doing a second call like SELECT * FROM customer_details WHERE customer_details.user_id IN (?) where ? is a list of user IDs. It then loops through the results from that second call and attaches them to the appropriate user objects. Without the id on the user objects, there's nothing to match against in the second call or when attaching.

EDIT

To select specific columns from an eagerly loaded table, you can use the closure capabilities explained here. So you use the relationship name as the key and a closure that receives a Builder object as the value, like so:

$user = User::select('id', 'email', 'phone')
    ->where('id', Auth::user()->id)
    ->with('customer_details' => function (Builder $query) {
        $query->select('id', 'user_id', 'name', 'foo');
    })
    ->first();

Note that you must select the relationship column for both, otherwise Laravel has no idea how to connect the two

edited Sep 23 '15 at 15:23

answered Sep 22 '15 at 22:16

Josh

8,079
3
24
49

Thank you that worked :) i gotta ask though how do i then select only specific columns from "customer_details" as well? – user3718908x100 Sep 23 '15 at 12:46
@user3718908 Please see the edit I just made to my answer – Josh Sep 23 '15 at 15:23
The code with the closure, there is something wrong with it, it gets underlined in red, i took out 'Builder' and left it as '$query' alone. It works now. :) – user3718908x100 Sep 23 '15 at 22:13
1

You would have to import the Builder class (`use Illuminate\Database\Eloquent\Builder;`) – Josh Sep 23 '15 at 23:28
Yes really correct answer and explanations. "Note that you must select the relationship column for both, otherwise Laravel has no idea how to connect the two" this is the real answer – Elby Feb 25 '19 at 06:52
Your code to select specific columns give error on Laravel 7. Update the "with" section by the following code: ->with(array('customer_details' => function ($query) {$query->select('id', 'user_id', 'name', 'foo');})) – habib Jun 02 '20 at 18:34

Morty · Answer 2 · 2015-09-22T21:33:47.677

-1

I think your select is bad. Use query builder in laravel.

try this

$user=DB::table('users')->join('customer_details','user.id','=','customer_details.id')->select('email','phone','first_name','last_name')->where('id','=',Auth::user()->id)->first();

I suppose that you use also id as primary key in customer_details.

edited Sep 22 '15 at 21:33

answered Sep 22 '15 at 21:27

Morty

164
3
18

score -1 · Answer 3 · answered May 12 '21 at 17:11

In your User.php file you missed to specify localKey to compare with foreign key:

return $this->hasOne('App\Models\CustomerDetails', 'user_id');

It should be : There is a 3 parameter : ( Model, 'Compare key with', 'Local key')

return $this->hasOne('App\Models\CustomerDetails', 'user_id','id');

Showkii Paa · Answer 4 · 2022-08-10T06:56:01.993

This absolutely correct.If you have thrown an exception error with this code. like this eg
"error": "mb_strpos() expects parameter 1 to be string, object given"

    $user = User::select('id', 'email', 'phone')
            ->where('id', Auth::user()->id)
            ->with('customer_details' => function (Builder $query) {
             $query->select('id', 'user_id', 'name', 'foo');
        })->first();

then use just [] in with like this

$user = User::select('id', 'email', 'phone')
        ->where('id', Auth::user()->id)
        ->with(['customer_details' => function (Builder $query) {
       $query->select('id', 'user_id', 'name', 'foo');
}])->first();

Select specific columns from Eloquent relations

4 Answers4

Linked