CODEWITHSUNDEEP
cakephpjoinmodel
Daniel Magliola
Daniel Magliola

Reputation: 32392

Manipulating Order of JOINS in CakePHP

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

Upvotes: 4

Views: 2714

Answers (4)

yohannan_sobin
yohannan_sobin

Reputation: 917

I too had this situation and I was almost stuck then. After doing some search I found that there are 2 ways to do it.

  1. Edit the core DboSource.php file to change the joins order. (How to change the sequence of 'joins' in CakePHP?)

  2. Or we need to explicitly specify our association in $joins array. Then use recursive -1 in query.

I hope there are the possible options now. If you have any other interesting way please post here!

Upvotes: 0

Stephen
Stephen

Reputation: 18964

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/2.0/en/core-libraries/behaviors/containable.html

Upvotes: 2

Andy Hobbs
Andy Hobbs

Reputation: 404

I realise this is an old question but it is still relevant...

Cake does look through existing joins before adding in new joins to satisfy associations. After some digging in dbo_source.php (cake 1.3, also applies to 2.x) I have that by specifying your "joins" exactly how cake builds them you can effectively override the order of the joins.

So, in your case here:

'joins' => array(
array(
    'table' => '`accounts`', 
    'alias' => 'Account', 
    'type' => 'Left', 
    'conditions' => '`Deposit`.`account_id` = `Account`.`id`'
),
array(
    'table' => '`customers`', 
    'alias' => 'Customer', 
    'type' => 'Left', 
    'conditions' => '`Account`.`customer_id` = `Customer`.`id`'
)
)

Cake using in_array() to do this so note the `` around table and field names, capitalisation of LEFT and not using an array for the conditions as these are required to make the comparison work.

Upvotes: 0

bancer
bancer

Reputation: 7525

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

Upvotes: 2

Related Questions