MySQL数据库架构，用于简单聊天一对一

Question

I trying to do a simple chat with these schema:

mes_id  int(10) unsigned Auto Increment  
mes_useid_receiver  int(10) unsigned     
mes_useid_sender    int(10) unsigned     
mes_date    int(10) unsigned     
mes_message text     
mes_read    tinyint(3) unsigned [0]  
mes_visible_for_who int(10) unsigned NULL   NULL = both; 0 = none; ID user

The problem is, how I can get the list of contacts of received messages and sent messages?

I'm trying something like this:

SELECT use_name, MAX(mes_date) AS date 
FROM message JOIN user ON mes_useid_receiver=use_id 
WHERE uti_id!=1 
AND (mes_useid_receiver=1 OR mes_useid_sender=1) 
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1) 
GROUP BY use_id 
ORDER BY date DESC

But with this query I just can get the contacts that send a message to user_id = 1, I want to get also the contacts that user_id = 1 sent messages.

Edited:

With union I can get almost that I want:

SELECT use_id, use_name, mes_read FROM message
JOIN user ON mes_useid_receiver=use_id
WHERE use_id!=1 
AND (mes_useid_receiver=1 OR mes_useid_sender=1) 
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1) GROUP BY use_id

UNION

SELECT use_id, use_name, mes_read FROM message
JOIN user ON mes_useid_sender=use_id
WHERE use_id!=1 
AND (mes_useid_receiver=1 OR mes_useid_sender=1) 
AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1) GROUP BY use_id

I need the mes_date to order the list, but if I select the mes_date I will get duplicate rows.

Thanks for the help.

Answer 1

if you are going in this root then my suggetion is something like below:

SELECT *  FROM(

    (SELECT use_id, use_name, mes_read ,mes_date FROM message
    JOIN user ON mes_useid_receiver=use_id
    WHERE use_id!=1 
    AND (mes_useid_receiver=1 OR mes_useid_sender=1) 
    AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1)) GROUP BY use_id 

    UNION

    (SELECT use_id, use_name, mes_read ,mes_date FROM message
    JOIN user ON mes_useid_sender=use_id
    WHERE use_id!=1 
    AND (mes_useid_receiver=1 OR mes_useid_sender=1) 
    AND (mes_visible_for_who IS NULL OR mes_visible_for_who=1)) GROUP BY use_id 

) as d Order By mes_date desc

also see if you can avoid grouping in within each selection ( you will feel a bad performance )

regards