find user2 friends + mutual count with user1

Question

mysql to find user2 Friends and get mutual_count with user1 ( with every user2 friend)

Table users

user_id |   username
------------------
1       |   user1
2       |   user2
3       |   user3
4       |   user4
5       |   user5

table friends

user_one_id |   user_two_id
------------------------
1           |   2
1           |   4
2           |   3
2           |   4
3           |   1
5           |   2

Expected output //( user2 friends : check result user_id with user1 to get mutual count)

user_id | mutual_count
------------------------
1       |   2             // user1 and user1 mutual `users(user2,user3,user4)`
3       |   1             // user3 and user1 mutual users(user2)
4       |   1             // user4 and user1 mutual users(user2)
5       |   0             // user5 and user1 -> No mutual users

mysql statement

SELECT  users.user_id,

    (SELECT count(a.friendID) FROM
        (   SELECT user_two_id friendID FROM friends  WHERE user_one_id = users.user_id
            UNION 
            SELECT user_one_id friendID FROM friends WHERE user_two_id = users.user_id
        ) AS a 
        JOIN  
        (   SELECT user_two_id friendID FROM friends WHERE user_one_id = 1
            UNION 
            SELECT user_one_id friendID FROM friends WHERE user_two_id = 1
        ) AS b 
        ON  a.friendID = b.friendID
    ) as mutual_count

FROM friends LEFT JOIN  users    
ON friends.user_one_id = users.user_id or  friends.user_two_id = users.user_id  
WHERE (friends.user_one_id = 2 OR friends.user_two_id = 2)   AND users.user_id != 2

Error I get Unknown column 'users.user_id' in 'where clause' problem subquery to get mutual_count canot know users.user_id

any help appreciated

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-05-11T15:31:24

Your hunch to use a union between the friends table was on the right track. The issue here is that a relationship between friends is only recorded in one direction, but we really need to detect each relationship regardless of direction. The UNION trick allows each relationship to be counted properly.

In the query below, the base table is this union. We self join it, on the conditions that:

The first table's user_one_id is 2
The first table's user_two_id matches to the second table's user_one_id
The second table's user_two_id is not 2, which would just be a cycle back to where we started

Then, we just aggregate by the first table's user_two_id and count the number of distinct friends to get the result.

SELECT
    t1.user_two_id AS user_id,
    COUNT(DISTINCT t2.user_two_id) AS mutual_count
FROM
(
    SELECT user_one_id, user_two_id
    FROM friends
    UNION ALL
    SELECT user_two_id, user_one_id
    FROM friends
) t1
LEFT JOIN
(
    SELECT user_one_id, user_two_id
    FROM friends
    UNION ALL
    SELECT user_two_id, user_one_id
    FROM friends
) t2
    ON t1.user_one_id = 2 AND
       t1.user_two_id = t2.user_one_id AND
       t2.user_two_id <> 2
WHERE
    t1.user_two_id <> 2
GROUP BY
    t1.user_two_id
ORDER BY
    t1.user_two_id;

find user2 friends + mutual count with user1

1 Answers

Demo