Rails: three most recent comments with unique users

Question

what would I put in the named scope :by_unique_users so that I can do Comment.recent.by_unique_users.limit(3), and only get one comment per user?

class User
  has_many :comments
end

class Comment
  belongs_to :user
  named_scope :recent, :order => 'comments.created_at DESC'
  named_scope :limit, lambda { |limit| {:limit => limit}}
  named_scope :by_unique_users
end

on sqlite named_scope :by_unique_user, :group => "user_id" works,

but makes it freak out on postgres, which is deployed on production PGError: ERROR: column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

Named scope might not work, but you can force Rails to do a manual query (From Geek Skillz).

Item.find( :all, :order => 'comments.created_at DESC', :select => 'DISTINCT user' )[0, 3]

Answer 2

Normally the order clause looks like this:

named_scope :recent, :order => 'created_at DESC'

You may want to try it.

Answer 3

Postgres is different from MySQL and SQLite in how it treats GROUP BY. In essence, it's very strict. Let's say you have.

id name
1  foo
2  bar
2  baz

Then you're doing GROUP BY id. MySQL assumes that you just want to drop all but the first name. So it will produce.

id name
1  foo
2  bar

However, Postgres will not be guessing the grouping method in this case. It needs a specific instruction of how you want to group other columns. It provides what's called aggregate functions for that purpose, and in this case you're looking for a function that takes a first of a bunch. I couldn't find a function that does that, but perhaps min() or max() could serve as one. In that case you need to use :select => 'min(comments.id), min(comments.some_other_column)', and this you should do for every column except user_id. Then you can use :group => 'user_id' without problems.

Btw, min() and max() accept strings, not just numbers, so they should work for any column. If you want to really take the first of a bunch, then google for "postgres aggregate first" to find some implementations, or use postgres arrays. Although these would break compatibility with mysql and sqlite.

Update

On the other hand if fetching recent comments isn't too expensive, let ruby handle the unique users part.

unique_comments = []
Comment.recent.each do |comment|
  unless unique_comments.find{|c| c.user_id == comment.user_id}
    unique_comments << comment
  end
  break if unique_comments.size > 2
end

Now you have at most 3 comments from distinct users.