Friday, July 11, 2014

Cake splitting up my queries

I have an issue where CakePHP is splitting what should be one query with a couple left joins into multiple queries using IN.

My structure is like this,
Users have ships,
Ships belong to a type, that type defines the properties of the ship
Type has slots,
Given a particular ship, a slot can have one component plugged in.

My current attempt, the issue is that Cake is splitting it into multiple queries so when we finally get down to ship components the table Ship is no longer present.
$ships = $this->Ship->find('all', [
            'conditions' => ['Ship.user_id' => 5],
            'contain' => [
                'ShipType' => [
                    'ShipSlot' => [
                        'ShipComponent' => [
                            'conditions' => [
                                'ShipComponent.ship_id = Ship.id'
                            ]
                        ],
                    ]
                ]
            ]
        ]);
The resulting sql is something like
select * from ships left join ship_types where ships.user_id = 5 // (is there a way to do a belongsTo that inner joins instead of left joins, a left join is really unnecessary)
select * from ship_slots where ship_slots.type in (1,2,3)
select * from ship_component where s_c.ship_slot_id in (1,2,3,4,5,6,7,8,9) and s_c.ship_id = ships.id   <--- error, ships not found
What I would like
select *  from ships  inner join ship_types on ship_types.id = ships.ship_type_id  left join ship_slots on ship_slots.ship_type_id = ship_types.id  left join ship_components on ship_components.ship_id = ships.id and ship_components.ship_slot_id = ship_slots.id  where ships.user_id = 5
How can I tell the ORM to not split up my queries?

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.

No comments: