How to list the latest message from all conversations

Asked

Viewed 195 times

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 have selected below on mysql and is working, but in sqlite returns error next to Union, (I don’t know much about syntax 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

inserir a descrição da imagem aqui

Select result

inserir a descrição da imagem aqui

java android mysql sqlite

  • What is the error message?

  • In mysql works perfectly, in Android sqlite returns error next to Union

  • Just that? Tries to run every part of the union separately. I’m not sure whether to put alias in column is needed by AS alias on sqlite, but it jumped out at me

  • Anyway, it is possible to make an equivalent consultation without union, using a or in the clause where and a case in the select. My experience says that removing this union can make the query lighter if it is being run repeatedly

2 answers

1


As Jefferson Quesado suggested, 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 running in SQL Fiddle with Sqlite (SQL.js).

  • 1

    Thank you very much! worked perfectly, I was already thinking of making 2 queries in db, to get the result, but you saved me! vlw even!

0

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

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

  • This way, it returns the last message I sent and also the last message I received from each conversation

  • @Julioantonini No... Tested the code?

  • That was my first idea, when I needed it, but I saw that it wouldn’t work, but thank you so much for your help

  • @Julioantonini I think my comment was not very enlightening: only returns the last message (sent or received). LIMIT 1 only returns 1 result!

  • a yes with the limit it returns only 1 result the last, however there can be unlimited conversations, then would have to make 2 selects to pick the last message of each conversation

Browser other questions tagged java android mysql sqlite

You are not signed in. Login or sign up in order to post.