6

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.

Community
  • 1
  • 1
Hilton Lam
  • 73
  • 1
  • 7
  • 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

2 Answers2

12

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();
fideloper
  • 12,213
  • 1
  • 41
  • 38
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();
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