Where clause inside whereHas being ignored in Eloq

Question

Im trying to make a query using whereHas with eloquent. The query is like this:

$projects = Project::whereHas('investments', function($q) {
                $q->where('status','=','paid');
            })
            ->with('investments')
            ->get();

Im using Laravel 5.2 using a Postgres driver.

The Project model is:

public function investments()
{
    return $this->hasMany('App\Investment');
}

The investments model has:

public function project() {
    return $this->belongsTo('App\Project');
}

The projects table has fields id,fields...

The investments table has the fields id,project_id,status,created_at

My issue is that the query runs and returns a collection of the projects which have at least one investment, however the where clause inside the whereHas is ignored, because the resulting collection includes investments with status values different than paid.

Does anyone has any idea of what is going on?

Answer 1

I believe this is what you need

$projects = Project::whereHas('investments', function($q) { 
          $q->where('status','=','paid'); 
     })->with(['investments' => function($q) { 
          $q->where('status','=','paid'); 
     }])->get();

whereHas wil check all projects that have paid investments, with will eagerload all those investments.

Answer 2

You're confusing whereHas and with.

The with method will let you load the relationship only if the query returns true.

The whereHas method will let you get only the models which have the relationship which returns true to the query.

So you need to only use with and not mix with with whereHas:

$projects = Project::with(['investments' => 
function($query){ $query->where('status','=','paid'); }])
->get();

Answer 3

Try like this:

$projects = Project::with('investments')->whereHas('investments', function($q) {
                $q->where('status','like','paid'); //strings are compared with wildcards.
            })
            ->get();

Answer 4

Change the order. Use with() before the whereHas(). I had a similar problem few weeks ago. Btw, is the only real difference between the problem and the functional example that you made.