postgresql 在两个用户之间的对话中拉出最后发送的消息

nle07wnf 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(2)|浏览(107)

假设如下：

create table my_schema.user(
  id serial primary key
);
create table my_schema.conversation(
  id serial primary key
);
create table my_schema.conversation_message(
  id serial primary key,
  conversation_id bigint references my_schema.conversation(id) not null,
  from_user_id bigint references my_schema.user(id) not null,
  to_user_id bigint references my_schema.user(id) not null,
  created_at timestamp not null
);

insert into my_schema.user values (1), (2), (3);
insert into my_schema.conversation values (50), (51);
insert into my_schema.conversation_message values
  (100, 50, 1, 3, current_timestamp(0)),
  (101, 51, 1, 2, current_timestamp(0)),
  (102, 51, 2, 1, current_timestamp(0)),
  (103, 51, 2, 1, current_timestamp(0)),
  (104, 51, 1, 2, current_timestamp(0));

我需要以下结果：

listing_conversation_id | latest_message
------------------------+------------+-----------------
                     50 | {"id":100,"created_at":"2023-04-13T19:23:06","to_user_id":3,"from_user_id":1,"conversation_id":50}
                     51 | {"id":104,"created_at":"2023-04-13T19:23:06","to_user_id":2,"from_user_id":1,"conversation_id":51}

我已经能够使用以下查询检索链接到每个conversation的所有conversation_message：

select
  c.id as listing_conversation_id,
  to_jsonb(array_agg(cm.*)) as messages
  -- to_json(
  --   select *
  --   from my_schema.conversation_message
  --   limit 1
  -- ) as latest_message
from my_schema.conversation c
join my_schema.conversation_message cm
  on cm.conversation_id = c.id
join my_schema.user u1
  on u1.id = cm.from_user_id
join my_schema.user u2
  on u2.id = cm.to_user_id
where u1.id = 1
or u2.id = 1
group by c.id;

但是latest_message显然不能是一个数组。
我如何才能做到这一点呢？每当我尝试嵌套查询时，我都会捕获语法错误，就像我在上面的查询注解部分所尝试的那样。另外，这种方法可以吗？或者我应该考虑不同的模型？
Fiddle

postgresql

来源：https://stackoverflow.com/questions/76008745/pulling-out-last-sent-message-in-a-conversation-between-two-users

2条答案

按热度按时间

u5rb5r591#

使用DISTINCT ON获取每个会话的 * 最后一条消息 *-顺序在order by子句中定义。
查询（不包括不相关的表）

with latest_message as ( 
select distinct on (conversation_id) * from  conversation_message order by conversation_id, created_at desc
)
select latest_message.conversation_id listing_conversation_id, to_jsonb(latest_message.*)
from latest_message;

listing_conversation_id|to_jsonb                                                                                                   |
-----------------------+-----------------------------------------------------------------------------------------------------------+
                     50|{"id": 100, "created_at": "2023-04-13T21:15:08", "to_user_id": 3, "from_user_id": 1, "conversation_id": 50}|
                     51|{"id": 101, "created_at": "2023-04-13T21:15:08", "to_user_id": 2, "from_user_id": 1, "conversation_id": 51}|

赞(0）回复(0）举报 2023-04-20

e5nqia272#

假设conversation_message.id被用作新近度的代理，下面给出了指定的结果（这是针对最初标题的帖子，它指定了“两个用户之间的最新消息”）：

SELECT
  t.*
FROM (SELECT DISTINCT ON (least(u1.id, u2.id), greatest(u1.id, u2.id))
             c.id AS listing_conversation_id,
             to_jsonb(cm.*) AS latest_message
        FROM
          my_schema.conversation c
          JOIN my_schema.conversation_message cm ON cm.conversation_id = c.id
          JOIN my_schema.user u1 ON u1.id = cm.from_user_id
          JOIN my_schema.user u2 ON u2.id = cm.to_user_id
       WHERE
         u1.id = 1 OR u2.id = 1
       ORDER BY
         least(u1.id, u2.id),
         greatest(u1.id, u2.id),
         cm.id DESC) t
ORDER BY
  t.listing_conversation_id;

(As Marmite Bomber指出，my_schema.user和conversation的连接是不必要的。它们是基于OP已经包含它们的假设，因为这些表中的其他列将被包含在最终查询中。）
DISTINCT ON (least(u1.id, u2.id), greatest(u1.id, u2.id))是必要的，因为问题的原始版本指定了用户对，而不是对话，并且在相同的两个用户之间可能有多个对话。外部查询按listing_conversation_id排序，以确保结果的顺序与OP的示例匹配。
一般来说，生成的ID列除了标识特定行之外不应具有任何意义。尽管在应用程序的生命周期中不太可能发生这种情况，但序列可能会循环，这样最近创建的行的ID可能小于较旧的行。如果需要合并来自其他源的数据，则使用ID值作为时间的代理也会产生问题。对于conversation_message，created_at将是确定新近度的更好选择。
与最后一点相关的是，可以使用clock_timestamp()而不是在设置脚本中使用current_timestamp，这样每个消息都有不同的created_at值。

赞(0）回复(0）举报 2023-04-20

我来回答

postgresql 在两个用户之间的对话中拉出最后发送的消息

2条答案

相关问题

热门标签

最新问答