Rails的阿雷尔选择不同的列 [英] Rails Arel selecting distinct columns

问题描述

我已经打了一个轻微的块与新的范围方法（阿雷尔0.4.0，Rails的3.0.0.rc）

I've hit a slight block with the new scope methods (Arel 0.4.0, Rails 3.0.0.rc)

基本上我有：

A 主题模式，的has_many：评论和评论模型（以 topic_id 列）其中 belongs_to的：主题

A topics model, which has_many :comments, and a comments model (with a topic_id column) which belongs_to :topics.

我想要获取的热门话题的集合，即这是最近评论的主题。当前code是如下：

I'm trying to fetch a collection of "Hot Topics", i.e. the topics that were most recently commented on. Current code is as follows:

# models/comment.rb
scope :recent, order("comments.created_at DESC")

# models/topic.rb
scope :hot, joins(:comments) & Comment.recent & limit(5)

如果我执行 Topic.hot.to_sql ，下面的查询被触发：

If I execute Topic.hot.to_sql, the following query is fired:

SELECT "topics".* FROM "topics" INNER JOIN "comments"
ON "comments"."topic_id" = "topics"."id"
ORDER BY comments.created_at DESC LIMIT 5

这工作得很好，但它可能返回重复的话题 - 如果话题＃3是最近谈到了好几次，它会被退回几次

This works fine, but it potentially returns duplicate topics - If topic #3 was recently commented on several times, it would be returned several times.

我的提问

我将如何返回一组主题鲜明，铭记我仍然需要访问 comments.created_at 字段，以显示多久以前的最后一个职位是？我会想象沿东西线不同的或 GROUP_BY ，但我不太确定如何最好地去吧。

How would I go about returning a distinct set of topics, bearing in mind that I still need to access the comments.created_at field, to display how long ago the last post was? I would imagine something along the lines of distinct or group_by, but I'm not too sure how best to go about it.

任何意见/建议多AP preciated - 我已经走到了优雅的解决方案很快的希望增加了100代表赏金

Any advice / suggestions are much appreciated - I've added a 100 rep bounty in hopes of coming to an elegant solution soon.

推荐答案

解决方法1

这不使用阿雷尔，但Rails的2.X的语法：

This doesn't use Arel, but Rails 2.x syntax:

Topic.all(:select => "topics.*, C.id AS last_comment_id, 
                       C.created_at AS last_comment_at",
          :joins => "JOINS (
             SELECT DISTINCT A.id, A.topic_id, B.created_at
             FROM   messages A,
             (
               SELECT   topic_id, max(created_at) AS created_at
               FROM     comments
               GROUP BY topic_id
               ORDER BY created_at
               LIMIT 5
             ) B
             WHERE  A.user_id    = B.user_id AND 
                    A.created_at = B.created_at
           ) AS C ON topics.id = C.topic_id
          "
).each do |topic|
  p "topic id: #{topic.id}"
  p "last comment id: #{topic.last_comment_id}"
  p "last comment at: #{topic.last_comment_at}"
end

请确保你指数 created_at 和 topic_id 在的评论专栏表。

解决方案2

添加 last_comment_id 列在主题模式。创建注释后更新 last_comment_id 。这种方法比使用复杂的SQL来确定最后一个注释快得多。

Add a last_comment_id column in your Topic model. Update the last_comment_id after creating a comment. This approach is much faster than using complex SQL to determine the last comment.

例如：

class Topic < ActiveRecord::Base
  has_many :comments
  belongs_to :last_comment, :class_name => "Comment"
  scope :hot, joins(:last_comment).order("comments.created_at DESC").limit(5)
end

class  Comment
  belongs_to :topic

  after_create :update_topic

  def update_topic
    topic.last_comment = self
    topic.save
    # OR better still
    # topic.update_attribute(:last_comment_id, id)
  end
end

这是比运行复杂的SQL查询来确定热门话题高效得多。

This is much efficient than running a complex SQL query to determine the hot topics.

这篇关于Rails的阿雷尔选择不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！