PostgreSQL上的Rails 3应用程序-获取按converstation分组的消息列表 [英] Rails 3 app with PostgreSQL - Getting the list of messages grouped by converstation
问题描述
我正在运行模拟电子邮件消息的Rails 3应用程序。该应用程序已部署在Heroku上，因此后端数据库是PostgreSQL。消息线程通过帖子表中的thread_id字段进行简单建模。当用户发布新帖子时，我们称其为p1，然后称为p1.thread_id = p1.id。如果用户使用p2答复p1，则p2.thread_id = p1.thread_id。
I am running a rails 3 app that models email messages. The app is deployed on on Heroku so the backend db is PostgreSQL. Messages threads are simple modeled by the thread_id field in the Posts table. When the user post a new Post, let's call it p1 then p1.thread_id=p1.id. If the user reply to p1 with p2 then p2.thread_id=p1.thread_id.
我需要编写查询以选择发送给特定用户的消息。结果列表每个线程只能包含一条消息，并且该消息必须是线程中的最新消息。我还需要知道每个线程中有多少条消息。
I need to write a query to select the messages sent to a certain user. The resulting list must contain only one message per thread and that message must be the latest message in the thread. I also need to know how many messages are in each thread.
使用以下选择：
SELECT DISTINCT ON(thread_id) * FROM "posts"
不起作用
这个也不起作用：
SELECT DISTINCT ON（thread_id）*从帖子开始，按thread_id排序，posts.created_at DESC
SELECT DISTINCT ON(thread_id) * FROM "posts" ORDER BY thread_id, posts.created_at DESC
因为帖子先由thread_id排序。
as the posts are ordered first by thread_id.
Posts表：
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
感谢您的帮助。
推荐答案
如果您不介意使用一些SQL来弄脏自己的手，可以使用窗口功能来完成工作。您可以使用以下SQL获取帖子ID：
If you don't mind getting your hands dirty with a bit of SQL you can use a window function to get the job done. You can get the post IDs with this SQL:
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} 当然是您感兴趣的收件人。
If you want more columns add them to both SELECT clauses. The #{user.id} is, of course, the recipient that you're interested in.
有趣的部分是窗口函数：
The interesting part is the window function:
rank() over (partition by thread_id order by created_at desc)
这将根据 thread_id 将表划分为几组（本地化的GROUP BY），按时间戳顺序排序（最先），然后 rank（）在每个组中的第一个条目产生1，在第二个组中产生2，
This will partition the table into groups based on thread_id (sort of a localized GROUP BY), order them by the timestamp (most-recent first), and then rank() yields 1 for the first entry in each group, 2 for the second, etc.
给出一个看起来像这样的表：
Given a table that looks like this:
=> 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)
然后我们将外部查询包裹起来以剥离只是排名最高的匹配项：
And then we wrap the outer query around that to peel off just the top ranking matches:
=> 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 即可完成。
So add the extra columns you want and wrap it all up in a Post.find_by_sql and you're done.
这篇关于PostgreSQL上的Rails 3应用程序-获取按converstation分组的消息列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
