php - Laravel, How to use where conditions for relation's column?

Question

I'm using Laravel and having a small problem with Eloquent ORM.. I can get this working simply with SQL query using a JOIN but I can't seem to get it working with Eloquent!

This is what I want, I have two tabels. one is 'Restaurants' and other is 'Restaurant_Facilities'.

The tables are simple.. and One-To-One relations. like there is a restaurant table with id, name, slug, etc and another table called restaurant_facilities with id, restaurant_id, wifi, parking, etc

Now what I want to do is.. load all restaurants which have wifi = 1 or wifi = 0.. How can i do that with Eloquent ? I have tried eager loading, pivot tables, with(), collections() and nothing seems to work!

The same problem I have for a Many-To-Many relation for cuisines! I have the same restaurant table and a cuisine table and a restaurant_cuisine_connection table..

but how do I load all restaurants inside a specific cuisine using it's ID ?

This works.

Cuisine::find(6)->restaurants()->get();

but I wanna load this from Restaurant:: model not from cuisines.. because I have many conditions chained together.. its for a search and filtering / browse page.

Any ideas or ways ? I've been struggling with this for 3 days and still no answer.

Example Models :

class Restaurant extends Eloquent {

    protected $table = 'restaurants';

    public function facilities() {
        return $this->hasOne('Facilities'); 
    }
}

class Facilities extends Eloquent {

    protected $table = 'restaurants_facilities';

    public function restaurant() {
        return $this->belongsTo('Restaurant');
    }

}

PS : This seems to be working.. but this is not Eloquent way right ?

Restaurant::leftJoin(
                'cuisine_restaurant', 
                'cuisine_restaurant.restaurant_id', 
                '=', 'restaurants.id'
             )
             ->where('cuisine_id', 16)
               ->get();

Also what is the best method to find a count of restaurants which have specific column value without another query ? like.. i have to find the total of restaurants which have parking = 1 and wifi = 1 ?

Please help on this.

Thank you.

Answer 1

I don't see anything wrong with doing the left join here, if you have to load from the Restaurant model. I might abstract it away to a method on my Restaurant model, like so:

class Restaurant extends Eloquent {
    protected $table = 'restaurants'; // will be default in latest L4 beta

    public function facility()
    {
      return $this->hasOne('Facility');
    }

    // Or, better, make public, and inject instance to controller.
    public static function withWifi()
    {
      return static::leftJoin(
        'restaurant_facilities',
        'restaurants.id', '=', 'restaurant_facilities.restaurant_id'
      )->where('wifi', '=', 1);
    }
}

And then, from your routes:

Route::get('/', function()
{
  return Restaurant::withWifi()->get();
});

On the go - haven't tested that code, but I think it should work. You could instead use eager loading with a constraint, but that will only specify whether the facility object is null or not. It would still return all restaurants, unless you specify a where clause.

(P.S. I'd stick with the singular form of Facility. Notice how hasOne('Facilities') doesn't read correctly?)