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.

Have a look at this post: http://stackoverflow.com/questions/18634398/eloquent-calling-where-on-a-relation/18634716#18634716 — Rob Gordijn, Sep 19 '13 at 09:56
I think this will output different result, because it will filter second table then join, not join first. i tried this before — Hilton Lam, Sep 19 '13 at 12:28

score 12 · Answer 1 · answered Sep 19 '13 at 12:43

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();

Hello, thanks, but seems you forgot this condition r.name = 'bbq' — Hilton Lam, Sep 20 '13 at 02:08

score 1 · Answer 2 · answered Sep 19 '13 at 04:04

1

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();

answered Sep 19 '13 at 04:04

Antonio Carlos Ribeiro

86,191
22
213
204

1

thanks, i also know this method. but can i use eloquent to do this, if can't? how do i know when should i use querybuilder? i think eloquent can't do complex query? am i right? – Hilton Lam Sep 19 '13 at 12:29

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

2 Answers2

Linked