如何在SQL中某个用户的每次会话中获取最新消息？

编程入门行业动态更新时间:2024-10-13 18:23:23

本文介绍了如何在SQL中某个用户的每次会话中获取最新消息？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我需要编写一个查询，该查询返回两个用户之间的对话中的最新消息。我已经在此小提琴中包含了模式和我的（失败的）尝试： sqlfiddle /＃！15 / 322c3 / 11

我已经解决这个问题已有一段时间了，但是每次我运行任何难看的查询<

任何帮助将不胜感激。为小猫做。

解决方案

...最新

假定ID为 1 的用户和 3 ，就像您在小提琴中所做的一样，我们对最新的 created_at 和（发件人ID，接收者ID）是（1,3）或（3,1）。

您可以使用临时行类型来缩短语法：

SELECT * 从消息中（sender_id，receiver_id）IN（（（1,3），（3,1）） ORDER BY created_at DESC 限制1；

或显式（且速度稍快，也更易于使用索引）：

选择* 来自消息 WHERE（sender_id = 1 AND receiver_id = 3 OR sender_id = 3 AND receiver_id = 1）在DESC LIMIT 1中创建的ORDER BY;

用于用户的所有对话

根据注释中的请求添加解决方案。

SELECT DISTINCT ON（user_id）* FROM（选择'out'AS类型，id，receiver_id AS user_id，主体，created_at FROM消息 WHERE sender_id = 1 UNION ALL SELECT'in'AS类型，id，sender_id AS user_id，主体，created_at 从消息 WHERE receiver_id = 1 ）sub ORDER BY user_id，created_at DESC;

此处的方法是将外国发送者/接收者折叠为一列，以简化最后一行的提取。

DISTINCT ON 在此相关答案中的详细说明：选择每个GROUP BY组中的第一行？

->更新了SQLfiddle 。 / p>

也考虑小提琴中经过改进和简化的测试用例。

I need to write a query that returns the latest message in a conversation between two users. I've included the schema and my (failed) attempts in this fiddle: sqlfiddle/#!15/322c3/11

I've been working around the problem for some time now but every time I run any of my ugly queries a sweet little kitten dies.

Any help would be much appreciated. Do it for the kittens.

解决方案

... the latest message in a conversation between two users.

Assuming the users with ID 1 and 3, like you did in the fiddle, we are interested in the message with the latest created_at and (sender_id, receiver_id) being (1,3) or (3,1).

You can use ad-hoc row types to make the syntax short:

SELECT * FROM messages WHERE (sender_id, receiver_id) IN ((1,3), (3,1)) ORDER BY created_at DESC LIMIT 1;

Or explicitly (and slightly faster, also easier to use with indexes):

SELECT * FROM messages WHERE (sender_id = 1 AND receiver_id = 3 OR sender_id = 3 AND receiver_id = 1) ORDER BY created_at DESC LIMIT 1;

For all conversations of a user

Added solution as per request in comment.

SELECT DISTINCT ON (user_id) * FROM ( SELECT 'out' AS type, id, receiver_id AS user_id, body, created_at FROM messages WHERE sender_id = 1 UNION ALL SELECT 'in' AS type, id, sender_id AS user_id, body, created_at FROM messages WHERE receiver_id = 1 ) sub ORDER BY user_id, created_at DESC;

The approach here is to fold foreign sender / receiver into one column to simplify the extraction of the last row.

Detailed explanation for DISTINCT ON in this related answer: Select first row in each GROUP BY group?