Laravel Eloquent: filtering model by relation table

Question

I have places and locations tables. Place could have many locations. Location belongs to Place.

Place: id title

Location: id place_id floor lat lon

class Location extends Model {

    public function place()
    {
        return $this->belongsTo('App\Place');
    }

}

And

class Place extends Model {

    public function locations()
    {
        return $this->hasMany('App\Location');
    }

}

And i need to find places, that belongs only to 1st floor. select * from places inner join locations on places.id = locations.place_id where locations.floor = 1

How does it should be done in Eloquent?

Is something similar to Place::where('locations.floor', '=', 1)->get() exists?

Yes, i know there is whereHas:

Place::whereHas('locations', function($q)
{
    $q->where('floor', '=', 1);
})->get()

but it generates a bit complex query with counts:

select * from `places` where (select count(*) from `locations` where `locations`.`place_id` = `places`.`id` and `floor` = '1') >= 1

What's the problem with the query that it's generating through the whereHas? — Samsquanch
– Samsquanch, Commented May 15, 2015 at 15:10
the problem is that it's strange query with aggregate count() function. maybe, just maybe, that behind the engine mysql translate this to simple join (or join to this, i really don't know). is it equal to join method, that i provide? — vedmaque
– vedmaque, Commented May 16, 2015 at 0:11
The thing is, with Laravel a join doesn't happen until it needs to happen. It performs the most efficient request possible at each step. In this case, since a join isn't required, a join isn't performed and a count() is done instead. If you're just wanting to do this for the sake of efficiency, you're either over-optimizing or you shouldn't be using Eloquent as it is extremely slow and resource-heavy. — Samsquanch
– Samsquanch, Commented May 16, 2015 at 13:52

skazska · Accepted Answer · 2015-05-15 15:43:13Z

0

does not this works?

class Location extends Model {

    public function place()
    {
        return $this->belongsTo('App\Place');
    }

}

$locations = Location::where('floor', '=', 1);
$locations->load('place'); //lazy eager loading to reduce queries number
$locations->each(function($location){ 
    $place = $location->place
    //this will run for each found location 
});

finally, any orm is not for database usage optimization, and it is not worth to expect nice sql's produced by it.

edited May 15, 2015 at 15:43

answered May 15, 2015 at 14:59

skazska

4312 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vedmaque Over a year ago

1 location -> 1 place. So this code provide me only one place, not all from 1st floor.

skazska Over a year ago

loop through $locations, i used first() just for demo purposes. ` $locations->each(function($location){ $place = $location->place });`

vedmaque Over a year ago

yes, i understand that i can loop, but it's a bit complex for this simple operation. but thank you for the answer, i think that i will use your approach... mine confuses me =(

zeratulmdq · Accepted Answer · 2015-05-15 15:53:41Z

0

I haven't tried this, but you have eager loading and you can have a condition:

$places = Place::with(['locations' => function($query)
{
    $query->where('floor', '=', 1);

}])->get();

Source

edited May 15, 2015 at 15:53

answered May 15, 2015 at 15:18

zeratulmdq

1,49416 silver badges15 bronze badges

1 Comment

vedmaque Over a year ago

sadly, it doesn't work, this code means that you grab all places, but load relations only for those, which on 1st floor.

John Slegers · Accepted Answer · 2016-01-14 02:54:58Z

0

Try this :

Place::join('locations', 'places.id', '=', 'locations.place_id')
->where('locations.floor', 1)
->select('places.*')
->get();

edited Jan 14, 2016 at 2:54

John Slegers

47.4k23 gold badges205 silver badges173 bronze badges

answered Jan 13, 2016 at 16:40

jeilsoft

5825 silver badges13 bronze badges

Collectives™ on Stack Overflow

Laravel Eloquent: filtering model by relation table

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related