给定has_many 的用户，找到具有最多的用户的正确方法是什么？

I am writing an app where each User has many posts. I would like to generate a top 3 list of the users with the most posts. The only way I can think of is to get all the posts and count how many each user has. This seems extremely expensive, and not scalable. Accuracy is important. Does anyone have any suggestions?

我正在编写一个应用程序,每个用户都有很多帖子。我想生成帖子最多的前3名用户列表。我能想到的唯一方法是获取所有帖子并计算每个用户拥有的数量。这似乎非常昂贵,而且不具备可扩展性。准确性很重要。有没有人有什么建议?

2 个解决方案

#1

Post.group(:user_id).order("count_all DESC").limit(3).count

should do the job. It groups the posts by the user, orders them after the number of posts and returns just the 3 with the most posts. the sql query may be more easy to understand:

应该做的工作。它按用户对帖子进行分组,在帖子数量之后对其进行排序,并返回最多帖子的3个帖子。 sql查询可能更容易理解:

(0.3ms) SELECT COUNT(*) AS count_all, user_id AS user_id FROM "posts" GROUP BY user_id ORDER BY count_all DESC LIMIT 3

(0.3ms)SELECT COUNT(*)AS count_all,user_id AS user_id FROM“posts”GROUP BY user_id ORDER BY count_all DESC LIMIT 3

and rails returns for you a hash like this:

和rails为你返回一个像这样的哈希:

{user_id => number_of_posts, user_id => number_of_posts, user_id => number_of_posts}

{user_id => number_of_posts,user_id => number_of_posts,user_id => number_of_posts}

#2

You're probably looking for a counter_cache here: check out the ActiveRecord association documentation. Essentially, if you have a posts_count integer column with a default of 0 on the users table and set :counter_cache to true in the association, Rails will update the column automatically. Then you can just do something like User.order('posts_count DESC').limit(3) to get the users with the most posts.

您可能在这里寻找counter_cache:查看ActiveRecord关联文档。实质上,如果在users表上有一个默认值为0的posts_count整数列,并且在关联中将:counter_cache设置为true,则Rails将自动更新该列。然后你可以做一些像User.order('posts_count DESC')。limit(3)这样的东西来获得帖子最多的用户。

#1