• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Recounting Votes

Robust

Well-known member
#1
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

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

PHP:
    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
#3
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:

Code:
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: