SQL for unread messages in chat app with multiple relationships

user_7245843 0
Viewed 943

I am adding a chat function to an application I'm writing. Most of this is not groundbreaking stuff, but I'm melting my brain slightly trying to figure out the bit that notfies users about the number of unread messages they have. A complication (don't know whether this is important, or just me over thinking it) - conversations can happen one-to-one, in one group of friends or in another group of people attending an event. Facebook clearly figured this one out.

My database schema at the moment it something like this (simplified for the purpose of SO):

Conversations
    id
    groupID // populated if group, otherwise 0
    eventID // populated if event, otherwise 0
    
ConversationMembers
    id
    conversationID
    userID
    
ConverationMessages
    id
    conversationID
    userID
    message
    messageSent // timestamp of DB insert
    
ConverstionsReadBy
    id
    conversationMessageID
    userID

A Venn diagram of the possible relationships is as follows (again, I may be over thinking this):

enter image description here

In this scenario:

C1: Direct message from me to another single user
C2: Message from me to a number of friends
C3: Message from me to a group where all recipients are friends (poss clone of C2)
C4: Message from me to attendees of an event where all recipients are friends
C5: Message from me to attendees of an event where all recipients are friends in same group
C6: Message from me to group where some recipients may not be direct friends
C7: Message from me to attendees of an event where some attendees may not be direct friends
C8: Message from me to attendees of an event where some attendees may be in same group and may be attending same event

Getting conversations I'm involved in seems easy enough:

SELECT c.id FROM Conversations c INNER JOIN ConversationMembers cm ON c.id = cm.conversationID WHERE cm.userID = :myID

Getting the messages from a conversation I'm involved with also seems straightforward (in theory):

SELECT m.message FROM ConversationMessages m INNER JOIN Converstions c ON m.conversationID = c.id INNER JOIN ConversationMembers cm ON c.id = cm.conversationID WHERE cm.userID = :myID AND c.id = :conversationID ORDER BY cm.messageSent DESC

Now here's where I'm struggling. How do I work out at the top level how many unread messages I have in total, and then how many by conversationID? I assume I need to outer join to the messagesRead table then count the null results somehow, but I'm feeling a bit out of my depth with the SQL. Will the fact messages are from friends or just those in common via a group or event make any difference, or will the ConversationMembers table make all that irrelevant? (I kinda think it might - but I'm second guessing myself here).

As I'm still developing the thing, the schema can change. Looking for the best way to do this rather than sticking to a dogmatic plan.

mysql sql
1 Answers

It would be usefull to add to the entity ConversationMessages its own key, something like ConversationMessageId, and then adding a foreign key to the ConversationsReadBy, so you could join these two tables. You could think on this field like the messageId, so you can in an easy way recognize if this message has been read or not.

If so, your query to get unread messages could look like this:

SELECT m.message 
FROM ConversationMessages m 
  INNER JOIN Conversations c 
    ON m.conversationID = c.id 
  INNER JOIN ConversationMembers cm 
    ON c.id = cm.conversationID 
  LEFT JOIN ConversationsReadyBy rb
    ON rb.conversationMessageId=m.id
WHERE cm.userID = :myID
  AND rb.conversationMessageId IS NULL

Assuming that the messages that haven't been sent do not create a record in the ConversationReadyBy table, you can use a left join in combination with where clause, keeping just those records which don't have joined with the ConversationReadBy table.

If you have a flag instead, you could change to this clause:

WHERE cm.userID = :myID AND c.id = :conversationID
  AND rb.[$yourSwitchField]= 0

Assuming your userId refers to the same to the filter, you could simplify your query with:

SELECT m.message 
FROM ConversationMessages m 
  LEFT JOIN ConversationsReadyBy rb
    ON rb.conversationMessageId=m.id
WHERE rb.userID = :myID
  AND rb.conversationMessageId IS NULL
user_68264790
Related