Laravel `with` issues

Question

I've got 3 models defined as such:

class Driver extends Model
{
    public function provider(){
        return $this->belongsTo(Provider::class);
    }
}

class DriverPhone extends Model
{
    public function provider(){
        return $this->belongsTo(Provider::class);
    }
}

class Provider extends Model
{
    public function drivers(){
        return $this->hasMany(Driver::class);
    }

    public function driverPhones(){
        return $this->hasMany(DriverPhone::class);
    }

}

Now - I want to get all available Drivers, whose Provider DriverPhones meet given criteria. I thought that doing it this way would be the way to go:

Driver::with(['provider.driverPhones' => function($query) use ($phone_uuid){
    $query->where('uuid', $phone_uuid);
}]);

And when I analyse the query via:

var_dump($phone_uuid, Driver::with(['provider.driverPhones' => function($query) use ($phone_uuid){
    $query->where('uuid', $phone_uuid);
}])->toSql()); exit;

it gives me:

select * from `drivers` where `drivers`.`deleted_at` is null

Yet when I use whereHas the resultset is correct:

Driver::whereHas('provider.driverPhones', function($query) use ($phone_uuid){
   $query->where('uuid', $phone_uuid);
})->get()`

Am I missing something?

---

Followup question - for given additional models:

class Journey extends Model
{
    public function coach(){
         return $this->belongsTo(CoachId::class);
    }
}

class Coach extends Model
{
    public function journey(){
         return $this->belongsTo(Journey::class);
    }
}

If I search Journey::with('coach', 'journeyLocations')->whereHas('coach', [my subquery]) it finds correct Journey, but trying to access coach gives me null. If I omit with, proper coach property is accessed, but of course multiple queries are run.

So - what's the deal with with?

Answer 1

First let me suggest a nicer way to inspect your sql queries:

• Add \DB::listen(function ($q) { \Log::info($q->sql, $q->bindings); }); before the queries.
• In command line, run tail -f storage/logs/laravel.log to see the queries printed there as they are executed.

You will find that actually with doesn't do a JOIN. Instead, it looks for related models with a WHERE clause: WHERE id IN (1, 2, 3). It's not a join, it's a separate query.

On the other hand, whereHas does include the constraint in the same query, with a WHERE EXISTS clause. So it "filters" from the result, but doesn't provide the eager loading of the related models.

So the solution would be what Tim Lewis mentioned in the comments: apply the same constraint function to with and whereHas:

$constraint = function ($query) use ($phone_uuid) {
    $query->where('uuid', $phone_uuid);
};

Driver::with(['provider.driverPhones' => $constraint])
    ->whereHas('provider.driverPhones', $constraint)
    ->get();