Manipulating Order of JOINS in CakePHP

Question

I have the following problem with CakePHP:

In my model, Deposit belongsTo Account, and Account belongsTo Customer.

When querying Deposits, I get the Account information, but not the Customer's, by default.

If I set Deposit->recursive to 2, I get the Customer information (and a whole lot more), but CakePHP esentially throws one SELECT per each deposit, which is quite bad in this case.

So, I did this:

'joins' => array(
    array('table'=>'customers', 'alias'=>'Customer', 'type'=>'left', 'foreignKey' => false, 'conditions'=>array('Account.customer_id = Customer.id'))
)

which almost works...

What I get from that is esentially:

SELECT (...) FROM Deposits LEFT JOIN Customers LEFT JOIN Accounts

instead of

SELECT (...) FROM Deposits LEFT JOIN Accounts LEFT JOIN Customers

which of course doesn't work.

Is there anyway to specify that "my custom joins" should go after the "regular model joins"?
Or do I have to manually unbind Deposit from Account, and specify both joins manually?

Thanks!
Daniel

Answer 1

You can use the Containable behavior to select just what you want, so from your Deposits controller:

$this->Deposit->find('all', array(
    // conditions
    'contain' => array('Account' => array('Customer'))
));

Just be sure to add the actsAs variable to the class. Here's more info. http://book.cakephp.org/view/1323/Containable

Answer 2

You need to unbind models that you want to be at the top and include the appropriate arrays in 'joins' array. See this question for details