"friends of friends" as sql query

Question

I am creating an application in which users can connect to each other (something like friends on any social network).

I save these compounds in a table with the following structure:

id_user1 | id_user2 | is_accepted | is_blocked | created_at 

Connections between users are bidirectional, so when two users are connected, there is only one entry in the table. It does not matter if user_id is in id_user1 or id_user2 collumn.

Now I need to write a sql query to get the “friends of friends” of a particular user who are not friends of the user yet. Also, the user must be accepted and not blocked.

In summary, here are the steps I need to follow.

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

• foreach returned by user_ids -> get all associated users (ignore associations with the current user) (make sure that this is also accepted and !blocked ).

How can I fulfill such a request?

Thanks for your help.

Answer 1

 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’s best to keep users the least first, largest second. In this case, the request will be simpler.

Answer 2

For the reasons that others talked about, and because I saw that it works better on other systems, I would go with a line for each direction.

 primary_user_id | related_user_id | is_accepted | is_blocked | created_at 

Then you can also create a clustered index in user_id, which should more than compensate for the overhead of doubling the number of rows.

Your first request is then converted to 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 perform table functions in MySql. If so, include this in the function to make your second request simpler.

Answer 3

When working with the "one record for friendship" table, all queries will be bloated and error prone, because you will often have to write "id_user1 = ... or id_user2 = ...". I would create a view

 CREATE VIEW bidifreinds (id_user1, id_user2, is_accepted, is_blocked, created_at) AS SELECT id_user1, id_user2, is_accepted, is_blocked, created_at FROM friends UNION SELECT id_user2, id_user1, is_accepted, is_blocked, created_at FROM friends 

It will make life easier.

Then you can write

 SELECT f1.id_user1, f2.id_user2 FROM friends f1, friends f2 WHERE f2.id_user1 = f1.id_user2 AND f1.is_accepted AND NOT f1.is_blocked AND f2.is_accepted AND NOT f2.is_blocked 

And I hope that you are not usind MySQL, because MySQL browses views very slowly.

Answer 4

 select id_user1 from friends where is_accepted = 1 and is_blocked = 0 and id_user2 in (select id_user1 from friends where is_accepted = 1 and is_blocked = 0 and id_user2 = :a_user: union select id_user2 from friends where is_accepted = 1 and is_blocked = 0 and id_user1 = :a_user:) union select id_user2 from friends where is_accepted = 1 and is_blocked = 0 and id_user1 in (select id_user1 from friends where is_accepted = 1 and is_blocked = 0 and id_user2 = :a_user: union select id_user2 from friends where is_accepted = 1 and is_blocked = 0 and id_user1 = :a_user:) 

You can add whereClause to filter out: a_user: from the result set.