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

SQL Query Help - Most Likes / Liked Post In Specific Thread

Discussion in 'General PHP and MySQL Discussions' started by ProCom, Dec 29, 2013.

  1. ProCom

    ProCom Active Member

    We often do contests where we use the "like" system to have members vote for the post they like the best within the entire thread.

    Sometimes the contests can have hundreds of submissions which make it very difficult to go through and find the post with the most likes.

    I don't need an addon... just a quick and easy SQL command to let me pull the data.

    @Jake Bunce did a great job doing a query for "most likes within a node" here: http://xenforo.com/community/threads/most-liked-posts-in-a-single-node-in-the-last-month.21893/

    ... but I need most likes within a specific thread ID. Anybody know which query I should run (assuming the ID for the thread is 1234)

    Thanks!
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Code:
    SELECT COUNT(*) AS likes, t.title, p.post_id
    FROM xf_liked_content AS lc
    LEFT JOIN xf_post AS p ON (p.post_id = lc.content_id)
    LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
    WHERE lc.content_type = 'post'
    AND t.thread_id = 9759
    GROUP BY lc.content_id
    ORDER BY likes
    DESC;
    
     
    ProCom likes this.
  3. ProCom

    ProCom Active Member

    Again I find myself bowing down to @Jake Bunce !

    It worked perfectly, thanks!

    Now, a quick follow up if you don't mind: Say I have a post that has the highest likes and it is post_id # 1234567, how do I create a URL / link directly to that post? For example, if the thread URL is:

    http://www.mydomain.com/threads/contest.9759/

    Is there a way to link directly to the post_id 1234567 in that thread?
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    ProCom likes this.
  5. ProCom

    ProCom Active Member

    Ugh, so brilliant, thanks @Brogan !

    I'm loving xF more and more with each passing post! :D
     

Share This Page