GROUP_BY 内的活动记录 LIMIT

发布于 2025-01-04 16:47:56 字数 442 浏览 3 评论 0原文

场景我有一个充满帖子的表和一个用户表。我希望能够获取所有帖子并按用户对它们进行分组，但我希望将限制设置为每个用户 10 个。

class Post < ActiveRecord::Base
    belongs_to :user
end

class User < ActiveRecord::Base
    has_many :posts
end

# I thought this might work but it just grabs the first 10 posts and groups them
Post.find(:all, :limit=>10).group_by(&:user)

有什么想法吗？我是否必须编写自定义 SQL 或者 Active Record 可以执行此操作吗？

原文

SCENARIO
I have a table full of posts with a users table.
I want to be able to fetch all the posts and group them by users but I want to set a limit of say 10 per user.

class Post < ActiveRecord::Base
    belongs_to :user
end

class User < ActiveRecord::Base
    has_many :posts
end

# I thought this might work but it just grabs the first 10 posts and groups them
Post.find(:all, :limit=>10).group_by(&:user)

Any thoughts? Do I have to write custom SQL for or can Active Record do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空城缀染半城烟沙 2025-01-11 16:47:56

类似的东西？

Post.group(:user_id).limit(10)

Something like?

Post.group(:user_id).limit(10)

回复收藏 0 原文

最近可好 2025-01-11 16:47:56

Post.group(:user_id).limit(10)

group_by不是一个查询方法，而是一个Enumerable的方法。

在您的代码中，Post.find(:all, :limit => 10) 在传递给 group_by 之前会转换为 Array。上面的方法将查询方法链接在一起，并且仅在需要使用它们时才将它们转换为Array。

ActiveRecord 处理整个事情。上面的方法转化为

SELECT `posts`.* FROM `posts` GROUP BY user_id LIMIT 10

Post.group(:user_id).limit(10)

group_by is not a query method, but rather a method of Enumerable.

In your code, Post.find(:all, :limit => 10) is turned into an Array before being passed to group_by. The method above chains query methods together and only converts them to an Array when you need to use them.

ActiveRecord handles the whole thing. The above method translates to

SELECT `posts`.* FROM `posts` GROUP BY user_id LIMIT 10

回复收藏 0 原文

情栀口红 2025-01-11 16:47:56

据我所知，获取每个用户最近 10 篇帖子的唯一方法需要嵌套子查询（这可能会产生性能问题）或 postgres 风格的横向连接。相当有信心仅使用活动记录无法完成此任务，并且需要编写自定义 SQL，而您已表示要避免这样做。

作为无需自定义 SQL 即可完成的替代方案，您可以使用以下内容列出某个时间窗口（例如过去的一个月或一年）内的每个用户及其帖子：

class User < ActiveRecord::Base
  has_many :recent_posts, -> { where(posts: {created_at 1.month.ago..Time.now}) }, class_name: 'Post'
end

User.includes(:recent_posts).each do |user|
  user.recent_posts
end

这不会为每个用户执行 SQL 查询，因此性能相对较高与纯粹使用 ruby 进行比较。

The only way I know to grab the recent 10 posts per user would require a nested sub-query (which can have performance issues) or postgres-style lateral join. Fairly confident this cannot be accomplished with only active-record and requires writing custom SQL, which you've indicated you want to avoid.

As an alternative that could be accomplished without custom SQL, you could list each user and their posts within a time window (e.g. past month or year) with the following:

class User < ActiveRecord::Base
  has_many :recent_posts, -> { where(posts: {created_at 1.month.ago..Time.now}) }, class_name: 'Post'
end

User.includes(:recent_posts).each do |user|
  user.recent_posts
end

Which would not execute a SQL query for each user and would therefore be relatively performant compared with doing it purely in ruby.

回复收藏 0 原文

~没有更多了~