CODEWITHSUNDEEP
mysqloptimizationexpressionengineleaderboardsubquery
Ryan Burney
Ryan Burney

Reputation: 567

Creating a leaderboard (ranked list of members) in MySQL with nested queries

I'm trying to create a leaderboard of members on my site by using the two nested queries below.

The first query grabs a list of members:

SELECT member_id, username, screen_name FROM exp_members WHERE group_id IN (1,5) LIMIT 100

The second query is nested inside the first query, and grabs a count of how many times a member's entries have been favorited:

SELECT COUNT(*) AS favorite_count
   FROM qb_channel_titles, qb_channel_data, qb_matrix_data
   WHERE qb_channel_titles.channel_id = '1'
   AND qb_channel_titles.entry_id = qb_channel_data.entry_id
   AND qb_channel_titles.entry_id = qb_matrix_data.entry_id
   AND field_id = '13'
   AND author_id = 'MEMBER_ID_FROM_FIRST_QUERY'
   ORDER BY favorite_count DESC"
}

So the code I have is like:

[first query]
   [second query]
      ..output one row of the leaderboard..
   [/second query]
[/first query]

Nesting the second query inside the first gives me the proper list of members and the number of votes they've each received, but the list is sorted on the first (outer) query rather than the second (inner) query.

Two questions:

  1. How do I sort the list of members by favorite_count in descending order?
  2. What is the most resource efficient way to do this? I suspect that nesting queries isn't the best way to go, but I honestly don't know any better.

Upvotes: 1

Views: 463

Answers (1)

Enrico
Enrico

Reputation: 667

Are you trying to do something like this?

SELECT
    member_id,
    username,
    screen_name,
    (SELECT COUNT(*) AS favorite_count
       FROM qb_channel_titles, qb_channel_data, qb_matrix_data
       WHERE qb_channel_titles.channel_id = '1'
       AND qb_channel_titles.entry_id = qb_channel_data.entry_id
       AND qb_channel_titles.entry_id = qb_matrix_data.entry_id
       AND field_id = '13'
       AND author_id = member_id
       ORDER BY favorite_count DESC") as "Votes"
FROM
    exp_members
WHERE
    group_id IN (1,5)
ORDER BY
    (SELECT COUNT(*) AS favorite_count
       FROM qb_channel_titles, qb_channel_data, qb_matrix_data
       WHERE qb_channel_titles.channel_id = '1'
       AND qb_channel_titles.entry_id = qb_channel_data.entry_id
       AND qb_channel_titles.entry_id = qb_matrix_data.entry_id
       AND field_id = '13'
       AND author_id = member_id
       ORDER BY favorite_count DESC")
LIMIT 100

You could also put this query in a view and query the view (sort of nesting another query performance-wise). I'm not expert on performance, but I'd say you could use a trigger and keep the favorite_count in another table, and every time an user favorites something the trigger will update. The other table could have just ID|COUNT. This will increase time when favoriting but reduce time to check the leaderboard, so the efficiency will depend on your user profile for favoriting or viewing the leaderboards...

Upvotes: 1

Related Questions