Get last message from each conversation

Question

I know similar questions had been asked before, but none of them had this same conditions and their answers didn't work for this case.

The table containing the messages looks like this:

id | owner_id | recipient_id | content      | created
 1 |        1 |            2 | Hello        | 2015-12-08 20:00
 2 |        2 |            1 | Hey          | 2015-12-08 20:10
 3 |        3 |            1 | You there?   | 2015-12-08 21:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00

And let's say I query for the last message from each one of the conversations for User ID 1, the expected result is:

id | owner_id | recipient_id | content      | created
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 2 |        2 |            1 | Hey          | 2015-12-08 20:10

I tried many combinations, using JOINs and sub-queries but none of them gave the expected results.

Here is one of the queries I tried but it's not working. I believe is not even near to what I'm needing.

SELECT
    IF ( owner_id = 1, recipient_id, owner_id ) AS Recipient,

    (
        SELECT
            content
        FROM
            messages

        WHERE
            ( owner_id = 1 AND recipient_id = Recipient  )
        OR
            ( owner_id = Recipient AND recipient_id = 1 )

        ORDER BY
            created DESC

        LIMIT 1
    )
FROM
    messages

WHERE
    owner_id = 1
OR
    recipient_id = 1

GROUP BY
    Recipient;

Could you kindly include the SQL queries that you've tried in your question? — summea, Dec 17 '15 at 19:55
Sorry, too tired to give a proper answer, but you basically have to group by "other user" of the conversation, it's achievable with `case` or `if` statements inside `GROUP_BY`. Select max(id) in that group by, then select where id in (group by query). — Timekiller, Dec 17 '15 at 19:57

score 7 · Accepted Answer · answered Dec 17 '15 at 20:12

7

select t.* 
    from 
        t 
      join 
        (select user, max(created) m  
            from 
               (
                 (select id, recipient_id user, created 
                   from t 
                   where owner_id=1 ) 
               union 
                 (select id, owner_id user, created
                   from t 
                   where recipient_id=1)
                ) t1
           group by user) t2
     on ((owner_id=1 and recipient_id=user) or 
         (owner_id=user and recipient_id=1)) and 
         (created = m)
   order by created desc

example on sqlfiddle

answered Dec 17 '15 at 20:12

splash58

26,043
3
22
34

This query returns the expected results, thank you so much! Although I'm still trying to figure out how it works. – Camilo Dec 18 '15 at 00:19
1

You are welcome ! i can write some explnations if you want – splash58 Dec 18 '15 at 06:13
That would be great @splash58 – Camilo Dec 19 '15 at 06:52

score 0 · Answer 2 · edited May 23 '17 at 10:29

This should do the trick:

$joins = array(
    array('table' => 'conversations',
        'alias' => 'Conversation2',
        'type' => 'LEFT',
        'conditions' => array(
            'Conversation.id < Conversation2.id',
            'Conversation.owner_id = Conversation2.owner_id',
        )
    ),
    array('table' => 'conversations',
        'alias' => 'Conversation3',
        'type' => 'LEFT',
        'conditions' => array(
            'Conversation.id < Conversation3.id',
            'Conversation.recepient_id = Conversation3.recepient_id',
        )
    )

);

$conditions = array(
    'OR' => array(
        array(
            'Conversation2.id'=>null,
            'Conversation.owner_id' => $ownerId
        ),
        array(
            'Conversation3.id'=>null,
            'Conversation.recipient_id' => $ownerId
        ),
     )
);

$order = array('Conversation.created'=>'DESC');

$lastConversations=$this->Conversation->find('all',compact('conditions','joins','order'));

Provided that the name of the table is conversations and the name of your model is Conversation. It's based on the technique described in the accepted answer of Retrieving the last record in each group.

This one seems to be close. Only problem so far is that the results are not being ordered by most recent. Tried adding the "order" parameter but it didn't solve the problem. — Camilo, Dec 18 '15 at 00:32
Thanks. I tried and the problem still persists. Also noticed that grouping is not working as expected. I will stick to the SQL answer, thanks anyway! — Camilo, Dec 18 '15 at 00:59

score 0 · Answer 3 · answered Jun 28 '20 at 08:22

0

This solution worked best for me.

    SELECT t1.*
    FROM chats AS t1
    INNER JOIN
    (
        SELECT
            LEAST(sender_id, receiver_id) AS sender_id,
            GREATEST(sender_id, receiver_id) AS receiver_id,
            MAX(id) AS max_id
        FROM chats
        GROUP BY
            LEAST(sender_id, receiver_id),
            GREATEST(sender_id, receiver_id)
    ) AS t2
        ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
           GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
           t1.id = t2.max_id
        WHERE t1.sender_id = ? OR t1.receiver_id = ?

Source

answered Jun 28 '20 at 08:22

juleslasne

580
3
22

1

What was the problem with the selected answer? Why does this works better for you? – Camilo Jul 07 '20 at 14:00
Hmm good question, from what I remember it didn't retreive the messages as I wanted. Like not in the order or all the ones i wanted – juleslasne Jul 07 '20 at 15:39

Get last message from each conversation

3 Answers3