MySQL: 4 Table "has-many-through" Join?
- by Nebs
Let's say I have the following 4 tables (for examples' sake): Owners, Trucks, Boxes, Apples.
An owner can have many trucks, a truck can have many boxes and a box can have many apples.
Owners have an id. Trucks have an id and owner_id. Boxes have an id and truck_id. Apples have an id and box_id.
Let's say I want to get all the apples "owned" by an owner with id = 34. So I want to get all the boxes that are in boxes that are in trucks that owner 34 owns.
There is a "hierarchy" if you will of 4 tables that each only has reference to its direct "parent". How can I quickly filter boxes while satisfying conditions across the other 3 tables?
I hope that made sense somewhat.
Thanks.