Recounting Votes


Well-known member
Well, I have this Q&A add-on and each thread has a best answer. The table xf_thread has a column bestanswer with the post ID of the best answer in the thread. This is updated after every vote. At the moment I'm thinking of having a function that runs after every vote (similar to a like) is made. The function uses the table ba_votes and goes through every post in x thread, then finds the post with the most votes (so the amount of occurrences of a post_id in the fetchall). Then this is set as the post bestanswer over in xf_thread.

Is there a more efficient way to do this? Seems like it could be made more efficient.


Well-known member
So far my crazy function is like this (mock, not tried)

    public function recountBestAnswerInThread($threadId)
        $db = $this->_getDb();
        $query = $db->fetchAll('SELECT *
                          FROM ba_votes
                          WHERE thread_id = ?
        ', $threadId);
        $data = array();

        foreach($query AS $row => $rowData)
            $data[''] = $rowData['post_id'];
            $data[$rowData['post_id']][''] = $rowData['votes'];

Luke F

Well-known member
Conceptually that is pretty much the best way of doing it (i.e. most efficient in this scenario without sacrificing realtime results)

Your code doesn't look right at all though, and I think the way you're doing the query is why you think it will be inefficient (which it will to some extent)

You want something like:

select count(*) as num_votes, post_id
from ba_votes
where thread_id = ?
group by post_id
order by num_votes desc
limit 1
You'd then run that with fetchRow not fetchAll

Edit: To improve robustness you could add an inner join to xf_post in there, just on the off-chance the most voted post is deleted.
Last edited: