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

XF 1.5 Likes

#1
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.
 
#3
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.
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.
 

Jake Bunce

XenForo moderator
Staff member
#4
If I can find out which user has the most like per forum that would be even better.
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.