MySQL query for mutual friends [duplicate]
Well, the only query that might work up to now is Simon's... but that's real overkill - such a complex nasty query (2 subqueries with 2 unions!) for so simple thing that you need to place a bounty? :-) And if you have like 1000+ users the query will be slow as hell - remeber, it's quadratic, and due to unions in subqueries, hardly any index would be used!
I'd suggest to re-think the design again and allow for 2 duplicate rows for a friendship:
id Person1 Person2 status1 1 2 friend2 2 1 friend3 1 3 friend4 3 1 friend
You might think that's inefficient but following simplification will allow to rewrite the query to simple joins:
select f1.Person2 as common_friendfrom friends as f1 join friends as f2 using (Person2)where f1.Person1 = '$id1' and f2.Person1 = '$id2' and f1.status = 'friend' and f2.status = 'friend'
which will be fast as hell! (Don't forget to add indices for Person1,2.) I've advised a similar simplification (rewriting subqueries to joins) in other very nasty data structure and it has speeded up the query from eternity to blitz-instant!
So what might have been looking as a big overhead (2 rows for one friendship) is actually a big optimization :-)
Also, it will make queries like "find all friends of X" much more easier. And no more bounties will need to be spent :-)
This query works with the assumption that there is no self-friending and no duplicates in friendship table, if these conditions are not meet little tweaks needed to make it work.
SELECT fid FROM ( --FIRST PERSON (X) FRIENDLIST SELECT (CASE WHEN Person1 = X THEN Person2 ELSE Person1 END) AS fid FROM Friendships WHERE (Person1 = X OR Person2 = X) AND status = "friend" UNION ALL --DO NOT REMOVE DUPLICATES WITH ALL JOIN --SECOND PERSON (Y) FRIENDLIST SELECT (CASE WHEN Person1 = Y THEN Person2 ELSE Person1 END) AS fid FROM Friendships WHERE (Person1 = Y OR Person2 = Y) AND status = "friend") FLISTGROUP BY fidHAVING COUNT(*) = 2
One more answer.
select (case when f1.person1 = 1 then f1.person2 else f1.person1 end) as fidfrom friends f1where f1.person1 = 1 or f1.person2 = 1and f1.status = 'friend'intersectselect (case when f1.person1 = 3 then f1.person2 else f1.person1 end) as fidfrom friends f1where f1.person1 = 3 or f1.person2 = 3and f1.status = 'friend'