CakePHP counterCache joining irrelevant tables to update counter

Question

I have a User model and a Message model.

The Message model is linked to the User model twice like this:

public $belongsTo = array(
    'UserSender' => array(
        'className' => 'User',
        'foreignKey' => 'sender_id',
        'counterCache' => array(
            'messages_sent_count' => array(
                'is_deleted' => FALSE
            )
        )
    ),
    'UserRecipient' => array(
        'className' => 'User',
        'foreignKey' => 'recipient_id',
        'counterCache' => array(
            'messages_received_count' => array(
                'is_deleted' => FALSE
            ),
            'messages_unread_count' => array(
                'is_deleted' => FALSE,
                'is_read' => FALSE
            )
        )
    ),
    'Operator' => array(
        'className' => 'Operator',
        'foreignKey' => 'operator_id'
    )
);

Besides the User model, the Message model also $belongsTo the Operator model. The Operator model is irrelevant to the message count for the users, but its table is still being joined in the count query, as debug shows:

'query' => 'SELECT COUNT(*) AS `count` FROM `database`.`messages` AS  `Message` LEFT JOIN `database`.`operators` AS `Operator` ON (`Message`.`operator_id` = `Operator`.`id`) LEFT JOIN `database`.`users` AS `UserSender` ON (`Message`.`sender_id` = `UserSender`.`id`) LEFT JOIN `database`.`users` AS `UserRecipient` ON (`Message`.`recipient_id` = `UserRecipient`.`id`)  WHERE `Message`.`is_deleted` = '0' AND `Message`.`sender_id` = 389',
        'params' => array(),
        'affected' => (int) 1,
        'numRows' => (int) 1,
        'took' => (float) 394

For the sake of simplicity I've actually excluded one more model that the Message model $belongsTo, but the above query shows the problem.

The counterCache function does a quite expensive query just to update the counter. Is there a way to maybe override or adjust the counterCache method to not join irrelevant tables in the query?

this query doesn't update the counter.and hwo are you defining the counterCache? it is suppose to be boolean in cake 2.x. — Sougata Bose
You're right. The next query in the log actually updates the counter. But this SELECT query is actually the problem, because it joins tables that don't need to be selected for the update. The counterCache counts the messages based on the criteria defined in the counterScope (is_read, is_deleted, etc) and writes them to the user table. — Botch

ndm ndm · Accepted Answer · 2014-08-01T15:51:38

I can't test it right now, but since the recursive setting used by Model::updateCounterCache() is hard-coded based on whether conditions are defined for the counter cache field, the only way to change this (besides completely reimplementing Model::updateCounterCache()) is probably to modify the count query in Model::_findCount() or Model::beforeFind() of your Message model.

public function beforeFind($query) {
    // ... figure whether this is the count query for updateCounterCache,
    // maybe even try to analyze whether the passed conditions require
    // joins or not.

    if(/* ... */) {
        $query['recursive'] = -1;
    }

    return $query;
}

Depending on how much control you'll actually need the containable behavior might do the trick too, it sets recursive to -1 in case no containments are being passed

$Message->contain(); // === recursive is being set to -1 in before find callback
$Message->delete(123);

CakePHP counterCache joining irrelevant tables to update counter

1 Answers