"friends of friends" like sql query

Question

I am building an application where users can connect with each other (something like friends in any social network).

I am saving that connections in a table with the following structure:

id_user1 | id_user2 | is_accepted | is_blocked | created_at

The connections between users are bi-directional, so when two users are connected there is only one record in the table. Doesnt matter if the user_id is in id_user1 or id_user2 collumn.

Now i need to write a sql query to get "friends of friends" of a certain user that are not already friends of the user. Also the user must be accepted and not blocked.

In resume, here are the steps i need to execute.

1. Find all the users id associated with the user I want (id_user1 = current_user or id_user2 = current_user and is_accepted and !blocked)

2. foreach of the returned user_ids --> get all the associated users( ignore associations with current user) (make sure it is accepted and !blocked also).

How can I do such query?.

Thanks for your help.

Answer 1

For the reasons others have mentioned, and because I've seen it work better in other systems, I'd go with a row for each direction.

primary_user_id | related_user_id | is_accepted | is_blocked | created_at

You can also then create a clustered index on the user_id which should more than offset the overhead of doubling the number of rows.

Your first query would then translate into something like this:

SELECT f1.related_user_id
FROM friends f1
WHERE f1.primary_user_id = @current_user
AND f1.is_accepted = 1 AND f1.is_blocked = 0
AND EXISTS (
  SELECT *
  FROM friends f2
  WHERE f1.related_user_id = f2.primary_user_id
  AND f2.related_user_id = @current_user
  AND f2.is_accepted = 1 AND f2.is_blocked = 0

Not sure if you can do table functions in MySql. If so then wrap this up into a function to make your second query simpler.

Answer 2

SELECT  CASE f2.id_user1 WHEN CASE f1.id_user1 WHEN $user THEN f1.id_user2 ELSE f1.id_user1 END THEN f2.id_user2 ELSE f2.id_user1 END
FROM    friends f1
JOIN    friends f2
ON      f2.id_user1 = CASE f1.id_user1 WHEN $user THEN f1.id_user2 ELSE f1.id_user1 END
        OR f2.id_user2 = CASE f1.id_user1 WHEN $user THEN f1.id_user2 ELSE f1.id_user1 END
WHERE   (f1.id_user1 = $user OR f1.id_user = $user)
        AND f1.is_accepted = 1
        AND f2.is_accepted = 1
        AND f1.is_blocked = 0
        AND f2.is_blocked = 0
        AND NOT (f1.id_user1, f1.id_user2) = (f2.id_user1, f2.id_user2)

Note that it is better to store the users least first, greatest second. In this case the query would be more simple.