Laravel 4 - How to use where conditions for relation's column

Question

This is what I want, I have two tables. one is 'Restaurants' and other is '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 facilities with id, restaurant_id, wifi, parking, etc

Here are my models:

class Restaurant extends Eloquent {

protected $table = 'restaurants';

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

class Facilities extends Eloquent {

protected $table = 'facilities';

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


}

I want do like this Select * from restaurants r left join facilities rf on r.id=rf.restaurant_id where r.name = 'bbq' and rf.wifi != '1'.

How to use Eloquent to do that?

ps. sorry for modify from https://stackoverflow.com/questions/14621943/laravel-how-to-use-where-conditions-for-relations-column#= , but I have the similar problem.

Answer 1

You can use where and other sql-based methods on the relationship objects.

That means you can either create a custom method in your model:

class Restaurant extends Eloquent {

    protected $table = 'restaurants';

    public function facilities($wifi) {
        return $this->belongsTo('Facility')->where('wifi', '=', $wifi);
    }
}

Or you can try to use query scopes:

class Restaurant extends Eloquent {

    protected $table = 'restaurants';

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

    public function scopeFiltered($query, $wifi)
    {
        return $query->where('wifi', '>', 100);
    }
}

Then:

$wifi = 1;
$restaurants = Restaurant::facilities()->filtered($wifi)->get();

This isn't exactly what you need likely, but query scopes is likely what you want to use to get what you're attempting.

THe key point is to know that relationship classes can be used like query builders - for example:

$this->belongsTo('Facility')->where('wifi', '=', $wifi)->orderBy('whatever', 'asc')->get();

Answer 2

There are some ways to filter both, this is using QueryBuilder:

Restaurant::join('facilities','facilities.restaurant_id','=','restaurants.id')
                ->where('name','bbq')
                ->where('facilities.wifi','!=', 1)
                ->get();