PostgreSQL加入表之间的最新记录 [英] PostgreSQL join to most recent record between tables

问题描述

我有两个与此问题有关的表:conversations有很多messages.基本结构(仅包含相关列)如下:

I have two tables pertaining to this question: conversations has many messages. The basic structure (with just the relevant columns) is as follows:

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:

For each conversations entry, given a user_id I want to receive:

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

• all conversations that user participated in (i.e.: conversations.*)
• joined to the most recent matching message (i.e.: order by messages.id desc limit 1)
• conversations ordered by their most recent message id (i.e.: order by messages.id desc)

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

Unfortunately, all the query help I can seem to find on anything like this pertains to MySQL, and that doesn't work in PostgreSQL. The closest thing I found is this answer on StackOverflow that gives an example of the select distinct on (...) syntax. However, unless I'm just doing it wrong, I can't seem to get the results ordered in the correct way given the grouping constraints I need with that method.

推荐答案

所有信息都在表"messages"中，您不需要其他表:

All information is in the table "messages", you don't need the other tables:

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;

只需加入对话"即可从该表中获取所需的数据.

Just JOIN on "conversations" to get the data needed from this table.

这篇关于PostgreSQL加入表之间的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！