Help with recursive SELECT

Question

Here is the situation. I have two tables:

• users (registered users of the website),
• (personal messages they sent to each other)

The message table contains these columns (only important):

• id
• sender (identifier of the user who sent the message),
• identifier of the recipient to whom the message was sent),
• reply_to (the identifier of the message for which this message is a response may be NULL)

I need to make a SELECT query that will select a full conversation between two users. That is, if user A replies to a message sent from user B, and user B replies to a message, I would like to receive three lines, such as:

• message03: reply to message02
• message02: reply to message01
• 01 A B

, SELECT reply_to, , .

SELECT MySQL.

Answer 1

: ANSI SQL . (, Oracle CONNECT BY) , , SQL.

? , .

message_id. , ( ) . , .

.

Answer 2

conversation_id messages. conversation_id, . :

select * from messages where conversation_id = ? order by id asc

Answer 3

.

MySQL , : ​​:

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    messages
                WHERE   reply_to = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, reply_to
                INTO    _id, _parent
                FROM    messages
                WHERE   id = _parent;
        END LOOP;
END

:

SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, parent, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, messages
        WHERE   @id IS NOT NULL
        ) ho
JOIN    messages hi
ON      hi.id = ho.id

. , :

• MySQL

( @start_with).

sender_id receiver_id, , .

Answer 4

, Joe Celko SQL Smarties - 29 . (, , ), . . , , .

Answer 5

- :

SELECT *
FROM [Messages] 
WHERE 
    (
        [Sender] = @Sender 
            AND [Reciever] = @Reciever
    ) OR (
        [Sender] = @Reciever 
            AND [Reciever] = @Sender)

. reply_to, , :

A) .

B) , , , .

- ConversationId , , , .

, , , :

SELECT * 
FROM [Messages] 
WHERE 
    (
        (
            [Sender] = @Sender 
                AND [Reciever] = @Reciever
        ) OR (
            [Sender] = @Reciever 
                AND [Reciever] = @Sender)
    )
    AND id >= @FirstMessageId
    AND id < 
        (
            SELECT TOP 1 [id] 
            FROM [Messages] 
            WHERE [id] > @FirstMessageId
                AND [reply_to] IS NULL
                AND  
                    (       
                        (
                            [Sender] = @Sender 
                                AND [Reciever] = @Reciever
                        ) OR (
                            [Sender] = @Reciever 
                                AND [Reciever] = @Sender
                        )
                    )
    )