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

Query to list members by "likes received" total for a single thread?

CyclingTribe

Well-known member
#1
Would one of you SQL whizzes mind giving me a query that will display the number of likes each member has received from within a specific thread ID - so it displays something like this:

Member C - 1478
Member T - 1293
Member G - 754
Member A - 15

Many thanks,
Shaun :D
 

Chris D

XenForo developer
Staff member
#2
Try this:
Code:
SELECT p.username, COUNT(*) AS like_count
FROM xf_liked_content AS lc
INNER JOIN xf_post AS p ON
    (lc.content_type = 'post' AND lc.content_id = p.post_id)
WHERE p.message_state = 'visible'
    AND p.thread_id = 9999
GROUP BY lc.content_user_id
ORDER BY like_count DESC
Change thread_id = 9999 to whichever thread ID you desire.
 

CyclingTribe

Well-known member
#3
Try this:
Code:
SELECT p.username, COUNT(*) AS like_count
FROM xf_liked_content AS lc
INNER JOIN xf_post AS p ON
    (lc.content_type = 'post' AND lc.content_id = p.post_id)
WHERE p.message_state = 'visible'
    AND p.thread_id = 9999
GROUP BY lc.content_user_id
ORDER BY like_count DESC
Change thread_id = 9999 to whichever thread ID you desire.
Fantastic - many thanks Chris - spot on! (y)

Cheers,
Shaun :D