How to select the total messages of users conversations?

#1

Hello using only 1 query I need to select the count of all the messages inside conversations that user participate.
At the moment I have 2 conversations. The first conversation has a total of 8 messages and has 3 participants: 1, 2 and 3. The second conversation has only 1 message and has 2 participants: 2 and 3.
My expected result is:

  • User 1, 8 messages (he is only in first conversation)
  • User 2, 9 messages (he is in both conversations)
  • User 3, 9 messages (he is in both conversations)

My current query is:

SELECT c.cid, cu.uid, COUNT(*) AS messages
FROM conversations c
JOIN conversations_users cu ON (cu.uid IN (1,2,3) AND cu.cid = c.cid)
LEFT JOIN conversations_messages cm ON (cm.cid = c.cid)
GROUP BY cm.cid, cu.uid

but its result is:

cid uid messages
3 1 8
3 2 8
3 3 8
4 2 1
4 3 1
#2

Oh, I’m stupid. I only need to group by cu.uid

1 Like
#3

now that’s not true.

maybe you are tired. ah! the life of a programmer/hacker = always tired :slight_smile:

1 Like