构建 SQL 查询以连接三个表以获取消息传递信息 [英] Building SQL query to join three tables to get messaging info

问题描述

我有以下三个用于消息传递系统的表:

I have the following three tables for a messaging system:

`messaging_messagethread`
- id
- subject
- initiator_id # who creates the thread
- recipient_id

`messaging_message`
- id
- thread_id
- content
- timestamp
- sender_id

`messaging_messagestatus` # a status will be created for each recipient of a message
- id
- message_id
- recipient_id
- status

给定一个用户，我需要构建一个查询来获得以下信息:

Given a user, I need to build a query to get the following:

• 显示线程 ID(不同)，
该线程中最新消息的
• content 和timestamp
• 删除包含最新消息的任何话题 status='deleted'.

• Show Thread ID (distinct),
• content and timestamp of the most recent message in that thread
• Remove any threads with the most recent message status='deleted'.

这是我目前所拥有的:

SELECT DISTINCT thread.id as thread_id, timestamp.timestamp 
    FROM messaging_messagethread thread 
INNER JOIN 
    (SELECT thread_id, MAX(timestamp) as timestamp
      FROM messaging_message GROUP BY thread_id) timestamp
      ON thread.id = timestamp.thread_id
WHERE initiator_id = 4 OR thread.recipient_id = 4 ORDER BY timestamp.timestamp DESC

这为我提供了按最近时间戳排序的不同线程 ID.(我的三点中的第一点).我将如何构建整个查询?

This gives me distinct thread id's ordered by most recent timestamp. (My first of three points). How would I build the entire query?

推荐答案

您可以使用相关子查询来获取特定线程的最新消息.试试这个:

You can use correlated subqueries to get the most recent message of a particular thread. Try this:

SELECT 
    a.id, 
    b.content, 
    b.timestamp
FROM
    messaging_messagethread a
INNER JOIN
    messaging_message b ON a.id = b.thread_id 
WHERE 
    b.timestamp = 
    (
        SELECT MAX(timestamp) 
        FROM messaging_message 
        WHERE thread_id = a.id
    )
    AND b.id NOT IN
    (
        SELECT message_id
        FROM messaging_messagestatus
        WHERE status = 'deleted'
    )
    AND 4 IN (a.initiator_id, a.recipient_id)
ORDER BY
    b.timestamp DESC

如果我理解正确，我相信这就是你想要的.

If I understand you correctly, I believe this is what you want.

这篇关于构建 SQL 查询以连接三个表以获取消息传递信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！