MySQL query to get top 10 point-earners in last week?

Question

I'm trying to write a query that returns the user ID's of the top 10 users who gained the most points in the last 7 days on my web app.

I have three tables that, together, have the info I need.

1. votingapi_votes table. It has a record for every up/down vote on a comment or node.
2. node table. It can associate a node ID with a user ID so you can figure out who posted the story getting the votes.
3. comments table, which does the same for comments.

I believe I need to write a query that selects every vote on a comment or node from the last week from the votingapi_vote table.

Here's the structure of that table:

• vote_id
• content_type
• content_id
• value
• value_type
• tag
• uid
• timestamp

So I'd SELECT rows of content_type "node" or "comment" with a Unix timestamp greater than time() - 684000.

Then it needs to

1. group these votes by "content_id".

2. Look up the respective "user_id" values for each content_id in the "node" and "comments" tables so we know who made the nodes and comments.

3. Calculate how many points total each user_id gained from his nodes and comments.

4. Sort these user_id's in reverse order and limits it to displaying only the first 10.

Phew. That seems like what I need to do. Now what does that actual query look like?

Answer 1

Use:

SELECT x.*
  FROM (SELECT n.user_id,
               SUM(vav.value) 'total_votes'
          FROM NODE n
          JOIN VOTINGAPI_VOTES vav ON vav.content_id = n.nid
                                  AND vav.content_type = 'node'
         WHERE vav.timestamp > NOW() - 684000
      GROUP BY n.user_id
       UNION
       SELECT c.user_id,
              SUM(vav.value) 'total_votes'
         FROM COMMENTS c
         JOIN VOTINGAPI_VOTES vav ON vav.content_id = c.cid
                                 AND vav.content_type = 'comment' 
        WHERE vav.timestamp > NOW() - 684000
     GROUP BY c.user_id) x
ORDER BY x.total_votes DESC
   LIMIT 10

Answer 2

Posting based on OMG Ponies' answer

SELECT x.user_id, SUM(x.total_votes)
    FROM (
        SELECT n.user_id, SUM(vav.value) AS total_votes
            FROM NODE n
            JOIN VOTINGAPI_VOTES vav 
                ON vav.content_id = n.nid
                AND vav.content_type = 'node'
            WHERE vav.timestamp > NOW() - 684000
            GROUP BY n.user_id
        UNION
        SELECT c.user_id, SUM(vav.value) AS total_votes
            FROM COMMENTS c
            JOIN VOTINGAPI_VOTES vav 
                ON vav.content_id = c.cid
                AND vav.content_type = 'comment' 
            WHERE vav.timestamp > NOW() - 684000
            GROUP BY c.user_id
    ) x
    GROUP BY 
        x.user_id
    ORDER BY 
        x.total_votes DESC
    LIMIT 10

The problem with the earlier code is that it returns 2 rows per user, 1 for comment, 1 for node. This code will do another SUM to aggregate it to just 1 number per user.

Answer 3

Can you just use the Views module? I'm pretty sure you can...