Reputation: 3658
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.
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
)
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
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
Reputation: 425803
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
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
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