Laravel 4 - How to use where conditions for column relations

Question

This is what I want, I have two tables. one is Restaurants and the other is Services.

Tables are simple .. and one-to-one relationships. for example, 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 to do 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 for this?

ps. Sorry for the change from https://stackoverflow.com/a/464616/2127 , but I have a similar problem.

Answer 1

You can use where and other sql based methods for relationship objects.

This means that you can create your own 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 using query areas :

 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 is not exactly what you need, but you probably want to use the query areas to get what you are trying.

The main thing to know is that relationship classes can be used as query builders - for example:

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

Answer 2

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

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