CODEWITHSUNDEEP
mysqlsqlsocial-network-friendship
Ahmed Hammad
Ahmed Hammad

Reputation: 3095

MySQL query that selects non-friends of a user

I have the following schema of a Mysql database:

User(Id, FirstName, LastName, NickName, Etc.)

Request(SenderId, ReceiverId)

Friendship(Id1, Id2)

I consider friendship to be an undirected relation, which means that for every friendship, I insert it twice to the Friendship table. (Let me know if this is not a good idea, please).

What I am trying to retrieve is a list of users, who are not friends to a specific user (let me name him UserX), nor have a current request ongoing to/from him.

My initial trials led me to this:

SELECT User.Id, User.NickName, User.Picture FROM User 
LEFT JOIN Friendship A ON User.Id = A.Id1
LEFT JOIN Friendship B ON User.Id = B.Id2 
LEFT JOIN Request C ON User.Id = C.Sender
LEFT JOIN Request D ON User.Id = D.Reciever 
WHERE User.Id <> ?

And, of course the placeholder is UserX's Id.

This doesn't work because, although the tuples that has friendships or requests with UserX are eliminated, The friends still appear because they have friendships with other users!

Thanks in advance.

Upvotes: 0

Views: 88

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you want an efficient solution, use not exists multiple times:

select u.*
from user u
where not exists (select 1 from friendship f where f.id1 = u.id and f.id2 = ?) and
      not exists (select 1 from friendship f where f.id2 = u.id and f.id1 = ?) and
      not exists (select 1 from request r where r.SenderId = u.id and r.ReceiverId = ?) and
      not exists (select 1 from request r where r.ReceiverId = u.id and r.SenderId = ?);

In particular, this can take advantage of indexes on:

  • friendship(id1, id2)
  • friendship(id2, id1)
  • request(SenderId, ReceiverId)
  • request(ReceiverId, SenderId)

This should have much better performance than solutions that union subqueries together.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

Using a left join to a union list:

select *
from User u1
left join 
    (
    select ID2 as id
    from Friendships
    where ID1 = 'UserX'
    union all
    select ID1
    from Friendships
    where ID2 = 'UserX'
    union all
    select Sender
    from Request 
    where Receiver = 'UserX'
    union all
    select Receiver
    from Request
    where Sender = 'UserX'
    ) ux
on ux.id = u1.id
where ux.id is null
and ux.id <> 'UserX'

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16918

What if you collect all distinct IDs from table "request" and "Friendship" and then select records from Users ID not available in the above list.

SELECT Id, FirstName, LastName, NickName
FROM User
WHERE ID NOT IN
(
    SELECT DSTINCT Id1 ID FROM Friendship
    UNION
    SELECT DSTINCT Id2 FROM Friendship
    UNION
    SELECT DSTINCT SenderId FROM Request
    UNION
    SELECT DSTINCT ReceiverId FROM Request
)A

Upvotes: 0

Related Questions