Laravel: Filter nested relationships?

Question

I don't understand why this isn't working:

Device::with(['travel.move.position' => function($q) use ($start, $end) {
    return $q->whereBetween('created_date', [$start, $end]);
}]);

I think it should give me all devices with travel->move->position relations but only those that position.datetime is between $start and $end

I tried something like

Device::whereHas('travel.move.position', function($q) use ($start, $end) {
    return $q->whereBetween('created_date', [$start, $end]);
});

and I get the expected result but I don't get the eager relationship.

ADDED QUERY FOR ANSWER 1:

select * 
from `devices` 
where exists (
    select * from `travels` 
    inner join `travel_info` on `travel_info`.`id` = `travels`.`info_id` 
    where `devices`.`id` = `travel_info`.`device_id` 
    and exists (
        select * 
        from `moves` 
        where `moves`.`travel_id` = `travels`.`id` 
        and exists (
            select * from `positions` 
            where `moves`.`position_id` = `positions`.`id` and `created_at`
            between ? and ?
        )
    )

Query is perfect (like was the whereHas) but when do the with():

select `travels`.*, `travel_info`.`device_id` 
from `travels` 
inner join `travel_info` on `travel_info`.`id` = `travels`.`info_id` 
where `travel_info`.`device_id` in (?, ?, ?) 

.... 
select * from `moves` where `moves`.`travel_id` in (?, ..., ?)
....
select * from `positions` where `positions`.`id` in (?, ..., ?)

This code is only filtering the position, not the device. So you will still get all devices, but within each device->travel->move relationship, you'll see only positions that match your filter. — Armin Sam
– Armin Sam, Commented Oct 20, 2016 at 10:52
Is your field really called datetime? Since this is a reserved word in mysql: dev.mysql.com/doc/refman/5.7/en/keywords.html I'm thinking that you might need to quote it whereBetween('datetime', [$start, $end]); — Cyclonecode
– Cyclonecode, Commented Oct 20, 2016 at 10:52
try to add with('travel.move.position') to your second query. It then gives you the expected result plus the eager loaded relationships. — Armin Sam
– Armin Sam, Commented Oct 20, 2016 at 10:55
@Cyclone . No it is a spanish word that means the same but I changed for better understanding. — kanashin
– kanashin, Commented Oct 20, 2016 at 10:56
@kanashin - haha okej then I understand - perhaps you should change it to something else like created_time etc =) — Cyclonecode
– Cyclonecode, Commented Oct 20, 2016 at 10:57

Armin Sam · Accepted Answer · 2016-10-20 14:16:44Z

6

Ok, after a long discussion with the question author, we found the problem is not the query itself, but rather incorrect loading of the relationships in the with() statement.

I came up with the following query that seems to return the expected result. It first do a main query to get all devices matching the filter criteria, and then apply the same filter to each of the relationships we need to eager load:

$start = '2016-10-01';
$end = '2016-10-31';

$devices = Device::whereHas('travel.move.position', function($q) use ($start, $end) {
    $q->whereBetween('created_at', [$start, $end]);
})->with([
    'travel' => function($q1) use ($start, $end) {
        $q1->whereHas('move.position', function($q2) use ($start, $end) {
            $q2->whereBetween('created_at', [$start, $end]);
        });
    },
    'travel.move' => function($q1) use ($start, $end) {
        $q1->whereHas('position', function($q2) use ($start, $end) {
            $q2->whereBetween('created_at', [$start, $end]);
        });
    },
    'travel.move.position' => function($q1) use ($start, $end) {
        $q1->whereBetween('created_at', [$start, $end]);
    }
])->get();

edited Oct 20, 2016 at 14:16

answered Oct 20, 2016 at 11:27

Armin Sam

9339 silver badges23 bronze badges

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

7 Comments

kanashin Over a year ago

Thank you but is the same: I get a device->travel->move->position->created_at == '2016-09-23'

Armin Sam Over a year ago

Could you switch on your database query logging and check the SQL query for the code above and post it in your question?

Armin Sam Over a year ago

Can one move have multiple positions?

kanashin Over a year ago

No, it can't have more than one. It's a belongs to relationship

Armin Sam Over a year ago

There are two suspicious things in the SQL queries you added: 1. where moves.position_id = positions.id and created_at ... why created_at is not prefixed with its table name, e.g. positions.created_at? 2. select * from positions where positions.id in (?, ..., ?) is not correct. It should be ... positions.move_id in (?, ..., ?)

|

Collectives™ on Stack Overflow

Laravel: Filter nested relationships?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related