ActiveRecord 在排序之前对组进行排序的方法

发布于 2024-10-24 08:47:43 字数 216 浏览 3 评论 0原文

我有这个活动记录查询：

@reviews = Review.limit(30).group('user_id').order('created_at desc')

我正在使用组方法来确保每个用户只显示一篇评论。问题是该评论不一定是该用户最后创建的评论。

我知道分组发生在订购之前，因此是否可以在调用订单之前以某种方式对组进行排序？

原文

I have this active record query:

@reviews = Review.limit(30).group('user_id').order('created_at desc')

I'm using the group method to ensure I only display one review per user.
The problem is that this review is not necessarily the last created review by that user.

I understand grouping happens before ordering so is it possible somehow to sort a group before calling order?

分享到QQ

分享到微博

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

发布评论

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

初雪 2024-10-31 08:47:43

ids = Review.group(:user_id).maximum(:created_at).keys
@reviews = Review.limit(30).where(:id => ids).order(:created_at)

ids = Review.group(:user_id).maximum(:created_at).keys
@reviews = Review.limit(30).where(:id => ids).order(:created_at)

回复收藏 0 原文

浮世清欢 2024-10-31 08:47:43

我找到了这个问题的很多答案，但每个答案都需要原始sql，当你有简单的查询时，你可以使用它，对于你的输入，它将是这样的：

Review.find_by_sql(
 "SELECT * FROM (
 SELECT * FROM reviews
 ORDER BY reviews.created_at DESC ) as my_table
 group by my_table.user_id
 LIMIT 30"
)

但如果查询更复杂并且需要更多联接，那么你可以使用几乎前面提到的好解决方案，我不确定它是否是最好的解决方案，但它对我有用：

ids = Review.order('created_at DESC').pluck(:id)
@reviews = Review.where(id: ids).group(:user_id).limit(30)

编辑：是的，最后一个解决方案不正确，因为它对数据库执行两个查询。我今天意外地回到了这个问题，我使用了 to_sql 方法（随 > Rails 3.0 一起提供）以及第一个解决方案，对我来说它是完美的 - 1.仍然是一个查询，2.子查询可以像你希望的那样复杂，并且代码的可读性将保持不变：

Review.find_by_sql(
     "SELECT * FROM (
     #{ Review.order(:created_at).to_sql }
     ) as my_table
     group by my_table.user_id
     LIMIT 30"
    )

I have found many answers to this question but each requires raw sql, when you have simple query you can use it, for your input it will be something like this:

Review.find_by_sql(
 "SELECT * FROM (
 SELECT * FROM reviews
 ORDER BY reviews.created_at DESC ) as my_table
 group by my_table.user_id
 LIMIT 30"
)

but if query is more complicated and need more joins then you can use the almost good solution mentioned earlier, I am not sure if it is the best solution but it works for me:

ids = Review.order('created_at DESC').pluck(:id)
@reviews = Review.where(id: ids).group(:user_id).limit(30)

edit: Yes, the last solution isn't right because it executes two queries to DB. I have accidently came back to this problem today and I have used the to_sql method (shipped with > Rails 3.0) along with first solution, for me its perfect - 1. still one query, 2. subquery can be as complicated as you wish and the code readability will remain same:

Review.find_by_sql(
     "SELECT * FROM (
     #{ Review.order(:created_at).to_sql }
     ) as my_table
     group by my_table.user_id
     LIMIT 30"
    )

回复收藏 0 原文

~没有更多了~