sql - 带有 PostgreSQL 的 Rails 3 应用程序 - 获取按对话分组的消息列表

Question

我正在运行一个模拟电子邮件消息的 Rails 3 应用程序。该应用程序部署在 Heroku 上，因此后端数据库是 PostgreSQL。消息线程由 Posts 表中的 thread_id 字段简单建模。当用户发布新帖子时，我们将其命名为 p1，然后命名为 p1.thread_id=p1.id。如果用户用 p2 回复 p1 那么 p2.thread_id=p1.thread_id。

我需要编写一个查询来选择发送给特定用户的消息。结果列表必须只包含每个线程一条消息，并且该消息必须是线程中的最新消息。我还需要知道每个线程中有多少消息。

使用以下选择:

SELECT DISTINCT ON(thread_id) * FROM "posts"

不起作用，因为它不返回按最后排在前的帖子。

这个也不行:

SELECT DISTINCT ON(thread_id) * FROM "posts"ORDER BY thread_id, posts.created_at DESC

因为帖子按 thread_id 首先排序。

帖子表:

create_table "posts", :force => true do |t|
    t.string   "title"
    t.text     "content"
    t.string   "content_type"
    t.text     "options"
    t.string   "type"
    t.integer  "receiver_id"
    t.integer  "sender_id"
    t.integer  "circle_id"
    t.text     "data_bag"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "parent_id"
    t.integer  "thread_id"
    t.datetime "posted_at"
  end

感谢您的帮助。

Answer 1

如果您不介意自己动手​​使用一些 SQL，您可以使用 window function完成工作。您可以使用此 SQL 获取帖子 ID:

select id
from (
    select id,
           rank() over (partition by thread_id order by created_at desc)
    from posts
    where receiver_id = #{user.id}
) as dt
where rank = 1

如果您想要更多列，请将它们添加到两个 SELECT 子句中。 #{user.id} 当然是您感兴趣的收件人。

有趣的部分是窗口函数:

rank() over (partition by thread_id order by created_at desc)

这将根据 thread_id(某种本地化的 GROUP BY)将表划分为多个组，按时间戳对它们进行排序(最近的排在最前面)，然后是 rank() 每组中的第一个条目产生 1，第二个条目产生 2，等等。

给定一个看起来像这样的表格:

=> select * from posts;
 id | receiver_id | thread_id |     created_at      
----+-------------+-----------+---------------------
  1 |           1 |         2 | 2011-01-01 00:00:00
  2 |           1 |         2 | 2011-02-01 00:00:00
  3 |           1 |         2 | 2011-03-01 00:00:00
  4 |           1 |         3 | 2011-01-01 00:00:00
  5 |           1 |         4 | 2011-01-01 00:00:00
  6 |           1 |         3 | 2011-01-01 13:00:00
  7 |           2 |        11 | 2011-06-06 11:23:42
(7 rows)

内部查询给你这个:

=> select id, rank() over (partition by thread_id order by created_at desc)
   from posts
   where receiver_id = 1;

 id | rank 
----+------
  3 |    1
  2 |    2
  1 |    3
  6 |    1
  4 |    2
  5 |    1
(6 rows)

然后我们围绕它包裹外部查询以剥离排名最高的匹配项:

=> select id
    from (                                                                  
        select id,
               rank() over (partition by thread_id order by created_at desc)
        from posts
        where receiver_id = 1
    ) as dt
    where rank = 1;

 id 
----
  3
  6
  5
(3 rows)

因此，添加您想要的额外列并将其全部包装在 Post.find_by_sql 中大功告成。