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?)

Answer 2

I stumbled across this post while trying to improve my REST API methodology when building a new sharing paradigm. You want to use Eager Loading Constraints. Let's say you have an api route where your loading a shared item and it's collection of subitems such as this:

/api/shared/{share_id}/subitem/{subitem_id}

When hitting this route with a GET request, you want to load that specific subitem. Granted you could just load that model by that id, but what if we need to validate if the user has access to that shared item in the first place? One answer recommended loading the inversed relationship, but this could lead to a confusing and muddled controller very quickly. Using constraints on the eager load is a more 'eloquent' approach. So we'd load it like this:

$shared = Shared::where('id', $share_id)
  ->with([ 'subitems' => function($query) use ($subitem_id) {
    $query->where('subitem_id', $subitem_id)
  }]);

So where only want the subitem that has that id. Now we can check if it was found or not by doing something like this:

if ($shared->subitems->isEmpty())

Since subitems is a collection (array of subitems) we return the subitem[0] with this:

return $shared->subitems[0];