Neha's avatar
Neha
OP
8 years ago
Level 1

Eloquent Relationship :Put Condition on "with()" clause

App\Request::where('id',4)->with('quotes.status','=','3')->with('sourceTable','destinationTable')->get(); In Request Model public function quotes(){ return $this->hasMany('App\Quote','request_id'); } public function sourceTable() { return $this->belongsTo('App\OriginatorTable', 'source_id'); } public function destinationTable() { return $this->belongsTo('App\OriginatorTable', 'destination_id'); }

In Quote Model public function request() { return $this->belongsTo('App\Request', 'id'); }

In above query , we have Request model which have relationship with quotes , sourceTable and destinationTable. Now I want to fetch data where request has id "4" along with quote status "3" and sourceTable ,DestinationTable. But I am getting the error :- Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'quotes.id' in 'where clause' (SQL: select * from quotes where quotes.id in (4))'

0 likes
5 replies
Snapey's avatar
Snapey
8 years ago
Best Answer
Level 122

You need to use whereHas if you want to limit the Request model by its relations

App\Request::where('id',4)
    ->whereHas('quotes', function ($query) {
        $query->where('status','=','3');
    })
    ->with('quotes','sourceTable','destinationTable')
    ->get();

but your query does not make sense. If you know the id of the request you want, checking the status is not necessary ?

If your requirement is actually "where request has id "4" along with all quotes having status "3" and sourceTable ,DestinationTable. " then you need to limit the related model and use a function on the with;

App\Request::where('id',4)
    ->with('quotes', function ($query) {
        $query->where('status','=','3');
    })
    ->with('sourceTable','destinationTable')
    ->get();
18 likes
kornel's avatar

@Snapey Your solution works great but what if you have to eager load more distant relation? Eg. quotes.author and quotes.translations

For example like this:

    ->whereHas('quotes', function ($query) {
        $query->where('status','=','3');
    })
    ->with('quotes.author','quotes.translations','sourceTable','destinationTable')
    ->get();

How to do it in this case?

Neha's avatar
Neha
OP
8 years ago
Level 1

Thanks Snapey, According to laravel 5.4 , Above query will give error(PHP warning: mb_strpos() expects parameter 1 to be string, object given in .../public_html/HorseTaxi/vendor/laravel/framework/src/Illuminate/Support/Str.php on line 94 ). Correct Query is : App\Request::where('id',4)->with(['quotes'=>function($query){ $query->where('status','=','3');}]) ->with('sourceStable','destinationStable') ->get();

1 like

Please or to participate in this conversation.