How to fetch messages from A to B and from B to A in the same query?

Asked

Viewed 104 times

1

I have a SELECT which does almost exactly what I need, is a chat system (Inbox) similar to facebook. Then he shows me with all the contacts that I have a conversation, but brings me the last message and the date.

My problem is this, if I send a message to someone for the first time and this person doesn’t answer me, it doesn’t show up.

I know where the problem is is where I ask to show only the messages I received, but I don’t know how to fix it so that even if I don’t get a reply, the message stays there. Anyway, I think I can explain, follow the SELECT and the structure of my table:

Table

user_inbox_id (id)
user_inbox_from (quem enviou)
user_inbox_to (quem recebeu)
user_inbox_msg (mensagem)
user_inbox_date (data)
user_inbox_new (mostra se foi lida ou nao)

Select

SELECT * FROM user_inbox ta, 
(SELECT user_inbox_to, user_inbox_from, max(user_inbox_date) ultima_msg 
FROM user_inbox
GROUP BY user_inbox_to, user_inbox_from) um, user

WHERE 
ta. user_inbox_to = um.user_inbox_to 
AND ta.user_inbox_from = um.user_inbox_from
AND ta.user_inbox_date = um.ultima_msg
AND ta.user_inbox_to = '$user_id'
AND ta.user_inbox_from = user.user_id
ORDER BY user_inbox_date DESC
mysql
  • 2

    Welcome to Stack Overflow in Portuguese! I didn’t understand your problem, friend, I could explain it better?

  • Hello! My problem is this, this SELECT is only bringing messages when I receive, when I send message it is not bringing, on account of this line AND ta.user_inbox_to = '$user_id' However, by removing it, it does not speak... In other words this box is who receives, I need in this select to say that I also want it to check the messages I sent, in the expensive would be the column user_inbox_from = '$user_id', was clearer friend?

  • 1

    If I understand correctly, you want the messages that user A sent to B as much as B sent to A, all in the same query. That’s it?

1 answer

4


The problem is you’re going to get the outgoing messages, to get the incoming ones, just do it like this:

SELECT * FROM user_inbox ta, 
(SELECT user_inbox_to, user_inbox_from, max(user_inbox_date) ultima_msg 
FROM user_inbox
GROUP BY user_inbox_to, user_inbox_from) um, user

WHERE 
ta. user_inbox_to = um.user_inbox_to 
AND ta.user_inbox_from = um.user_inbox_from
AND ta.user_inbox_date = um.ultima_msg
AND ((ta.user_inbox_to = '$user_id' AND ta.user_inbox_from = user.user_id ) OR (ta.user_inbox_to = user.user_id AND ta.user_inbox_from = '$user_id'))
ORDER BY user_inbox_date DESC

So with the two users in question you can fetch the sent and received by these two users in both directions.

  • 1

    Perfect friend! Thank you so much for your help!

  • 1

    You’re welcome Leonardo

Browser other questions tagged mysql

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