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

Navigation
1

I have SELECT which does almost exactly what I need, it's a chat system (inbox) similar to facebook. Then he shows me with all the contacts I have a conversation, but it 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 does not respond to me, it does not appear.

I know where the problem is where I ask to show only the messages I received, but I do not know how to solve this so even though I do not receive a response, the message stays there. Anyway, I think I was able to explain, it follows 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
    
asked by anonymous 17.12.2015 / 15:37

1 answer

4

The problem is that you are going to get the sent messages, to search for those received, you just have to do the following: 

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 get the ones sent and received by these two users in both directions.

    
17.12.2015 / 15:52
Which version of PIP should I use? Structure of a chained JSON