CODEWITHSUNDEEP
mysqlsql
Bouffe
Bouffe

Reputation: 849

MySQL query for on user to list of users

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

ScaisEdge
ScaisEdge

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

Related Questions