Messaging system query to get last messages, number of unread messages and array of users in conversation

Question

I'm working on a messaging system with two tables and another table with the users information.
A conversation can be between 2 or more users. Every conversation has a UID and every messages exchanged between users are labeled with that conversation UID.

Here are the tables :

conversation_list : every row in this table links the user_id and the conversation_id, it also contains the last time the user viewed the conversation.

`id`                 -> unique ID, autoincremented
`user_id`            -> This contains the user associated with the conversation.
`conversation_id`    -> This contains the UID of the conversation
`date_lastView`      -> This field has the time that the user viewed the conversation last

conversation_messages : every row in this table contains a message

`id`                 -> unique ID, autoincremented
`user_id`            -> This contains the user that sent the message.
`conversation_id`    -> This contains the UID of the conversation
`date_created`       -> This contains the time when the message was posted
`message`            -> This contains the message

users : every row in this table contains a user

`User_ID`            -> UID of the user
`FirstName`          -> This contains the first name of the user
`LastName`           -> This contains the last name of the user

I already have a SQL query to get the last message of every conversation. Here it is :

SELECT *
FROM conversation_messages AS m

JOIN
  (SELECT mx.conversation_id,
          MAX(mx.date_created) AS MaxTime
   FROM conversation_messages AS mx
   GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
AND m.date_created = mx.MaxTime

JOIN
  (SELECT mu.conversation_id
   FROM conversation_list AS mu
   WHERE mu.user_id = :USER_ID_CONNECTED
   GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id

JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id

GROUP BY mu.conversation_id
ORDER BY m.date_created DESC

I now would like to add to this perfectly working query the ability to return:

• The number of unread messages for each conversation (count of all messages with the date_creaded bigger then date_lastView of the logged in user)
• An array containing the User_ID of every user in each conversation and sorted by when they last posted a message in the conversation.
• With the same idea of the last array but with the FirstName and LastName of the user.

I tried a few things but I was really unsuccessful, so I'm now asking the SO community for its precious help.

All this can only display conversations where the logged in user takes part in.

It it helps, I created a SQLFiddle

Bonis

Answer 1

Number of unread messages in user's conversation (here user #6):

SELECT l.conversation_id, count(*)
FROM   conversation_list l
JOIN   conversation_messages m ON m.conversation_id = l.conversation_id AND m.date_created > l.date_lastview
WHERE  l.user_id = 6
GROUP BY l.conversation_id

Participants in conversations ordered by last activity:

SELECT conversation_id, user_id, max(date_created) as last_active
FROM   conversation_messages
GROUP BY conversation_id, user_id
ORDER BY conversation_id, last_active

The third query should be just as the second one, just joining one more table on the user_id, right?

Dwarfism

Well I knew how to do this, but my big problem is to implement this in the SQL code that I have in my question. – Bonis

I would like to get all this information in only one query to the database. – Bonis

For optimization reasons? I'm not sure that would be faster, because of added complexity (meaning DB will have harder time optimizing its order of operation) and because output you want is so heterogeneous. I.e. the first (sub)query would be essentially "row per conversation", second/third -- "row per conversation/user". – Dwarfism

Okay, I was thinking that it would be faster. So i guesses that I'm just going to use multiple query's. Thanks – Bonis

I'm not saying it certainly would not be faster. It may be. Then again, you can try to write your code to abstract this away. Later, when you have time and actually make sure that this is a (relative) bottleneck in your application, return to it and see if such a complicated but single query is significantly faster. As they say, make things work first, then make things fast. – Dwarfism

Answer 2

I added 3 improvements to the query to fetch the unread messages:

• If the last_view field is null, it will assume that that user never check his messages, so all the messages will be "unread"
• Identify as new messages just the ones that were not created by the user.
• When there are not unread messages, the count will return 0

SELECT l.conversation_id, count(m.id)
FROM   conversation_list l
LEFT JOIN   conversation_messages m ON m.conversation_id = l.conversation_id AND (l.date_lastview IS NULL OR m.date_created > l.date_lastview)  AND m.user_id != 6
WHERE  l.user_id = 6
GROUP BY l.conversation_id

Hancock