CakePHP not joining the correct tables in find query

Question

OK, another cake question from me.

I have rather a complex table structure with quite a few joins. Here's the jist of it:

So, in summary:

RiskCategory $hasMany Client   [Client $belongsTo RiskCategory]
Client $hasMany Account        [Account $belongsTo Client]
Account $hasMany Holding       [Holding $belongsTo Account]

In my RiskCategory model I want to run a query that basically returns the sum of the holdings in each riskCategory id, grouped by date. There's a few other conditions but here is what I've put together:

$findParams = array(
    'recursive' => 4,
    'fields' => array(
        'Holding.holding_date',
        'SUM(Holding.value) AS risk_category_value'
        ),
    'group' => array('Holding.holding_date'),
    'order' => 'Holding.holding_date ASC'
    );

$findParams['conditions'] = array(
    'Client.active' => true,
    'Client.model' => true,
    'Client.currency' => 'GBP',
    'OR' => array(
        'Holding.holding_date' => $this->end_date,
        array(
            'Holding.holding_date = LAST_DAY(Holding.holding_date)',
            'MONTH(Holding.holding_date)' => array(3,6,9,12)
            )
        )
    );

$valuations = $this->Client->Account->Holding->find( 'all', $findParams );

When I run the above cake is giving me an error saying various fields are not present in the query. The raw query created is as follows:

SELECT `Holding`.`holding_date`, 
       Sum(`Holding`.`value`) AS risk_category_value 
FROM   `ips_client_db`.`holdings` AS `Holding` 
       LEFT JOIN `ips_client_db`.`accounts` AS `Account` 
              ON ( `Holding`.`account_id` = `Account`.`id` ) 
       LEFT JOIN `ips_client_db`.`sedols` AS `Sedol` 
              ON ( `Holding`.`sedols_id` = `Sedol`.`id` ) 
WHERE  `client`.`active` = '1' 
       AND `client`.`model` = '1' 
       AND `client`.`currency` = 'GBP' 
       AND ( ( `Holding`.`holding_date` = '2013-10-01' ) 
              OR (( ( `Holding`.`holding_date` = Last_day( 
                      `Holding`.`holding_date`) ) 
                    AND ( Month(`Holding`.`holding_date`) IN ( 3, 6, 9, 12 ) ) ) 
                 ) ) 
GROUP  BY `Holding`.`holding_date` 
ORDER  BY `Holding`.`holding_date` ASC 

It looks as though cake is not doing all the joins. It is only joining Account to Holding and then Holding to Sedol (which is another joined table in the database but is not needed for this query so I've omitted it from the diagram)

Why are the joins not being made properly and how to acheive this? I'd like to avoid writing a raw statement if possible.

EDIT: The joins should be as follows:

...
FROM   risk_categories 
   LEFT JOIN ((clients 
               LEFT JOIN accounts 
                      ON clients.id = accounts.client_id) 
              LEFT JOIN holdings 
                     ON accounts.id = holdings.account_id) 
          ON risk_categories.id = clients.risk_category_id 
...

Answer 1

CakePHP does not perform deep joins for associations that are more than 1 level deep. That is why you're getting an error for references to the Client table in the conditions.

For these types of problems it's easier to use the Containable behavior. I usually add this behavior to my AppModel as the default handler for associations.

Containable allows you to define the associations (only those already defined) with their fields and conditions as part of the find operation. This is done by adding a new contain key in the find parameters. Below I've taking a guess at what you might need.

$findParams = array(
    'fields' => array(
        'Holding.holding_date',
        'SUM(Holding.value) AS risk_category_value'
        ),
    'conditions'=>array(
        'OR' => array(
            'Holding.holding_date' => $this->end_date,
            array(
                'Holding.holding_date = LAST_DAY(Holding.holding_date)',
                'MONTH(Holding.holding_date)' => array(3,6,9,12)
                )
            )
    ),
    'group' => array('Holding.holding_date'),
    'order' => 'Holding.holding_date ASC',
    'contain'=>array(
        'Account'=>array(
           'Client'=>array(
                'RiskCategory',
                'conditions'=>array(
                    'Client.active' => true,
                    'Client.model' => true,
                    'Client.currency' => 'GBP',
                )
           )
        )
    )
);

$valuations = $this->Client->Account->Holding->find( 'all', $findParams );