Cakephp 3: Countercache with conditions on the target model

Question

I am trying to count the number of Spots associated with a Plan, but limited to Spots downloaded after the plans renewal date. Hope that makes sense. I would image something like this, but it doesn't work:

class SpotsTable extends Table
{
    public function initialize(array $config)
    {
        $this->addBehavior('CounterCache', [
            'Plan' => [
                'creditsUsed' => [
                    'conditions' => [
                        'downloaded >' => 'Plan.renewed'
                    ]
                ]
            ]
        ]);
        ...
    }
...
}

Basically right now it acts as though Plan.renewed means NULL. Is this possible, or am I on the wrong track?

Answer 1

Two problems

1. Identifiers cannot be passed as string values

When using the key => value format, the value side will always be subject to binding/escaping/casting unless it's an expression object, so since the downloaded column is probably a date/time type, you'll end up with Plan.renewed being bound as a string, thus the final SQL will be something like:

downloaded > 'Plan.renewed'

which probably always results in false. Long story short, use for example an identifier expression:

'Spots.downloaded >' => new \Cake\Database\Expression\IdentifierExpression('Plan.renewed')

2. The counter query doesn't have access to the associated table

Plan.renewed will not be accessible in the query generated by the counter cache behavior, it will not automatically contain/join associations, it will create a simple query with a condition based on the foreign key value in the currently processed Spot entity.

So you have to use a custom/modified query, for example using a custom finder, something like this:

'creditsUsed' => [
    'finder' => 'downloadedAfterPlanRenewal'
]

// in SpotsTable

public function findDownloadedAfterPlanRenewal(\Cake\ORM\Query $query, array $options)
{
    return $query
        ->innerJoinWith('Plan')
        ->where([
            'Spots.downloaded >' => $query->identifier('Plan.renewed')
        ]);
}

This will properly join in the association, so that you can compare with a field from Plan. The original primary key conditions generated by the behavior will already be applied on the given query object.

See also

• Cookbook > Database Access & ORM > Behaviors > CounterCache > Advanced Usage
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods