Laravel Eager Loading - Load only specific columns

Question

I am trying to eager load a model in laravel but only return certain columns. I do not want the whole eager loaded table being presented.

public function car()
{
    return $this->hasOne('Car', 'id')->get(['emailid','name']);
}

I am getting the following error:

log.ERROR: exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Call to undefined method Illuminate\Database\Eloquent\Collection::getAndResetWheres()'

Answer 1

Make use of the select() method:

public function car() {
    return $this->hasOne('Car', 'id')->select(['owner_id', 'emailid', 'name']);
}

Note: Remember to add the columns assigned to the foreign key matching both tables. For instance, in my example, I assumed a Owner has a Car, meaning that the columns assigned to the foreign key would be something like owners.id = cars.owner_id, so I had to add owner_id to the list of selected columns;

Answer 2

Also you don't need to specify getting specific columns in the model and the relationship method itself... You can do that whenever you need it... Like this:

$owners = Owner::
          with([
              'car' => function($q)
               {
                    $q->select('id', 'owner_id', 'emailid', 'name');
               },
               'bike' => function($q)
               {
                    $q->select('id', 'owner_id', 'emailid', 'name');
               }
          ])->
          get();

In this way you can also get all of the columns of the related model if you have ever needed to.

Answer 3

In your controller you should be doing something like

App\Car::with('owner:id,name,email')->get();

Supposing that you have two models defined like below

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Car extends Model
{
    protected $table = 'car';

    public function owner()
    {
        return $this->belongsTo('App\Owner', 'owner_id');
    }
}

and

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;

class Owner extends Model
{
    protected $table = 'owner';

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

and you have two tables something like:

owners: id | name | email | phone | other_columns...

and

cars: id | owner_id | make | color | other_columns...

Credits go to the docs: eloquent-relationships#eager-loading scroll to Eager Loading Specific Columns

Answer 4

The easiest thing you can do is go by the documentation and do the following thing for getting only specific columns without any extra line of code or closure. Follow steps,

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

then when you eager load the relation select only selected columns

$owner = $this->owner
->where('id', 23)
->with('car:id,owner_id,emailid,name')   //no space after comma(, ) and id has to be selected otherwise it will give null

Hope this works, have a great day.

Answer 5

The answers from shahrukh-anwar and Bogdan are both excellent and led me to solving my version of this problem.

However, there's one critical piece i would add for clarification (even the docs don't mention it).

Take the following, which was still broken for me:

Car::with('owner:id,name,email')->get(['year', 'vin']); 

You rarely see specific column selection on the primary model (->get(...)) so it's easy to forget: your selection needs the foreign key column:

Car::with('owner:id,name,email')->get(['owner_id', 'year', 'vin']);

Sure, it seems obvious once you make the mental connection between with using a Closure and this syntax, but still, easy to overlook.

When you have tables with 30+ columns and only need 3 of them, this might keep your memory load down a bit.

Answer 6

It is also possible on eager loading to specify a specific column to load. Suppose you have this modal class

class user extends Model {
  public function car()
    {
      return $this->hasOne('Car', 'id')->get(['emailid','name']);
    }
}

if you want to load user with its car you can do this on your controller

User::with(['car:id,emailid,name'])->get(); 

:-Note that DON"T forget to add the foreign key inside the columns you want to get with other wise it will now work. You need a car model as well as the reverse relationship.

For more details you can refer this

Eager Loading Specific Columns

Answer 7

Using load is even more easy. If we have a model already instantiated we can put for example having a model User that has a relation of one to many with model Comments and we only want to select from Comment the id and the title

In User model the relation method is

public function comments()
    {
        return $this->hasMany(Comment::class);
    }

In Comment model the relation method is

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

In our controller if we want to recover user with their comments

$user = new User();

$user->load('comment:id,title')

And we will get the user with comment relation loaded only with id and title :-)

Answer 8

Try WITH() & WHERE() conditions.

$user_id = 1; // for example

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

Answer 9

Sometimes you need to make your relation generic so that can call on your ease.

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

and while fetching you can mention columns you need.

$owners = Owner::with(['car' => function($query) { // eager loading
            $query->select('emailid','name');
           }
          ])->get();

foreach($owners as $owner){
    $owner->car->emailid;
    $owner->car->name;
}

Answer 10

make sure to put id column when using eager loading

Voucher::with(['storeInfo:id,name as branchName,code as branchCode'])->get();

IN MODEL

public function storeInfo() { return $this->belongsTo(Branch::class,'branch_id'); }

Answer 11

For Nested Relation, we can use this

 Post::with(['user' => function ($query) {
            $query->select('id','company_id', 'username');
        }, 'user.company' => function ($query) {
            $query->select('id', 'name');
        }])->get();