XF 2.1 When to use cron vs _postSave() to update count

[AndrewSimm]

Any rule of thumb of when I should update a count in the database vs using cron?

Currently, my addon updates an article_count column on users, which is used to show article count on the member profile and under member statistics.

These are my _postSave() and _postDelete() functions in my Article Entity

    protected function _postSave()
    {
        $db = $this->db();
        $userId = $this->user_id;

        $db->query("
        UPDATE xf_user
        SET article_count = article_count + 1
        WHERE user_id = ?", $userId);
    }

    protected function _postDelete()
    {

        $db = $this->db();
        $userId = $this->user_id;

        $db->query("
        UPDATE xf_user
        SET article_count = article_count - 1
        WHERE user_id = ?", $userId);
    }

 

[AndrewSimm]

Actually I am going to update in cron because the above won't prevent an entry for just saving the article.

 

[Xon]

Rule of thumb; single row updates are fine.

Also; you want to-do this when decreasing a column via a query;

UPDATE xf_user
SET article_count = GREATEST(0, cast(alerts_unread AS SIGNED) -1 )
WHERE user_id = ?

This casts the alerts_unread from an unsigned integer to a signed integer, decrements; and if less than zero clamps to zero. Otherwise you'll get weird SQL type errors if a counter underflow unexpectedly

 

[AndrewSimm]

Rule of thumb; single row updates are fine.

Also; you want to-do this when decreasing a column via a query;

UPDATE xf_user
SET article_count = GREATEST(0, cast(alerts_unread AS SIGNED) -1 )
WHERE user_id = ?

This casts the alerts_unread from an unsigned integer to a signed integer, decrements; and if less than zero clamps to zero. Otherwise you'll get weird SQL type errors if a counter underflow unexpectedly

Thanks, Xon, this is helpful!

What I ended up doing was fetching the number of articles and then updating the field. This prevents me from counting each time save is pressed and from having to create a cron job to do the exact same thing.

   public function updateArticleCount(&$error = null)
    {
        $db = $this->db();
        $userId = $this->user_id;

        $articleCount = $db->fetchOne(
            "SELECT COUNT(*) FROM xf_andrew_articles WHERE user_id = ? AND publish = 1", $userId
        );

        $db->query("
        UPDATE xf_user
        SET article_count = ?
        WHERE user_id = ?", [$articleCount, $userId]
        );
    }

    protected function _postSave()
    {
        $this->updateArticleCount();
    }

 

[AndrewSimm]

Ok, I forgot about isInsert, so I went back to my original way but with Xon's recommendation on the decrement.

    protected function _postSave()
    {

        if ($this->isInsert())
        {
            $db = $this->db();
            $userId = $this->user_id;

            $db->query("
            UPDATE xf_user
            SET article_count = article_count + 1
            WHERE user_id = ?", $userId);

        }
        else if ($this->isUpdate())
        {

            if ($this->getOption('log_moderator'))
            {
                $this->app()->logger()->logModeratorAction('article', $this, 'article_updated');
            }

        }
    }

    protected function _postDelete()
    {
        $db = $this->db();
        $userId = $this->user_id;

        $db->query("
        UPDATE xf_user
        SET article_count = GREATEST(0, cast(article_count AS SIGNED) -1 )
        WHERE user_id = ?", $userId);
    }