Devz Devz - 4 months ago 8
SQL Question

Get only first row from join

I'm trying to get a special behavior on a mysql request.

Here are my tables :

users
id, email

infos_users
id_user, firstName, lastName, avatar

chatroom
id

chatroomUsers
id_chatroom, id_user

chatMessage
id, id_chatroom, message, id_sender


The thing i want to do is to get every room with its users based on a user id, to get every chatroom the user is in. To do this, i'm using this request :

SELECT chatroomUsers.id_chatroom,
users.id,
infos_user.avatar,
infos_user.firstName,
infos_user.lastName
FROM chatroomUsers
INNER JOIN users ON chatroomUsers.id_user = users.id
INNER JOIN infos_user ON infos_user.id_user = users.id
WHERE id_chatroom IN
(
SELECT id_chatroom
FROM chatroomUsers
WHERE id_user = ?
)


Where
?
is the id of the user i'm focusing on. Of course, i'm doing some post processing to clean everything and to have a nice array with key = id_chatroom and value an array of users.

So what's next ? Well i want for every row, the last message of the chatroom (which is the one with the corresponding id_chatroom and the highest id_message value). Currently the request returns me this:

id_chatroom id avatar firstName lastName
0 0 avatar1 blah1 blah2
0 1 avatar2 blah3 blah4
1 2 avatar3 blah5 blah6
1 1 avatar1 blah1 blah2
1 3 avatar4 blah7 blah8


And i want something like this :

id_chatroom id avatar firstName lastName lastMessage id_message id_sender
0 0 avatar1 blah1 blah2 blahblahblah 0 1
0 1 avatar2 blah3 blah4 blahblahblah 0 1
1 2 avatar3 blah5 blah6 blahblahblahblah 1 3
1 1 avatar1 blah1 blah2 blahblahblahblah 1 3
1 3 avatar4 blah7 blah8 blahblahblahblah 1 3


Once again, in order to do some post processing. But every time i try to construct my request, it messes up everything.

Can someone help me?

EDIT : There could be no messages recorded for the chatroom ! In that case, i want messages fields to be filled with
null
.

Answer

If you want last message of one chat room, you should use a sub query; if you want to fill message columns which has no message per chat room with null value, you have to use left join, so we can do it like this(maybe a quite simple one):

SELECT     chatroomUsers.id_chatroom, 
           users.id, 
           infos_user.avatar, 
           infos_user.firstName, 
           infos_user.lastName,
           sub.message AS lastMessage,
           sub.id AS id_message,
           sub.id_sender
FROM       chatroomUsers 
INNER JOIN users      ON chatroomUsers.id_user = users.id 
INNER JOIN infos_user ON infos_user.id_user = users.id
LEFT JOIN  (
    SELECT t1.*
    FROM chatMessage t1
    INNER JOIN (
        SELECT MAX(id) AS id, id_chatroom
        FROM chatMessage
        GROUP BY id_chatroom
    ) t2 ON t1.id_chatroom = t2.id_chatroom AND t1.id = t2.id
) sub                  ON chatroomUsers.id_chatroom = sub.id_chatroom 
WHERE      id_chatroom IN 
(
    SELECT id_chatroom 
    FROM   chatroomUsers 
    WHERE  id_user = ?
)
Comments