Блог

Я пытаюсь вернуться, чтобы написать запрос, который возвращает только первый результат, где someid (результат первого случая) =X — и игнорирует последующие результаты, где someid =X. Как бы мне изменить следующий запрос для достижения этого?

Вот что я сейчас использую:

 SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE GROUP_CONCAT(receivers.id SEPARATOR ', ') END someid,   
            CASE WHEN $userid != senderid THEN senders.username ELSE GROUP_CONCAT(receivers.username SEPARATOR ', ') END somename,
            messages.body, 
            messages.time
    FROM messages 
    LEFT JOIN messages_recipients AS recipients ON messages.id = recipients.messageid
    LEFT JOIN users AS senders ON messages.senderid = senders.id
    LEFT JOIN users AS receivers ON recipients.userid = receivers.id
    WHERE recipients.userid = $userid
    OR messages.senderid = $userid
    GROUP BY messages.id
    ORDER BY messages.time DESC
  

Основываясь на информации из ваших комментариев, я предпринял еще одну попытку в этом направлении.

Я очень практичен, поэтому, хотя я могу концептуализировать, что произойдет, я не знал наверняка, пока не начал возиться.

Итак, давайте начнем с данных. Основываясь на том, о чем мы говорили, я создал три таблицы:

Пользователи

 id  user_name
1   Walker    
2   John      
3   Kate      
  

Сообщения

 id  senderid    body    time
1   1   ignored 1                                           2010-04-01 00:00:00.000
2   1   ignored 2                                           2010-04-02 00:00:00.000
3   3   ignored 3                                           2010-04-03 00:00:00.000
4   1   msg A to john and kate                              2010-04-10 00:00:00.000
5   3   msg b from kate to walker and john                  2010-04-11 00:00:00.000
  

*messages_recipients*

 id  messageid   userid
1   1   2
2   1   3
3   2   2
4   3   1
5   4   2
6   4   3
7   5   1
8   5   2
  

Данные настроены таким образом, что вы (Walker) отправили и получили сообщения в течение апреля с Джоном и Кейт.

Вы можете просмотреть список этих сообщений, выполнив следующую инструкцию sql:

 SELECT 
    u2.user_name AS Sender, 
    u1.user_name AS Receiver, 
    m.body, 
    m.time
FROM 
    messages m
JOIN 
    messages_recipients mr ON m.id = mr.messageid
JOIN 
    users u1 ON mr.userid = u1.id
JOIN 
    users u2 ON m.senderid = u2.id
ORDER BY 
    time DESC 
  

Теперь, когда у нас есть тестовый сценарий, сложная часть: возвращает самое последнее сообщение связи между вами (Walker) и Джоном и Кейт. У меня получилось довольно длинное SQL-выражение, и, по общему признанию, я не лучший в их создании, но я думаю, что это все равно сработает:

 BEGIN
    DECLARE @UserId INT = 1

    --A.  Main Query
    SELECT
        CASE 
            WHEN mtemp.senderid = 1 --@UserId 
                THEN 
                    CONCAT('Message To:  ', receivers.user_name)
                ELSE 
                    CONCAT('Message From:  ' , senders.user_name)
                END AS MessageType, 
        mtemp.body, 
        mtemp.time 
    FROM 
        messages mtemp 
        INNER JOIN users senders ON 
            mtemp.senderid = senders.id 
        INNER JOIN 
            (
            --B.  Inner Query determining most recent message (based on time) 
            --    between @UserID and the person @UserID 
            --    Communicated with (either as sender or receiver)
            select userid,max(maxtime) as maxmaxtime from
                (
                    --C.1.  First part of Union Query Aggregating sent/received messages on passed @UserId
                    SELECT 
                        m2.body,
                        kk.* 
                    FROM 
                        `messages` m2 INNER JOIN
                            (
                                SELECT DISTINCT
                                    userid,
                                    MAX(m.time) AS MaxTime
                                FROM
                                    messages m INNER JOIN
                                        messages_recipients mr  ON m.id = mr.messageid AND
                                        m.senderid = 1 --@UserId
                                GROUP BY
                                    mr.userid
                            ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId

                    UNION
                    --C.2.  Second part of Union Query Aggregating sent/received messages on passed @UserId
                    SELECT 
                        m1.body,
                        jj.* 
                    FROM 
                        `messages` m1 INNER JOIN
                        ----C.2a.  Inner most query of users users who sent message to userid
                        (SELECT DISTINCT
                                senderid as userid,
                                MAX(m.time) AS MaxTime
                            FROM
                                messages m INNER JOIN
                                    messages_recipients mr  ON m.id = mr.messageid AND
                                    mr.userid = 1 --@UserId
                            GROUP BY
                                m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid 
                 )       MaximumUserTime
             group by 
                MaximumUserTime.userid 
             ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime 
                    INNER JOIN users receivers on AggregatedData.userid = receivers.id 
            ORDER BY `time` DESC
END
  

Для тестирования в phpMyAdmin вам также придется удалить комментарии и инструкции begin / end declare. Я просто хотел опубликовать это так, как если бы это выглядело в процедуре.

Запрос предполагает, что вы не будете одновременно отправлять и получать разные сообщения от одного и того же пользователя в одно и то же время; вероятность того, что это произойдет, кажется очень маленькой, поэтому я надеюсь, что это сработает.

Когда я запускаю этот запрос, я получаю следующие результаты:

 MessageType body    time
Message From:  Kate         msg b from kate to walker and john                  2010-04-11 00:00:00.000
Message To:  John       msg A to john and kate                              2010-04-10 00:00:00.000
  

Это самые последние сообщения о Walker среди всех тех пользователей, которые общались с Walker.

Надеюсь, это поможет.

В этом случае может сработать вложенный запрос (хотя его производительность может не сделать его отличным вариантом):

 SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE GROUP_CONCAT(receivers.id SEPARATOR ', ') END someid,   
            CASE WHEN $userid != senderid THEN senders.username ELSE GROUP_CONCAT(receivers.username SEPARATOR ', ') END somename,
            messages.body, 
            messages.time
    FROM messages 
    INNER JOIN
       (
        SELECT m.senderId, m.receiverId, MAX(m.time) AS MyMaxTime FROM messages GROUP BY m.senderId, m.receiverId
       ) myLittleQuery
            ON messages.senderid = myLittleQuery.senderid AND messages.senderid = myLittleQuery.receiverId AND messages.time = myLittleQuery.MyMaxTime
    LEFT JOIN messages_recipients AS recipients ON messages.id = recipients.messageid
    LEFT JOIN users AS senders ON messages.senderid = senders.id
    LEFT JOIN users AS receivers ON recipients.userid = receivers.id
    WHERE recipients.userid = $userid
    OR messages.senderid = $userid
    GROUP BY messages.id
    ORDER BY messages.time DESC
  

Прошло некоторое время с тех пор, как я был в области MySQL, поэтому мой синтаксис, скорее всего, отключен. Тем не менее, суть этого есть.

Кроме того, в зависимости от того, что вы пытаетесь извлечь, вы могли бы также отказаться от операторов DISTINCT и CASE .