Reputation: 849
My message system is based on message, threads, and participant
I got this request. It give me the number of message a user has received (= number of message where the user is not the creator)
I count all messages created in a list of threads where the user is not the creator. I got the list of threads by find all thread_id where my user participant is linked
SELECT COUNT(m.id)
FROM message m
INNER JOIN message_thread t ON m.thread_id = t.id
WHERE m.created_by_id != :userId
AND t.id IN (
SELECT t2.id
FROM message_thread t2
INNER JOIN message_participants p ON p.thread_id = t2.id
WHERE p.user_id = :userId
)
So that is for one uniq user.
+-------------+
| count(m.id) |
+-------------+
| 7 |
+-------------+
I'm trying to do the same, but with severals users, and get for each of them, the number of message they received
+---------+-------------+
| User id | count(m.id) |
+---------+-------------+
| 12645 | 1 |
| 985 | 5 |
| 8956 | 15 |
| 37856 | 2 |
+---------+-------------+
I tried to do that with more JOIN instead of t.id IN (
and group by the result, but without success...
Upvotes: 0
Views: 117
Reputation: 1271241
One method is a brute force method, where you bring in all the users you care about and use that information for the processing:
SELECT u.userid, COUNT(m.id)
FROM message m INNER JOIN
message_thread t
ON m.thread_id = t.id CROSS JOIN
(SELECT 12645 as userid UNION ALL
SELECT 985 UNION ALL
SELECT 8956 UNION ALL
SELECT 37856
) u
WHERE m.created_by_id <> u.userId AND
t.id IN (SELECT t2.id
FROM message_thread t2 INNER JOIN
message_participants p
ON p.thread_id = t2.id
WHERE p.user_id = u.userId
)
GROUP BY u.userId;
If you wanted to do this for all users, I would recommend a different approach: count all the messages a user participates in and subtract out the ones where s/he is the creator:
SELECT p.userid,
COUNT(DISTINCT CASE m.created_by_id <> p.userId THEN m.id END) as numMessages
FROM message m INNER JOIN
message_thread t
ON m.thread_id = t.id INNER JOIN
message_participants p
ON p.thread_id = t.id
GROUP BY p.userid;
Upvotes: 1
Reputation: 133410
You should use a group by
SELECT message_thread.your_user_id COUNT(m.id)
FROM message m
INNER JOIN message_thread t ON m.thread_id = t.id
WHERE m.created_by_id != :userId
AND t.id IN (
SELECT t2.id
FROM message_thread t2
INNER JOIN message_participants p ON p.thread_id = t2.id
)
group by message_thread.your_user_id
Upvotes: 0