sql - 检索用户的对话以及最后发送和接收的时间戳

Question

我需要检索特定用户已交换的所有用户的列表消息，连同发送的最后一条消息的时间戳和收到的最后一条消息的时间戳。我想在单个查询中执行此操作。

结果会是这样的:

[
    [0] {
                :uname => "fred",
        :last_received => 2013-04-09 22:47:20 UTC,
            :last_sent => 2013-04-09 22:47:28 UTC
    },
    [1] {
                :uname => "barney",
        :last_received => nil,
            :last_sent => 2013-06-16 16:25:56 UTC
    },
    [2] {
                :uname => "dino",
        :last_received => 2013-06-09 17:52:54 UTC,
            :last_sent => 2013-06-10 15:56:52 UTC
    }
]

简化的模式是:

CREATE TABLE users (
  id serial NOT NULL,
  uname text NOT NULL,
  created_at timestamp without time zone DEFAULT timezone('utc'::text, now())
)

CREATE TABLE messages (
  id serial NOT NULL,
  sender_id integer NOT NULL,
  recipient_id integer NOT NULL,
  message_text_id integer,
  created_at timestamp without time zone DEFAULT timezone('utc'::text, now())
)

我有一个执行此操作的查询，但由于它确实对用户进行了左联接，所以我担心随着用户数量的增加它会变慢——我不知道postgresql会不会优化一下避免这种情况的发生。

WITH t AS (
    select sender_id, recipient_id, max(created_at) as latest_date
    from messages
    where sender_id = #{id} or recipient_id = #{id}
    group by sender_id, recipient_id
)
select uname, t1.latest_date last_received, t2.latest_date last_sent
from users
left join t t1 on t1.sender_id    = users.id and t1.sender_id != #{id}
left join t t2 on t2.recipient_id = users.id and t2.recipient_id != #{id}
where t1.latest_date is not null or t2.latest_date is not null

我有兴趣了解 postgresql 是否会对此进行优化，并了解执行相同查询的更好方法。

谢谢。马克

Answer 1

您可以尝试以下几行 - 尝试使用 explain 看看哪个看起来最好。

SELECT u.uname, max(x.last_received) last_received, max(x.last_sent) last_sent
FROM (
    SELECT sender_id user_id, max(created_at) last_received, null last_sent
    FROM messages
    WHERE recipient_id = #{id}
    GROUP BY recipient_id 

    UNION ALL

    SELECT recipient_id user_id, null last_received,  max(created_at) last_sent
    FROM messages
    WHERE sender_id = #{id}
    GROUP BY sender_id
) x 
JOIN users u 
ON x.user_id = u.user_id