如何在SQL中某个用户的每次会话中获取最新消息？ [英] How to get the latest message in each conversation of a certain user in SQL?

问题描述

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

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

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

解决方案

...最新

假定ID为 1 的用户和 3 ，就像您在小提琴中所做的一样，我们对最新的 created_at 和<$ c的消息感兴趣$ c>（发件人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: http://sqlfiddle.com/#!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?

-> Updated SQLfiddle.

Also consider the improved and simplified test case in the fiddle.

这篇关于如何在SQL中某个用户的每次会话中获取最新消息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！