PostgreSQL 加入表之间的最新记录

Question

我有两个与这个问题相关的表:conversations 有很多 messages。基本结构(只有相关的列)如下:

conversations (
  int id (PK)
)

create table conversation_participants (
  int id (PK),
  int conversation_id (FK conversations),
  int user_id (FK users),
  unique key on [conversation_id, profile_id]
)

create table messages (
  int id (PK),
  int conversation_id (FK conversations),
  int sender_id (FK users),
  int recipient_id (FK users),
  text body
)

对于每个对话条目，给定一个 user_id 我想接收:

• 用户参与的所有对话(即:conversations.*)
• 加入最近的匹配消息(即:order by messages.id desc limit 1)
• 对话按最近的消息 ID 排序(即:按 messages.id desc 排序)

不幸的是，我似乎可以找到的所有查询帮助都与 MySQL 有关，而这在 PostgreSQL 中不起作用。我找到的最接近的是 this answer on StackOverflow这给出了 select distinct on (...) 语法的示例。但是，除非我只是做错了，否则我似乎无法在给定该方法所需的分组约束的情况下以正确的方式对结果进行排序。

Answer 1

所有信息都在“消息”表中，您不需要其他表:

SELECT 
    id, 
    body,
    c.* -- content from conversations 
FROM messages 
    JOIN
        (SELECT MAX(id) AS id, conversation_id 
        FROM messages 
        WHERE 1 IN(sender_id, recipient_id) -- the number is the userid, should be dynamic
        GROUP BY conversation_id) sub
        USING(id, conversation_id)
    JOIN conversations c ON c.id = messages.conversation_id
ORDER BY
    id DESC;

编辑:只需加入“对话”即可从此表中获取所需的数据。