unread count and grouping

Question

I have a very similar setup to this answer Is there a simpler way to achieve this style of user messaging?

However I'm having an issue with getting the total unread conversations and not unread messages.

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`

I'm only selecting p.conversation_id for testing purposes, but the result I am getting is:

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

If I were to put the results in a PHP while loop I get

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
)

The result I am wanting is unread: 8

score 0 · Answer 1 · answered Mar 16 '15 at 10:27

0

I think you just want conditional aggregation:

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

In addition, if you want the total over all conversations, leave out the group by.

answered Mar 16 '15 at 10:27

Gordon Linoff

1,242,037
58
646
786

Thank you, but that gives me the total messages unseen and not the conversations. There are currently 8 conversations with a total of 469 messages between them all. All of them contain unseen messages so I'm after the number 8 and not 469. If that makes sense? – Jack Mar 16 '15 at 10:36

unread count and grouping

1 Answers1