cakephpcakephp-3.0counter-cache

CakePHP 3 CounterCache - don't start counting with 0

I'm trying to migrate a IP-based voting system to logged-in-user-based voting system and would like to keep the old votes/likes. So i copied the old systems like_count into a column in my quotes table.

quotes table

enter image description here

votes table

votes

I implemented countercache behaviour for like_count in quotes table based on the votes in votes table. But if for example I'd vote the quote with id 145 up now the like_count would jump to 1 instead of 3616, because there's only 1 vote for quote id 145 in votes table.

So is there a way for the CounterCache behaviour to take into account the like_count column and start from there instead from 0?

Solution

  • You have to account for that on your own, there's no out of the box functionality for such a case.

    I'd suggest to store the old votes in a separate column, say legacy_like_count, and then either calculate the sum of the old and new votes/likes when you read the data, being it manually, or for example using a virtual property, like:

    protected $_virtual = ['total_like_count'];

public function _getTotalLikeCount() {
    return $this->_properties['like_count'] + $this->_properties['legacy_like_count'];
}

    or if you want the total count stored in the database, use the callback functionality supported by the counter cache behavior, where you can build a custom query that counts the new votes, and add the old number of likes, for example:

    [
    'Quotes' => [
        'like_count' => function (
            \Cake\Event\Event $event,
            \Cake\Datasource\EntityInterface $entity,
            \Cake\ORM\Table $table
        ) {
            // $event = Model.afterSave or Model.afterDelete (VotesTable)
            // $entity = Vote 
            // $table = VotesTable

            $votes = $table
                ->find()
                ->where([
                    'Votes.quote_id' => $entity->get('quote_id')
                ])
                ->count();

            $quote = $table->Quotes->get($entity->get('quote_id'));

            return $votes + $quote->get('legacy_like_count');
         }
    ]
]

    You could probably also do the calculation on SQL level, something along the lines of this:

    $query = $table->Quotes->find();
return $query
    ->select([
        'totalVotes' => $query
            ->newExpr()
            ->add([
                'Quotes.legacy_like_count',
                $query->func()->count('Votes.id')
            ])
            ->setConjunction('+') // use tieWith() in CakePHP < 3.4
    ])
    ->leftJoinWith('Votes')
    ->where([
        'Quotes.id' => $entity->get('quote_id')
    ])
    ->groupBy('Quotes.id');

    which would produce SQL similar to the following, which would (when returned as in the example) then be used as a subquery in the updating process of the like_count column:

    SELECT
    (Quotes.legacy_like_count + COUNT(Votes.id)) AS totalVotes
FROM
    quotes Quotes
LEFT JOIN
    votes Votes ON Quotes.id = Votes.quote_id
WHERE
    Quotes.id = :c0
GROUP BY
    Quotes.id

    Please note that this is all untested example code!

    See also