user5779223 user5779223 - 6 months ago 13
MySQL Question

How to select messages from two different tables in a specific order with Mysql?

There are totally five data tables:

theUser (*id*, name)
theGroup (*id*, name)
Membership (*group_id*, *user_id*)
gpMsge (*id*, sender, receiver, content, time) # message within group
idMsge (*id*, sender, receiver, content, time) # message between user and user


The relationship among
theUser
,
theGroup
and
Membership
has been explained from this question. Now I want to extract the conversation(
gpMsge
and
idMsge
) and display the name of corresponding sender (
theUser
and
theGroup
) in the order of the newset message in that conversation. Indeed the business logic is exactly the same with
Whatsapp
. In the first part, the sender names (individual user name if the conversation is p2p or group name if it is within group) show with the order by the time of the newest message in that conversation. And below each sender name, there are should be five newest message in that conversation. In the second part, all group with no conversation is displayed (no need to specify the order in this part).

The difficulty for me now is the order of the conversation as the
gpMsge
and
idMsge
is isolated. How can I sort the
time
value in each table and get the name of
theUser
and
theGroup
in a table? Thank you in advance!

Answer

You can use "UNION" so you can compare the time between the two different tables

This query will list the last 5 conversations from both tables.

SELECT id,sender,receiver,content,time
FROM gpMsge
UNION
SELECT id,sender,receiver,content,time
FROM idMsge
ORDER BY time DESC 
LIMIT 5

UPDATE

full query will be like this::

 SELECT US.name AS sender,UR.name AS receiver,content,time 
     FROM gpMsge AS GM 
     LEFT JOIN theUser AS US ON GM.sender=US.id 
     LEFT JOIN theUser AS UR ON GM.receiver=UR.id 
 UNION 
 SELECT US.name AS sender,UR.name AS receiver,content,time 
    FROM idMsge AS UM 
    LEFT JOIN theUser AS US ON UM.sender=US.id 
    LEFT JOIN theUser AS UR ON UM.receiver=UR.id 
 ORDER BY time DESC
 LIMIT 5;

see demo

Comments