Geek Answers Handbook

Select Users by General Content

I know this is easy, but it drives me crazy ...

I have a user table, comment table, and picture table.

I want the list of the top 10 users to be based on materials (all their comments and their photos).

What is it.

Shame on me.

UPDATE: Based on Ed.

here is my setup:

  • user table (user_id, username)
  • image table (img_id, submittedby_id = users.user_id)
  • comment table (id, submittedby_id = users.user_id)

and final request:

select submittedby_id, sum(total) from (select submittedby_id, count(img_id) as total from images group by submittedby_id union select submittedby_id, count(id) as total from comments group by submittedby_id ) as x group by submittedby_id order by sum(total) desc limit 10;
+4
source share
3 answers

Maybe something like this:

 select username, sum(submissions) from (select username, count(picture_id) from pictures group by username union select username, count(comment_id) from comments group by username ) group by username order by sum(submissions) desc limit 10;

For a conceptual review:

  • Count user views in each table
  • Combine those, so each user will have 0 to 2 samples from the subquery.
  • Group again by summing up the two accounts, and then order so that the largest amount is on top.

Hope this helps.

+5
source

psuedocode, of course, but you want something like this:

 select u.userid , count(commentID) + count(photoID) as totalsubmissions from users u left outer join comments c on u.userid = c.userid left outer join pictures p on u.userid = p.userid group by u.userid order by 2 desc fetch first 10 rows only
+2
source

Fine-tuning Ed's answer:

 select submittedby_id, sum(submissions) from (select submittedby_id, count(img_id) as submissions from images group by submittedby_id union all select submittedby_id, count(id) as submissions from comments group by submittedby_id ) as x group by submittedby_id order by sum(submissions) desc limit 10

I believe that you want to make a union here, just a union can omit records that look the same (same identifier and number of instances).

0
source

More articles:


All Articles