Fixed Inefficient query in XF\ReactionAbstractHandler::updateRecentCacheForUserChange

[Kirby]

Affected version

2.2.13

\XF::db()->query("
    UPDATE (
        SELECT content_id FROM xf_reaction_content
        WHERE content_type = ?
        AND reaction_user_id = ?
    ) AS temp
    INNER JOIN {$table} AS reaction_table ON (reaction_table.`$primaryKey` = temp.content_id)
    SET reaction_table.`{$recentField}` = REPLACE(reaction_table.`{$recentField}`, ?, ?)
", [$this->contentType, $newUserId, $oldFind, $newReplace]);

This query tries to update all records for $newUserId, even those whre nothing needs to be changed.

In case $newUserId is 0 this could hit a lot of unaffected records (if there are already many reactions by deleted users).
See related issue https://xenforo.com/community/threa...ce-when-renaming-a-user-upon-deletion.217244/

Changing this to smth. like

\XF::db()->query("
    UPDATE (
        SELECT content_id FROM xf_reaction_content
        WHERE content_type = ?
        AND reaction_user_id = ?
    ) AS temp
    INNER JOIN {$table} AS reaction_table ON (reaction_table.`$primaryKey` = temp.content_id)
    SET reaction_table.`{$recentField}` = REPLACE(reaction_table.`{$recentField}`, ?, ?)
    WHERE reaction_table.`{$recentField}` LIKE ?
", [$this->contentType, $newUserId, $oldFind, $newReplace, '%' . \XF::db()->escapeLike($oldFind) . '%']);

seems to significantly speed up the query in those cases.

 

[dethfire]

Can we get this fixed please

 

[XF Bug Bot]

Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.14).

Change log:

Use a more efficient query when updating reaction caches for content

There may be a delay before changes are rolled out to the XenForo Community.