Friends of a user + mutual friend test with another user
|Added at||2016-12-31 00:12|
I have this table that I use (but not only) for storing friends in a database :
where 'status' can be -1,0 or 1. Here, we will consider only cases where status are '0' (pending for user_1) or '1' (approved by user_2). I have the following query to look for pending/approved friends for a given $user :
The goal here is to modify the query to also tell if a given $user2 is a common (approved) friend of $user1 and each (approved) friend of $user1.
After some researches, I figured out that the left join would do the trick, by setting another field to either NULL (if no mutual) or $user2. I would want to do it efficiently. I tried several shots, but no success around it.
Thanks by advance for your help
EDIT : For example, let's say we have the following entries :
I want to list the friends of 'a' and for each friend f of 'a', verify if 'b' is a common friend of f and 'a'. Also, f =/= b for the mutual test. The result of such a query would be :
Let me know if you need more clarification