Get mutual relationship from database table-CodePudding

I have table user_follow which has only two columns: who and whom. They represent links between users when WHO follows WHOM. If two users follow each other, they are considered friends. In such case the table would contain records:

WHO   WHOM
1     2
2     1

Where 1 and 2 are simply user IDs.

In order to determine if two users are friends, I have to query the table and use simple condition

SELECT COUNT(*) 
FROM user_follow 
WHERE (who = 1 AND whom = 2) OR (who = 2 AND whom = 1)

If I get 2 then they are friends.

But if I want to load list of all user's friends, I cannot do it that way. So i came up with sql join into itself:

SELECT uf2.whom 
FROM user_follow AS uf1 
LEFT JOIN user_follow AS uf2 ON uf1.who = uf2.whom 
WHERE uf1.whom = ? AND uf2.who = ? ORDER BY uf2.whom

I made a dummy table to test it and it works. But I would like for someone to confirm this is the correct solution.

CodePudding user response：

I think your query is correct if you supply the same parameter value for ?. I find the query below simpler to understand.

The query below lists the friends (users who follow each other) of a user:

SELECT uf1.whom
FROM user_follow AS uf1
INNER JOIN user_follow AS uf2
    -- filter down to followed users
    ON uf1.whom = uf2.who
    -- followed users follow their followers
    AND uf1.who = uf2.whom
-- the user whose friends are listed
WHERE uf1.who = ?
ORDER BY 1
;