Rails Arel selecting distinct columns

Question

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

Basically I have:

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

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)

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

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

My question

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.

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

Answer 1

Solution 1

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

Make sure you index the created_at and topic_id column in the comments table.

Solution 2

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.

E.g:

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

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