I have a User table
/ id_user / username / ..
friends table (pair AB and BA):
/ friend_of / friend_to
and a friend_request table (used for pending friend requests)
/ id_req/ sent_from / sent_to
I would like to have a little search for new friend feature (autosuggestion style) and I would like NOT to show in the list friends that are ALREADY in my friends list OR being in a friend request pending.
The idea behind the query would be to show the username of user NOT in the friend_to column and NOT in the sent_to column.
I came up with these two flavors:
select id_user,username
from public.user u
where
username like '%tom%'
and
u.id_user NOT IN (SELECT sent_to FROM friend_request where sent_from = 288)
AND
u.id_user NOT IN (SELECT friend_to FROM friends where friend_of=288);
OR
select id_user,username
from public.user u
left join friend_request fr
on
u.id_user=sent_to AND fr.sent_from=288
left join friends f
on
u.id_user = f.friend_to AND f.friend_of=288
where username like '%tom%' AND sent_to IS NULL and friend_to IS NULL;
Both of these produce the same result. But I would like some advice on which one is a better choice performance and bets practice wise?
Aucun commentaire:
Enregistrer un commentaire