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

XF 1.1 Most liked posts in a single node in the last month

Discussion in 'XenForo Questions and Support' started by jgas, Oct 21, 2011.

  1. jgas

    jgas Active Member

    On my board, we run a monthly contest, where the most voted song is elected "song of the month".

    I want to use the like system to count the votes, so I need to know if it is possibile to search for the "most liked posts" in the latest x days, for a single node/category/forum.

    Many thanks!
     
    HydraulicJack likes this.
  2. Brogan

    Brogan XenForo Moderator Staff Member

    You can filter threads by first message likes, but not individual posts.
     
    HydraulicJack likes this.
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Run this query on your database:

    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.node_id = 10
    GROUP BY lc.content_id
    ORDER BY likes
    DESC;
    
    You will get results like this:

    Screen shot 2011-10-21 at 1.24.30 PM.png
     
    Blue and HydraulicJack like this.
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Oh, you want a time period:

    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.node_id = 10
    AND lc.like_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
    GROUP BY lc.content_id
    ORDER BY likes
    DESC;
    
     
  5. jgas

    jgas Active Member

    Thanks jake, you really know the answers!
    Since it's a work that i need to do every month, or even more if i want some stats about the "likeness" of my posts, it would be great to have a page where i can choose the node and the period of time.
    Is it hard to code a page like this? Who can help me?
     
    HydraulicJack and Floris like this.
  6. jgas

    jgas Active Member

    By the way, this is interesting too. How can i do it?

    Edit : i found it out :it's /n the thread display option on the bottom of every threadlist page!
     
    HydraulicJack likes this.
  7. Barbossa

    Barbossa Active Member

    Jake, how bout, most liked user in a single/all node in the last month? Thank you.
     
  8. Blue

    Blue Well-Known Member

    I would like to be able to list the the most liked user / topic for the whole forum in the last 30 days.

    Very useful, thanks Jake.
     

Share This Page