cakephpbehaviorcakephp-3.xcounter-cache

Cakephp 3: Countercache with conditions on the target model

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?

Solution

  • 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