Recounting Votes

[Robust]

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.

 

[Robust]

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]

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.