CODEWITHSUNDEEP
mysqlsql
brpaz
brpaz

Reputation: 3658

"friends of friends" like sql query

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.

Upvotes: 6

Views: 3110

Answers (4)

rob
rob

Reputation: 210

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.

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425843

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.

Upvotes: 2

Kal
Kal

Reputation: 24910

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 a whereClause to weed out :a_user: from the resultset.

Upvotes: 0

Christoph Walesch
Christoph Walesch

Reputation: 2437

When working with the one-record-per-friendship table directly, all queries will be bloated and error-prone, because you will have to write 'id_user1 = ... or id_user2 = ...' often. I'd 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

This will make life much 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 you're not usind MySQL, because MySQL is very slow at querying over views.

Upvotes: 1

Related Questions