查询以获取用户收件箱的最新对话 [英] Query to get last conversations for user inbox

问题描述

我需要一个特定的SQL查询来为用户收件箱选择最后10个会话。
收件箱仅显示与每个用户的对话（线程）-它选择对话中的最后一条消息并将其显示在收件箱中。

I need a specific SQL query to select last 10 conversations for user inbox. Inbox shows only conversations(threads) with every user - it selects the last message from the conversation and shows it in inbox.

已编辑。

期望的结果：从 10个最新对话中的每一个中提取条最新消息 。 Facebook以相同的方式显示最新对话

Expecting result: to extract latest message from each of 10 latest conversations. Facebook shows latest conversations in the same way

还有一个问题。如何进行分页在下一页中显示来自先前最新对话的后10条最新消息？

And one more question. How to make a pagination to show next 10 latest messages from previous latest conversations in the next page?

私人消息数据库中的数据如下：

Private messages in the database looks like:

| id | user_id | recipient_id | text
| 1  | 2       | 3            | Hi John!
| 2  | 3       | 2            | Hi Tom! 
| 3  | 2       | 3            | How are you? 
| 4  | 3       | 2            | Thanks, good! You?

推荐答案

根据我的理解，您需要获取最新的基于用户的对话消息（最近10次最新对话）

As per my understanding, you need to get the latest message of the conversation on per-user basis (of the last 10 latest conversations)

更新：我修改了查询以获取 latest_conversation_message_id 每次用户对话

Update: I have modified the query to get the latest_conversation_message_id for every user conversation

下面的查询获取 user_id = 2的详细信息，您可以修改 users.id = 2 来获取其他任何用户

The below query gets the details for user_id = 2, you can modify, users.id = 2 to get it for any other user

SQLFiddle ，希望这可以解决您的目的

SQLFiddle, hope this solves your purpose

SELECT
    user_id, 
    users.name, 
    users2.name as sent_from_or_sent_to,
    subquery.text as latest_message_of_conversation
FROM
    users
    JOIN
    (
    SELECT
        text,
        row_number() OVER ( PARTITION BY user_id + recipient_id ORDER BY id DESC) AS row_num,
        user_id,
        recipient_id,
        id
    FROM
        private_messages
    GROUP BY
        id,
        recipient_id,
        user_id,
        text
    ) AS subquery ON ( ( subquery.user_id = users.id OR subquery.recipient_id = users.id)  AND row_num = 1 )
    JOIN users as users2 ON ( users2.id = CASE WHEN users.id = subquery.user_id THEN subquery.recipient_id ELSE subquery.user_id END )
WHERE
    users.id = 2
ORDER BY
    subquery.id DESC
LIMIT 10

信息：该查询获取的最新消息是与其他任何用户的每次对话，如果 user_id 2 ，都会向 user_id 3 发送一条消息，也显示为它表示对话的开始。与其他用户进行的每次对话的最新消息都会显示出来

Info: The query gets the latest message of every conversation with any other user, If user_id 2, sends a message to user_id 3, that too is displayed, as it indicates the start of a conversation. The latest message of every conversation with any other user is displayed

这篇关于查询以获取用户收件箱的最新对话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！