[FIXED] Laravel eloquent, select model with relationship in a single query

Issue

Let's say we have many-to-many relationship for models Car and Driver:

class Car extends Model {...}

class Connection extends Model
{
    public function car()
    {
        return $this->belongsTo('app\Car');
    }

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

class Driver extends Model {...}

Now I'd like to query for Cars which can be driven by John. With QueryBuilder this is quite simple:

 $query = DB::table('cars')
        ->join('connections', 'cars.id', '=', 'connections.car_id')
        ->join('drivers', 'connection.driver_id', '=', 'drivers.id')
        ->where('drivers.name', 'like', 'John')->select('cars.*')->get();

This will generate a single SQL query which is more or less exactly would I'd expect.

The rest of the application uses Eloquent for DB querying and so I'd like to rewrite this part with Eloquent. I gave it multiple tries but I can't generate a single query with plain JOINs (not subqueries). Is it possible?

Solution

You can use whereHas eloquent function to get Cars which can be driven by John

$cars = Car::whereHas('drivers',function($query) use ($driver_name){
       $query->where('name','like','%'.$driver_name.'%');
})->get();

for models you can use which @Idob write to his answer.

Answered By - seyyed sina