How to list the last message of all conversations

Question

How to list the last message of all conversations

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm creating a chat in android studio, so I need to list the last message between the chat user and his contacts, regardless of whether he sent or received the last message.

I made the select below in mysql and it works, but in sqlite it returns error next to union, (I do not understand much of the syntax of sqlite )

Code below using variable 7

SELECT mensagens.* FROM mensagens JOIN (
    SELECT user, MAX(id) m FROM (
        (SELECT id, id_user user, data FROM mensagens WHERE contact_user=7) 
        UNION 
        (SELECT id, contact_user user, data FROM mensagens WHERE id_user=7)
    ) t1 GROUP BY user
) t2 ON ((contact_user=7 AND id_user=user) OR (contact_user=user AND id_user=7)) 
AND (id = m) ORDER BY id DESC

My table

Selectresult

mysql java android sqlite

asked by anonymous 26.09.2017 / 01:59

2 answers

0

To achieve the desired result for only one specific user can be used:

SELECT * FROM mensagens WHERE id_user=7 OR contact_user=7 ORDER BY data DESC LIMIT 1;

04.10.2017 / 10:32

Problem with subquery Javascript Category Filter

score 1 · Accepted Answer

As suggested by Quesado, the query becomes simpler using a case:

SELECT mensagens.* FROM mensagens JOIN (
    SELECT 
      (case when id_user=7 then contact_user
        when contact_user=7 then id_user
       end) user
      , MAX(id) m 
    FROM mensagens 
    WHERE id_user=7 or contact_user=7
    GROUP BY user
) t1 ON id = m 
ORDER BY id DESC

Example working in SQL Fiddle with SQLite (SQL.js).