1. 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?

Discussion in 'General PHP and MySQL Discussions' started by CyclingTribe, Feb 14, 2016.

  1. CyclingTribe

    CyclingTribe Well-Known Member

    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
  2. Chris D

    Chris D XenForo Developer Staff Member

    Try this:
    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.
    ozzy47 likes this.
  3. CyclingTribe

    CyclingTribe Well-Known Member

    Fantastic - many thanks Chris - spot on! (y)

    Shaun :D

Share This Page