消息服务系统SQL查询需求：开发发送/接收消息列表查询语句

Hey there! Let's work through those two SQL queries you need for your custom messaging service. I've gone over your schema and requirements, so here's how to build each query:

1. Query for Messages Sent by the Current User #

This query will pull all messages sent by the user associated with the provided logintoken, including the message content, creation timestamp, and the receiver's username (if the message has been picked up by someone).

SELECT 
    m.content,
    m.creation_timestamp AS timestamp,
    u_receiver.username AS receiver_username
FROM 
    session s
JOIN 
    user u_sender ON s.user_id = u_sender.id
JOIN 
    route r_sender ON u_sender.id = r_sender.id_user
JOIN 
    message m ON r_sender.id_message = m.id
LEFT JOIN 
    route r_receiver ON m.id = r_receiver.id_message AND r_receiver.id_user != u_sender.id
LEFT JOIN 
    user u_receiver ON r_receiver.id_user = u_receiver.id
WHERE 
    s.token = 'your_logintoken_parameter';

How this works: #

• We start by linking the session table to the user table using the user_id to get the current sender's ID.
• Then we join the route table to find all messages created by this user (since a route record is generated when the user creates a message).
• We pull in the message details from the message table.
• Using a LEFT JOIN on the route table again, we look for any records where the same message was assigned to a different user (the receiver), then link to the user table to get their username. If the message hasn't been picked up yet, receiver_username will be NULL.

2. Query for Messages Received by the Current User #

This query retrieves all messages the current user has received, including the sender's username, message content, message creation date, and the date/time the user received the message.

SELECT 
    u_sender.username AS sender_username,
    m.content AS message,
    m.creation_timestamp AS creation_date,
    r_receiver.timestamp AS receiving_date
FROM 
    session s
JOIN 
    user u_receiver ON s.user_id = u_receiver.id
JOIN 
    route r_receiver ON u_receiver.id = r_receiver.id_user
JOIN 
    message m ON r_receiver.id_message = m.id
JOIN 
    route r_sender ON m.id = r_sender.id_message AND r_sender.id_user != u_receiver.id
JOIN 
    user u_sender ON r_sender.id_user = u_sender.id
WHERE 
    s.token = 'your_logintoken_parameter';

How this works: #

• Again, we start with the session table to get the current user's ID, linking to the user table as the receiver.
• We join the route table to find all messages assigned to this receiver.
• We pull in the message content and creation timestamp from the message table.
• We then join back to the route table to find the record associated with the message's sender (the other user linked to the same message), and get their username from the user table.
• The timestamp from the receiver's route record gives us the date/time the message was received.

Quick tip: Replace 'your_logintoken_parameter' with the actual parameter your application uses (this might be a placeholder like ? or :token depending on your framework, instead of a hardcoded string).

内容的提问来源于stack exchange，提问作者christian2812