Laravel 5 belongsToMany search using pivot table

Question

I'm using Laravel's belongsToMany and a pivot table to link Models. However, the scoping I'm using creates a very inefficient query. Here's my code:

Restaurant class

class Restaurant extends Model {
    public function cuisines() {
        return $this->belongsToMany('Cuisine');
    }

    public function scopeByCity($query, $city_id) {
        return $query->where('city_id' '=', $id);
    }

    public function scopeByCuisine($query, $cuisine_id) {
        return $query->whereHas('cuisines', function($q) use ($cuisine_id) {
            $q->where('id', '=', $cuisine_id);
        });
    }
}

Cuisine class

class Cuisine extends Model {
    public function restaurants() {
        return $this->belongsToMany('Restaurant');
    }
}

now Restaurant::byCity(1)->byCuisine(2)->toSql() gives me:

select * from `restaurants` where `city_id` = ? and (select count(*) from `cuisines` inner join `restaurants_cuisines` on `cuisines`.`id` = `restaurants_cuisines`.`cuisine_id` where `restaurants_cuisines`.`restaurant_id` = `restaurants`.`id` and `id` = ?) >= 1

which takes 3 times as longer to execute than the more optimized query:

select * from `restaurants` left join `restaurants_cuisines` on `restaurants`.`id` = `restaurants_cuisines`.`restaurant_id` left join `cuisines` on `cuisines.id` = `restaurants_cuisines`.`cuisine_id` where `restaurants`.`city_id` = ? and `cuisines`.`id` = ?

Is this a limitation of Laravel's query builder or am I doing it wrong?

UPDATE I have now marked @Zoe Blair's answer as the correct one, but I still had to modify it for my need. For anyone in a similar situation, here is what the solution ended up being:

public function scopeByCuisine($query, $cuisine=null) {
    return $query->leftJoin('restaurants_cuisines', 'restaurants.id', '=', 'restaurants_cuisines.restaurant_id')
        ->leftJoin('cuisines', 'cuisines.id', '=', 'restaurants_cuisines.cuisine_id')
        ->where('cuisines.id', '=', $cuisine);
}

and as she suggested in her answer, Laravel will get all columns from all tables combined, so I also did:

$sql = Restaurant::select('restaurants.*')->byCity($city_id)->byCuisine($cuisine_id)->toSql

which gives me exactly what I was after!

Answer 1

You could tweak your query scope to use the join:

public function scopeByCity($query, $city_id) {
    return $query->leftJoin('cities', 'cities.id', '=', 'restaurants.city_id')
           ->where('city_id' '=', $city_id);
}

public function scopeByCuisine($query, $cuisine_id) {
    return $query->leftJoin('cuisines', 'cuisines.id', '=', 'restaurants.cuisine_id')
           ->where('cuisine_id' '=', $cuisine_id);
}

I believe eloquent defaults to bringing back all the columns, so I would restrict it to Restaurant::select('restaurants.*') before calling your query scopes, and then access the cuisine and city eloquent objects by eager loading them using with('cuisine', 'city')

And together:

$restaurants = Restaurant::select('restaurant.*')
    ->city(4)
    ->cuisine(3)
    ->with('cuisine', 'city')
    ->get();