Eloquent where clause to use related model's column when using with() -laravel 4

Question

I have 2 models

Truck

class Truck extends \Eloquent {
    // Add your validation rules here
    public static $rules = [
        'trucktype_id' => 'required',
        'weight'=> 'required',
        'truck_no'=> 'required'

    ];

    // Don't forget to fill this array
    protected $fillable = ['trucktype_id','weight','picture_path','remarks','truck_no'];

    public function TruckType(){
        return $this->belongsTo('TruckType','trucktype_id');
    }
}

TruckType

class Trucktype extends \Eloquent {
    // Add your validation rules here
    public static $rules = array(
                    'type'         => 'required|unique:trucktypes,type',
                    'max_weight'   => 'required'
                );

    // Don't forget to fill this array
    protected $fillable = ['type','max_weight'];
}

I need to lookup related table records i.e TruckType

$trucksobj = Truck::with('TruckType');
if($truck_no!="")
    $trucksobj->where("truck_no",'=',$truck_no);
if($start_date!="" && $end_date!="")
    $trucksobj->whereBetween('created_at', array($start_date, $end_date));
if($truck_type!="")
    $trucksobj->where("trucktype_id",'=',$truck_type);
if($overweight=="on")
    $trucksobj->where('TruckType.max_weight', '>=', 0);

But the above query didnt resolve TruckType.max_weight and throws following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'TruckType.max_weight' in 'where clause' (SQL: select count(*) as aggregate from trucks where TruckType.max_weight >= 0)

what about using having instead of where in the last statement? — Vit Kos
i havent used aggregate function here .. so no point of using having — sumit

Warren Moore Warren Moore · Accepted Answer · 2014-08-22T07:42:12

I think you misunderstand how with() actually works. It is only used to alleviate the N+1 query problem, and does not make the contents of the table available for querying. After your first query has ran to select all of the trucks, the with() simply causes the following query to be automatically ran:

select * from TruckType where TruckType.id in (...)

Here the list at the end will contain all of the different truck.trucktype_id values that were found in your first query, and then they'll automatically be available for you to use via $truck->TruckType->{property} etc.

Now, if you actually have a look at the query that's being generated for you, you can clearly see that there is no TruckType table referenced anywhere:

select count(*) as aggregate from trucks where TruckType.max_weight >= 0

This is why the error is being thrown.

You have two options:

(1) Use a join

$trucksobj = Truck::with('TruckType')->join('TruckType', 'truck.trucktype_id', '=', 'TruckType.id')->where('TruckType.max_weight', '>=', 0);

(2) Use whereHas() to place a constraint on your relationship

$trucksobj = Truck::with('TruckType')->whereHas('TruckType', function($q) {
  $q->where('max_weight', '>=', 0);
});

If you don't actually need to know anything about the truck type, and you only want to use it to sieve through the trucks, then you can get rid of with('TruckType') and just keep the rest of the query.

Eloquent where clause to use related model's column when using with() -laravel 4

1 Answers