由父对象分组的意见，由最早的评论订购父对象 [英] grouping comments by parent object, ordering parent object by oldest comment

问题描述

我有有意见的对象。作为周期性电子邮件摘要的一部分，我想确定最早的评论对象的第一顺序一段注释，present的对象。

数据：

  OBJECT_ID COMMENT_ID
30 40
40 42
32 41
30 43
32 44
 

输出：

对象＃30

• 注释40


• 注释43

对象＃40

• 注释42

对象＃32

• 注释41


• 注释44

---

我用这code来获取数据到中间阵列 - 我试图让这一切一举使用.group_by（安培;：commentable_id），但在正确的顺序中的数据还没有出来。

 注释= account.comments.all（
  ：条件=＆GT; [comments.created_at＆GT;8.hours.ago]
  ：为了=＆GT; comments.created_at ASC）{.MAP | C | [c.commentable_id，c.id]}= GT; [30,40]，[40,42]，[32,41]，[30,43]，[32,44]
 

如果我能得到这些数据转换成下面的表格，我可以遍历数组建立电子邮件内容...

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

但我不知道如果我做这个难度比我需要...任何建议？

（我用Rails 2.3和Ruby稀土元素1.8.7）

解决方案

您可以使用一组阵列总去，你要寻找的数组形式。<​​/ P>

阵列聚集体是大量分贝依赖性。 MySQL的是GROUP_CONCAT。 Postgres的是ARRAY_AGG。 SQLite不有一个现成的，但我知道你可以自定义聚合函数，所以它不是不可能的。

实际上还没有试过运行此code，但这里的东西，应该指向你在正确的方向：

 结果= Object.all（
  ：选择=＆GT; objects.id，GROUP_CONCAT（COMMENT_ID）AS comment_array'，
  ：组=＆GT; comments.id
）{.MAP | C | [c.id，c.comment_array]}
 

我使用的命名，从第一个例子，所以你需要改变对象到无论你的表称为。希望这是有道理的。 Rails的可能没有解析阵列的内置支持，所以它可能会返回一个字符串comment_array，你可能需要解析它。

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 email 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.

这篇关于由父对象分组的意见，由最早的评论订购父对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！