grouping comments by parent object, ordering parent object by oldest comment

I have objects that have comments. As part of a periodic email summary, I want to determine comments for a period, and present the objects in the order of oldest commented object first.

Data:

object_id  comment_id
30         40
40         42
32         41
30         43
32         44

Output:

Object #30

• comment 40
• comment 43

Object #40

• comment 42

Object #32

• comment 41
• comment 44

---

I am using this code to get the data to an intermediate array - I tried to get it all in one swoop using .group_by(&:commentable_id) but the data didn't come out in correct order.

comments = account.comments.all( 
  :conditions => ["comments.created_at > ?", 8.hours.ago],
  :order => "comments.created_at asc" ).map { |c| [c.commentable_id,c.id] }

=>  [ [30,40], [40,42], [32,41], [30,43], [32,44] ]

If I can get that data to transform into the following form, I could just iterate over the array to build the e开发者_如何学运维mail content...

[ [30,[40,43]], [40,[42]], [32,[41,44]] ]

But I wonder if I'm making this harder than I need to... Any advice?

(I'm using Rails 2.3 and Ruby ree-1.8.7)

---

You can use a group with an array aggregate to get to the array form that you're looking for.

Array aggregates are massively db dependent. MySQL's is GROUP_CONCAT. Postgres' is ARRAY_AGG. Sqlite doesn't have one out of the box, but I know you can define custom aggregate functions, so it's not impossible.

Haven't actually tried running this code, but here's something that should point you in the right direction:

result = Object.all(
  :select => 'objects.id, GROUP_CONCAT(comment_id) AS comment_array',
  :group => 'comments.id'
).map { |c| [c.id, c.comment_array] }

I used the naming from the first example, so you'll need to change 'object' to whatever your table is called. Hope it makes sense. Rails probably doesn't have inbuilt support for parsing an array, so it will probably return a string for comment_array, and you might have to parse it.

---

Having all the comments for a single object in a single block/element will definitely make life easier while doing any operation on them. However, I won't go as far as turning them into an array of array of arrays because it is already an array of arrays. I would prefer creating a hash like so:

comments_array = [ [30,40], [32,41], [40,42], [30,43], [32,44] ]
obj_with_comments = {}
comments_array.each do |x|
  obj_with_comments[x.first] ||= []
  obj_with_comments[x.first] << x.last
end

obj_with_comments #=> {40=>[42], 30=>[40, 43], 32=>[41, 44]}

But this presents another problem which is, hashes are not ordered, so you loose your ordering in some random fashion if you just iterate over the hash. However, you can create an array of objects then iterate over the hash like so:

objects = comments_array.collect{|x| x.first}.uniq
objects #=> [30, 32, 40]

# now get the hash value for each object key in order
objects.each { |obj| puts obj_with_comments[obj].inspect }

Hope that makes sense.

---

Try this:

comments = account.comments.all(
  :conditions => ["comments.created_at > ?", 8.hours.ago],
  :order => "comments.commentable_id ASC, comments.id ASC" 
).map { |c| [c.commentable_id,c.id] }

This will return the following result set:

=>  [ [30,40], [30,43], [32,41], [32,44], [40,42] ]

In the query above I am using id for sorting instead of create_at. If you are using MySQL and if the id's are auto generated this logic will work as the id of a new object will be higher than the id of an older object. If you don't allow editing of comments then this logic will work.

If you want to explicitly sort by the dates then use the following syntax:

comments = account.comments.all(
  :joins => "accounts AS accounts ON comments.commentable_type = 'Account' AND
                                     comments.commentable_id  = accounts.id", 
  :conditions => ["comments.created_at > ?", 8.hours.ago],
  :order => "accounts.id ASC, comments.id ASC" 
).map { |c| [c.commentable_id,c.id] }