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

XF 1.5 Likes

Discussion in 'XenForo Questions and Support' started by Huskermax, Jul 19, 2016.

  1. Huskermax

    Huskermax Member

    https://xenforo.com/community/resources/post-ratings-taking-likes-to-the-next-level.410/

    Not asking about this ad on.

    I would like to know if their is away to arrange a user list by how many likes they received or how many likes a post or thread received. Is their away to do that or will I need this ad on or something like it?

    I see I can arrange users in admin search by trophy points but it just by points I can't separate it more then that it seems.
     
  2. Brogan

    Brogan XenForo Moderator Staff Member

    The notable members page lists the top 20 members with the most likes: https://xenforo.com/community/members/?type=likes

    Threads within each forum can be sorted on first post likes, using the tools at the bottom of the thread list.

    Anything more than that would require an add-on or custom development.
     
  3. Huskermax

    Huskermax Member

    Ok, is their away to use or run a query in the DB to find out which post has the most likes.

    I would like to give awards based on this. Thread with most likes, post with most likes, user with most likes. If I can find out which user has the most like per forum that would be even better.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    This query should do it:

    Code:
    SELECT sub.node_id, n.title, sub.user_id, u.username, sub.UserLikes
    FROM (
    	SELECT n.node_id, p.user_id, SUM(p.likes) AS 'UserLikes'
    	FROM xf_node AS n
    	LEFT JOIN xf_thread AS t ON (t.node_id = n.node_id)
    	LEFT JOIN xf_post AS p ON (p.thread_id = t.thread_id)
    	GROUP BY n.node_id, p.user_id
    ) sub
    INNER JOIN xf_node n ON (n.node_id = sub.node_id)
    INNER JOIN xf_user u ON (u.user_id = sub.user_id)
    -- null self join to keep only max likes
    LEFT JOIN (
    	SELECT n.node_id, p.user_id, SUM(p.likes) AS 'UserLikes'
    	FROM xf_node AS n
    	LEFT JOIN xf_thread AS t ON (t.node_id = n.node_id)
    	LEFT JOIN xf_post AS p ON (p.thread_id = t.thread_id)
    	GROUP BY n.node_id, p.user_id
    ) self ON (self.node_id = sub.node_id AND self.UserLikes > sub.UserLikes)
    WHERE sub.UserLikes > 0
    AND self.node_id IS NULL
    ORDER BY sub.node_id
    
    Note that this allows for ties so you might have more than one row per forum.
     

Share This Page