Select Users with more Items

Question

Each user HAS MANY photos and HAS MANY comments. I would like to order users by

SUM(number_of_photos, number_of_comments)

Can you suggest me the SQL query?

Answer 1

In Ruby On Rails:

User.find(:all, :order => '((SELECT COUNT(*) FROM photos WHERE user_id=users.id) + (SELECT COUNT(*) FROM classifications WHERE user_id=users.id)) DESC')

Answer 2

If I were to assume that you had a count of comments and a count of photos (user.number_of_photos, user.number_of_comments; as seen above), it would be simple (not stupid):

Select user_id from user order by number_of_photos DESC, number_of_comments DESC

Answer 3

Select * From Users U
Order By (Select Count(*) From Photos
          Where userId = U.UserId) +
          (Select Count(*) From Comments
          Where userId = U.UserId)

EDIT: although every query using subqueries can also be done using Joins, which will be faster ,

1. is not a simple question,
2. and is irrelevant unless the system is experiencing performance problems.

1) Both constructions must be translated by the query optimizer into a query plan which includes some type of correlated join, be it a nested loop join, hash-join, merge join, or whatever. And it's entirely possible, (even likely), that they will both result in the same query plan.

NOTE: This is because the entire SQL Statement is translated into a single query plan. The subqueries do NOT get their own, individual query plans as though they were being executed in isolation.

What query plan and what type of joins are used will depend on the data structure and the data in each specific situation. The only way to tell which is faster is to try both, in controlled environments, and measure the performance... but,

2) Unless the system is experiencing an issue with performance, (unacceptable poor performance). clarity is more important. And for problems like the one described above, (where none of the data attributes in the "other" tables are required in the output of the SQL Statement, a Subquery is much clearer in describing the function and purpose of the SQL that a join with Group Bys would be.

Answer 4

I think that the accepted solutions would be problematic from a performance standpoint, assuming you have many users, photos, and comments. Your query runs two separate select statements for every row in the user table.

What you want to do is synthesize a query using ActiveRecord that looks like this:

SELECT user.*, COUNT(c.id) + COUNT(p.id) AS total_count
FROM users u LEFT JOIN photos p ON u.id = p.user_id
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY user.id 
ORDER BY total_count DESC

The join will be much, much more efficient. Using left joins insures that even if a user has no comments or photos they will still be included in the results.

Answer 5

GROUP BY with JOINs works more efficiently than dependent subqueries (in all relational DBs I know):

Select * From Users
Left Join Photos On (Photos.user_id = Users.id)
Left Join Comments On (Comments.user_id = Users.id)
Group By UserId
Order By (Count(Photos.id) + Count(Comments.id))

with some assumptions on the tables (e.g. an id primary key in each of them).