Caching Data

[Robust]

Alright, I'm really confused here. So I'm making a Best Answer/Q&A manager. I've got a few problems, and have tried resolving them for 2 weeks but found no solution. I'd really appreciate some help.

Problem 1: Showing phrase ba_vote or ba_unvote
So, this function almost identically represents the one of XenForo's Like function. The way I currently do it:

If {xen:helper checkVoteStatus, $user} is true, show ba_unvote, else show ba_vote.
checkVoteStatus calls this method:

    /**
     * Gets a best answer content record for a user that has voted on a piece of content.
     *
     * @param integer $postId
     * @param integer $userId
     *
     * @return array|false
     */
    public function getCurrentBestAnswerStatusOnPost($postId, $userId)
    {
        return $this->_getDb()->fetchRow('
            SELECT *
            FROM ba_votes
            WHERE post_id = ?
                AND vote_user_id = ?
        ', array($postId, $userId));
    }

If it's an array (true), it means a vote already exists, so it shows ba_unvote, and if it returns false then no vote already exists, and it shows vote.

This works all fine, but damn that's a lot of queries. That's an extra query per post. I could always make one query per page, using WHERE thread_id = ? instead and then filter based on post IDs, but even then that's poorly inefficient. What's the best way of showing vote or unvote based on if they have already voted?

Problem 2: Submitting Votes / Unvotes
I previously posted a thread on too many DB queries (https://xenforo.com/community/threads/optimising-code-too-many-db-queries.97853/). I got given the following, and it sounds like a good idea, but I'm not completely sure on how to execute it.

Well depending on how often you need to read the votes, you might want to consider storing the votes in a serialized fashion like how XF stores data about likes on a per post basis. It stores the likes, username of who did the like, etc. in a serialized array on a per post basis so it doesn't need to do any extra queries when displaying a post. Then anytime a vote is added/deleted/changed, you update that serialized array for the post table.

I think it means to have an array with the data I'd be inputting into the database, and serialise it (the serialize() function), then insert it into the DataRegistry perhaps? Not really sure on how to execute this.

Thanks for any help you can give

 

[Chris D]

For Problem 1:

The way that XF handles this, is as follows:

When the posts for a thread are fetched, we do a join on the xf_liked_content table - we fetch the "like_date" from that table... a severely cut down version of that query would look something like:

SELECT post.*, liked_content.like_date
FROM xf_post AS post
LEFT JOIN xf_liked_content AS liked_content
    ON (liked_content.content_type = \'post\'
        AND liked_content.content_id = post.post_id
        AND liked_content.like_user_id = ?

That's just a basic concept, the actual practice of doing it is actually quite difficult in XF. It's quite difficult to add additional fetch and join options. But, you get the idea, if the like_date exists for that user and that post in the like table, then the user has liked that post.

There is, however, another way you may want to look at.

What about doing a single query on each page load. You need to know the post IDs which is easy to get if you're extending the controller.

SELECT *
FROM your_table
WHERE post_id IN (?)
AND user_id = ?

You then have a list of all the posts that user has voted on which with a bit more work should be easy to pass to your template and easy to interrogate to see which posts have or haven't been voted for.

Another option is basically problem 2.

The idea here is you have a new field in the post table, e.g. 'votes'.

Each time a vote is added, get a list of all votes for that post into an array, serialize that array and then write it to the votes field. Then, to access that, all you need to do is unserialize it, and it's there, inside every post record, automatically, so you can then use it as you need it without any additional query overhead.

 

[Robust]

@Chris D
I took a look at XenForo's query that it does:

SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
user_privacy.*,
deletion_log.delete_date, deletion_log.delete_reason,
deletion_log.delete_user_id, deletion_log.delete_username,
session_activity.view_date AS last_view_date,
liked_content.like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_user_privacy AS user_privacy ON
(user_privacy.user_id = post.user_id)
LEFT JOIN xf_deletion_log AS deletion_log ON
(deletion_log.content_type = 'post' AND deletion_log.content_id = post.post_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
LEFT JOIN xf_liked_content AS liked_content
ON (liked_content.content_type = 'post'
AND liked_content.content_id = post.post_id
AND liked_content.like_user_id = 1)
WHERE post.thread_id = ?
AND (post.position >= 0 AND post.position < 20)
AND (post.message_state IN ('visible','deleted','moderated'))
ORDER BY post.position ASC, post.post_date ASC

Quite complex but if I wanted to add to that I could do:

LEFT JOIN ba_votes AS voted_content
(ON voted_content.post_id = post.post_id
AND voted_content.vote_user_id = ?)

Or would that not work? I'd also need to figure out how to modify the existing query and add this on without causing conflict with another add-on that's also wanting to add to the query.

 

[Chris D]

I'd also need to figure out how to modify the existing query and add this on without causing conflict with another add-on that's also wanting to add to the query.

Hence my further comments suggesting other ways. Don't do it like that, I just wanted to show you how XF does it. It's not impossible, but I think the other ways are likely going to be easier, and, over time, less painful.