mysql - 未读计数和分组

Question

我的设置与此答案非常相似Is there a simpler way to achieve this style of user messaging?

但是，我在获取未读对话总数而不是未读消息时遇到问题。

SELECT p.conversation_id, COUNT(p.conversation_id) as unread
FROM participation AS p
INNER JOIN messages AS m
    ON m.conversation_id = p.conversation_id AND m.seen = 0
WHERE p.uid1 = {$user->data['id']}
GROUP BY m.conversation_id`

我仅选择 p.conversation_id 进行测试，但我得到的结果是:

Array
(
    [conversation_id] => 1
    [unread] => 77
)

如果我将结果放入 PHP while 循环中，我会得到

Array
(
    [conversation_id] => 1
    [unread] => 77
)

Array
(
    [conversation_id] => 3
    [unread] => 7
)

Array
(
    [conversation_id] => 8
    [unread] => 1
)

Array
(
    [conversation_id] => 17
    [unread] => 35
)

Array
(
    [conversation_id] => 22
    [unread] => 2
)

Array
(
    [conversation_id] => 24
    [unread] => 305
)

Array
(
    [conversation_id] => 29
    [unread] => 41
)

Array
(
    [conversation_id] => 31
    [unread] => 1
)

我想要的结果是未读:8

Answer 1

我认为你只想要条件聚合:

SELECT p.conversation_id,
       SUM(m.seen = 0) as unseen, 
       SUM(m.seen <> 0) as seen
FROM participation AS p INNER JOIN
     messages AS m
     ON m.conversation_id = p.conversation_id AND m.seen = 0
WHERE p.uid1 = {$user->data['id']}
GROUP BY m.conversation_id

此外，如果您想要所有对话的总计，请省略分组依据。