多关系聊天应用中未读消息的sql

我正在为我正在编写的应用程序添加聊天功能。大部分这些都不是开创性的东西，但我正在慢慢地融化我的大脑，试图找出一点提示用户他们有多少未读信息。一个复杂的问题（不知道这是否重要，或者只是我想得太多了）-谈话可以一对一地发生，在一组朋友中，或者在另一组参加活动的人中。facebook很清楚地发现了这一点。
目前我的数据库模式是这样的（为此目的而简化）：

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

可能关系的维恩图如下所示（同样，我可能考虑过度了）：

在这种情况下：
c1：直接从我的信息到另一个单一的用户
c2：我给一些朋友的信息
c3:从我发送给所有收件人都是朋友的组的消息（c2的poss克隆）
c4：我给所有收件人都是朋友的活动参与者的消息
c5：我给所有收件人都是同一组中的朋友的活动的与会者的消息
c6：我发来的邮件给某些收件人可能不是直接朋友的群
c7：我给某些与会者可能不是直接朋友的活动的与会者的消息
c8：我给某个活动的参与者的消息，其中一些参与者可能在同一个组中，并且可能正在参加同一个活动
获得我参与的对话似乎很容易：

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

从我参与的对话中获得信息似乎也很简单（理论上）：

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

现在我在这里挣扎。如何在顶层计算出我总共有多少封未读邮件，然后根据conversationid计算出多少封？我假设我需要外部连接到messagesread表，然后以某种方式计算空结果，但是我对sql感到有点力不从心。消息来自朋友，或者仅仅是通过一个小组或事件的共同点，这会有什么不同吗，或者conversationmembers表会使所有这些都变得无关紧要吗(我有点想可能-但我在这里是在猜测我自己）。
因为我还在开发这个东西，模式可能会改变。寻找最好的方法来做到这一点，而不是坚持教条的计划。

添加到实体中会很有用 ConversationMessages 它自己的钥匙 ConversationMessageId ，然后将外键添加到 ConversationsReadBy ，这样就可以连接这两个表。您可以像messageid一样考虑这个字段，这样您就可以很容易地识别出这个消息是否已被读取。
如果是这样，则获取未读邮件的查询可能如下所示：

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

假设尚未发送的消息不会在 ConversationReadyBy table，你可以用 left join 结合 where 条款，只保留那些与 ConversationReadBy table。
如果你有 flag 相反，您可以更改为以下条款：

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

假设您的userid引用相同的过滤器，您可以使用以下方法简化查询：

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

多关系聊天应用中未读消息的sql

1条答案

相关问题

热门标签

最新问答