Select mutual followers excluding blocked contacts

user_192525 0
Viewed 155

Users Table

id   activated 
---  ------------ 
1       1
2       1
3       0
4       1
5       1
6       1

Follow table

follower_id   following_id 
------------ --------------
    1               2
    2               1
    4               2
    5               1
    3               2
    5               2
    6               1
    3               4
    6               2
    3               1
    4               1

Blocking table

blocking_id   blocked_id 
------------ --------------
    2               4

Let's say I am the user with the id of 2

Expected Result

   user_id      mutual_followers 
-------------  -----------------
      1               2
      5               0
      6               0

I want to get the mutual followers of users and exclude blocking or blocked and users whose activated is 0.

How can I get it done all the way by MySQL?

mysql
1 Answers

First use a CTE that returns all the followers of the user, filtered by your conditions to be active and non-blocked and then for each of the followers get their non-blocked followers.
Then filter the followers of the followers so that only the mutual followers are left (or none) and aggregate:

WITH cte AS (
  SELECT f.follower_id user_id
  FROM Follow f
  INNER JOIN Users u ON u.id = f.follower_id AND u.activated
  LEFT JOIN Blocking b ON b.blocking_id = f.following_id AND b.blocked_id = f.follower_id 
  WHERE f.following_id = 2 AND b.blocked_id IS NULL  
)
SELECT c1.user_id, 
       COUNT(c2.user_id) mutual_followers 
FROM cte c1 
LEFT JOIN Follow f ON f.following_id = c1.user_id
LEFT JOIN Blocking b ON b.blocking_id = f.following_id AND b.blocked_id = f.follower_id
LEFT JOIN cte c2 ON c2.user_id = f.follower_id
WHERE b.blocked_id IS NULL 
GROUP BY c1.user_id;

See the demo1 or demo2 (in case the 1st is inaccessible or sometimes does not run).

user_104988280
Related