List last messages of chats

Question

CREATE TABLE message
    (`id` int, `from_user_id` text, `to_user_id` text, `created` datetime, `message` text)
;

INSERT INTO message
    (`id`, `from_user_id`, `to_user_id`, `created`, `message`)
VALUES
    (1, 'a', 'b', '2013-01-14 00:00:00', 'hello'),
    (2, 'b', 'a', '2013-01-14 00:00:00', 'world'),
    (3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!')
;

I want to get the last messages applicable to (either sent / received by ) a user. In the above example, there are only 2 users (a, b), and the last message applicable to them is id = 3. So the expected result is

'a', 3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!'
'b', 3, 'b', 'a', '2013-01-15 00:00:00', 'hi!!'

There are similar questions in SO, the closest I found was here

The last answer seems to what I wanted, but it is complex to wrap my head around, and also couldn't make it work.

I use lovefield and run it in the browser. It supports join and subset of sql syntax, but may not advanced use. Below code gives last message for a provided user id, but I want rows for all users.

SELECT m.*
FROM message m
WHERE 'a' in (from_user_id, to_user_id) AND
      m.created = (SELECT MAX(m2.created)
                   FROM message m2
                   WHERE (m2.from_user_id = m.from_user_id AND m2.to_user_id = m.to_user_id) OR
                         (m2.from_user_id = m.to_user_id AND m2.to_user_id = m.from_user_id) 
                  )
ORDER BY m.created DESC

sqlfiddle

Why is this tagged sqlite if you're using a different database? — Shawn, Nov 15 '19 at 21:40
I see in the website direct comparison with sqlite https://github.com/google/lovefield/blob/master/docs/sqlite_comparison.md and the test harness is ported from sqlite. I thought it is also the least common denominator among relational databases. — bsr, Nov 15 '19 at 21:57

score 1 · Accepted Answer · answered Nov 15 '19 at 20:56

1

Below code gives last message for a provided user id, but I want rows for all users.

Your query looks fine. Just remove the condition that filters on a specific user, and you should get the result that you expect:

SELECT m.*
FROM message m
WHERE m.created = (SELECT MAX(m2.created)
                   FROM message m2
                   WHERE (m2.from_user_id = m.from_user_id AND m2.to_user_id = m.to_user_id) OR
                         (m2.from_user_id = m.to_user_id AND m2.to_user_id = m.from_user_id) 
                  )
ORDER BY m.created DESC

Here is an updated version of your fiddle. I added a few more records for other users, and the query returns:

id  from_user_id  to_user_id  created               message
4   d             c           2013-01-17T00:00:00Z  yo!!
3   b             a           2013-01-15T00:00:00Z  hi!!

answered Nov 15 '19 at 20:56

GMB

216,147
25
84
135

thanks a ton. It been years since I used sql directly :-) cheers – bsr Nov 15 '19 at 21:01
instead of `WHERE m.created = (SELECT MAX(m2.created)`, where created can have duplicates in rare case, can we use `id`. Something like `WHERE m.id = ...` – bsr Nov 15 '19 at 21:10
@bsr: if there are top ties, which message do you want to display? – GMB Nov 15 '19 at 21:12
1

that's true :-) ya, there can't be a tie in my app (though no constraints). Even if, it doesn't matter in my case. thanks again – bsr Nov 15 '19 at 21:14

score 1 · Answer 2 · answered Nov 15 '19 at 21:10

1

Use a CTE and UNION ALL:

with cte as (
  select *,
    min(from_user_id, to_user_id) user1,
    max(from_user_id, to_user_id) user2,
    row_number() over (partition by min(from_user_id, to_user_id) order by created desc) rn1,
    row_number() over (partition by max(from_user_id, to_user_id) order by created desc) rn2
  from message
)
select user1 as user, id, from_user_id, to_user_id, created, message 
from cte
where rn1 = 1
union all
select user2 as user, id, from_user_id, to_user_id, created, message 
from cte
where rn2 = 1
order by user

See the demo.
Results:

| user | id  | from_user_id | to_user_id | created             | message |
| ---- | --- | ------------ | ---------- | ------------------- | ------- |
| a    | 3   | b            | a          | 2013-01-15 00:00:00 | hi!!    |
| b    | 3   | b            | a          | 2013-01-15 00:00:00 | hi!!    |

answered Nov 15 '19 at 21:10

forpas

160,666
10
38
76

thanks for your help and showing a better sql fiddle app. I am not sure this will work in my simple sql engine (lovefield as mentioned in the question). will try it. Out of curiosity, any advantage compared to the other answer. – bsr Nov 15 '19 at 21:17
I think you want as 1st column the user id for which that row is the last message, right? – forpas Nov 15 '19 at 21:18
yup :-) you are absolutely right. Wasn't thinking :-) A search for `partition` on https://github.com/google/lovefield didn't return any result. so is there a way with common sql syntax – bsr Nov 15 '19 at 21:23

score 0 · Answer 3 · answered Nov 15 '19 at 21:45

Using UNION:

with lastmessages as
       (select id, from_user_id as user_id, from_user_id, to_user_id, max(created) mx, message
        from message group by(from_user_id) 
        UNION ALL
        select id, to_user_id as user_id,   from_user_id, to_user_id, max(created) mx, message
        from message group by(to_user_id) )
select user_id, id, from_user_id, to_user_id, max(mx) mx, message
  from lastmessages
  group by user_id;

Output

user_id|id|from_user_id|to_user_id|mx|message
a|3|b|a|2013-01-15 00:00:00|hi!!
b|3|b|a|2013-01-15 00:00:00|hi!!

List last messages of chats

3 Answers3

Output