I've got two models User and Post where a
:user has_many :posts
How do I find a list of users who have more than n posts?
I'm looking for a simple statement like where rather than something to do with using scopes.
Add a comment
|
3 Answers
The where query does not allow aggregate functions like count inside it, the ideal method to tackle this problem is using the having method.
User.joins(:posts).group('users.id').having('COUNT(*) >= ?', n)
This will be mapped to
SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" GROUP BY users.id HAVING COUNT(*) >= n
There is another method possible which is more easier, by using counter cache inside the Post Model and keeping the count as a posts_count column on the user table.
belongs_to :user, counter_cache:true
User.where('posts_count > ?', n)
But I prefer the first method as it does not involve any DB changes and is pretty straight forward
Comments
User.join(:posts).group('users.id').having('count(user_id) > n')
Another way of solving this is to use counter_cache and add a posts_count column on the user table.
belongs_to :user, counter_cache:true # Inside the Post model
Then you can do
User.where('posts_count > ?', n)
Comments
Let try this:
User.where('id IN (SELECT user_id
FROM posts
GROUP BY user_id
HAVING COUNT(*) > ?
)', n)
