CODEWITHSUNDEEP
mysql
Ariel
Ariel

Reputation: 21

mySql - find all not friends of a user

I am making a social network, and I'm trying to write a mySql query that find all the users that are not friends of a specific user. There is one table - USERS,that has id_user,name and email.

USERS:
user_id , name , email..
1         Dan     [email protected]
2         Ron     [email protected]
3         James   [email protected]
4         Joe     [email protected]
5         Max     [email protected]

And FRIENDS table that show the relationships (status=2 mean that the two are friends):

FRIENDS:
user_id , friend_id , status
1             2         2
3             1         2
4             3         2

What is the mySql query that gives the list of the user_id(from USERS) that are not friends of user_id =1 ? (users 4 and 5).

Upvotes: 0

Views: 938

Answers (1)

spencer7593
spencer7593

Reputation: 108380

An anti-join pattern should be sufficient.

It looks like "a isa friend of b" can be represented in the friends table by a a row, either (a,b,2) or (b,a,2). (That's not entirely clear to me, but I'm going to assume that's true for now.)

This query is just a first cut. To check for friends both ways in the friends table, I'm going to use an OR condition in the join predicate.

This is essentially getting all rows from users, along with "matching" rows from friends. The "trick" is the predicate in the WHERE clause, which eliminates all rows that found a "match" in friends, leaving only those rows from users that didn't have a "match".

SELECT u.user_id
     , u.name
  FROM users u
  LEFT
  JOIN friends f
    ON ( f.user_id   = u.user_id AND f.friend_id = ? AND f.status = 2 )
    OR ( f.friend_id = u.user_id AND f.user_id   = ? AND f.status = 2 )
 WHERE f.user_id   IS NULL
    OR f.friend_id IS NULL

Unfortunately, the MySQL optimizer doesn't usually handle these OR conditions too elegantly. As another cut at a re-write, we can try splitting the join to friends into two left joins.

I think this is equivalent:

SELECT u.user_id
     , u.name
  FROM users u
  LEFT
  JOIN friends f
    ON ( f.user_id   = u.user_id AND f.friend_id = ? AND f.status = 2 )
  LEFT
  JOIN friends g
    OR ( g.friend_id = u.user_id AND g.user_id   = ? AND g.status = 2 )
 WHERE f.user_id   IS NULL 
   AND g.friend_id IS NULL

(I haven't tested that second query... the goal is to do the same thing. All rows from users, along with matching rows from friends, and then filter out all the rows that had a match.

EDIT

I think we'd also need to filter out the row from users "of a specific user". The queries above would return the user himself as "not a friend" of himself. To accomplish that, we could add to the WHERE clause 

 AND u.user_id <> ?

For the first query, we need to take care with the order of precedence between the OR and AND boolean operators... I think we'd need to wrap the two OR'd conditions in the first query in parens.

FOLLOWUP

There are a couple of other query patterns that can return an equivalent result. As two examples, we can use predicates of the form NOT IN (subquery), or predicates of the form NOT EXISTS (subquery).

using "NOT IN (subquery)"

With this form, take care that the subquery does not return a NULL value. If there's a NULL value in the list, the predicate will not return TRUE.

SELECT u.user_id
     , u.name
  FROM users u
 WHERE u.user_id NOT IN ( SELECT f.user_id
                            FROM friends f
                           WHERE f.user_id IS NOT NULL
                             AND f.status = 2
                             AND f.friend_id = ?
                        )
   AND u.user_id NOT IN ( SELECT f.friend_id
                            FROM friends f
                           WHERE f.friend_id IS NOT NULL
                             AND f.status = 2
                             AND f.user_id = ?
                        )
   AND u.user_id <> ?

using "NOT EXISTS (subquery)"

SELECT u.user_id
     , u.name
  FROM users u
 WHERE NOT EXISTS ( SELECT 1 
                      FROM friends f
                     WHERE f.user_id = u.user_id
                       AND f.status = 2
                       AND f.friend_id = ?
                  )
   AND NOT EXISTS ( SELECT 1
                      FROM friends g
                     WHERE g.friend_id = u.user_id
                       AND g.status = 2
                       AND g.user_id = ?
                  )
   AND u.user_id <> ?

Upvotes: 2

Related Questions