CODEWITHSUNDEEP
laravel-5eloquent
orim
orim

Reputation: 143

Laravel eloquent, select model with relationship in a single query

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?

Upvotes: 0

Views: 2656

Answers (2)

seyyed sina
seyyed sina

Reputation: 114

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.

Upvotes: 1

Idob
Idob

Reputation: 1650

You can use the belongsToMany relationship and delete your Connection model.

Change your models to be:

class Car extends Model
{
    public function drivers()
    {
        return $this->belongsToMany(Driver::class, 'connection');
    }
}

class Driver extends Model
{
    public function cars()
    {
        return $this->belongsToMany(Car::class, 'connection');
    }
}

And query the driver's cars by:

$drivers = Driver::with('cars')
    ->where('name', 'like', 'John')
    ->get();

foreach ($drivers as $driver) {
    $cars = $driver->cars;
}

Another small optimization is to follow Laravel's conventions for table naming and call the connection table as car_driver - than you would be able to remove the second parameter from the belongsToMany mathod.

Upvotes: 0

Related Questions