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

Recounting Votes

Discussion in 'XenForo Development Discussions' started by Robust, Apr 26, 2015.

  1. Robust

    Robust 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.
  2. Robust

    Robust 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 = ?
    $data = array();

    $query AS $row => $rowData)
    $data[''] = $rowData['post_id'];
    $data[$rowData['post_id']][''] = $rowData['votes'];
  3. Luke F

    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: Apr 27, 2015
    TheBigK and Robust like this.

Share This Page