i making social network, , i'm trying write mysql query find users not friends of specific user. there 1 table - users,that has id_user,name , email.
users: user_id , name , email.. 1 dan a@a.com 2 ron b@b.com 3 james c@c.com 4 joe d@d.com 5 max c@c.com
and friends table show relationships (status=2 mean 2 friends):
friends: user_id , friend_id , status 1 2 2 3 1 2 4 3 2
what mysql query gives list of user_id(from users) not friends of user_id =1 ? (users 4 , 5).
thank's lot,
an anti-join pattern should sufficient.
it looks "a isa friend of b" can represented in friends table a row, either (a,b,2)
or (b,a,2)
. (that's not entirely clear me, i'm going assume that's true now.)
this query first cut. check friends both ways in friends table, i'm going use or
condition in join predicate.
this getting rows users
, along "matching" rows friends
. "trick" predicate in where
clause, eliminates rows found "match" in friends, leaving rows users
didn't have "match".
select u.user_id , u.name users u left join friends f on ( f.user_id = u.user_id , f.friend_id = ? , f.status = 2 ) or ( f.friend_id = u.user_id , f.user_id = ? , f.status = 2 ) f.user_id null or f.friend_id null
unfortunately, mysql optimizer doesn't handle these or
conditions elegantly. cut @ re-write, can try splitting join friends 2 left joins.
i think equivalent:
select u.user_id , u.name users u left join friends f on ( f.user_id = u.user_id , f.friend_id = ? , f.status = 2 ) left join friends g or ( g.friend_id = u.user_id , g.user_id = ? , g.status = 2 ) f.user_id null , g.friend_id null
(i haven't tested second query... goal same thing. rows users
, along matching rows friends
, , filter out rows had match
.
edit
i think we'd need filter out row users
"of specific user". queries above return user himself "not friend" of himself. accomplish that, add where
clause
, u.user_id <> ?
for first query, need take care order of precedence between or
, and
boolean operators... think we'd need wrap 2 or
'd conditions in first query in parens.
followup
there couple of other query patterns can return equivalent result. 2 examples, can use predicates of form not in (subquery)
, or predicates of form not exists (subquery)
.
using "not in (subquery)"
with form, take care subquery not return null value. if there's null value in list, predicate not return true.
select u.user_id , u.name users u u.user_id not in ( select f.user_id friends f f.user_id not null , f.status = 2 , f.friend_id = ? ) , u.user_id not in ( select f.friend_id friends f f.friend_id not null , f.status = 2 , f.user_id = ? ) , u.user_id <> ?
using "not exists (subquery)"
select u.user_id , u.name users u not exists ( select 1 friends f f.user_id = u.user_id , f.status = 2 , f.friend_id = ? ) , not exists ( select 1 friends g g.friend_id = u.user_id , g.status = 2 , g.user_id = ? ) , u.user_id <> ?
Comments
Post a Comment