Mysql Query Help for Recommended friends

Question

I have to find friends of friends (not my friends) from an sql table but the problem is that am stuck with excluding users who are already my friends or some blocked friends.

Here is the query

SELECT 
  IF(Friends.User_Id1 IN (1111,2222),
       Friends.User_Id2,
       Friends.User_Id1) AS 'Friend_Id', 
  CONCAT(User_FirstName," ",User_LastName) AS User_FirstName,
  User_ProfilePic 
FROM Users
JOIN Friends ON
  IF(Friends.User_Id1 IN (1111,2222),
       Friends.User_Id2,
       Friends.User_Id1) = Users.User_Id
WHERE 
 (Friends.User_Id2 IN (1111,2222) OR Friends.User_Id1 IN (1111,2222)) AND 
 (Friends.User_Id2 != MY_ID AND Friends.User_Id1 != MY_ID AND Friends.Status = 1)
LIMIT 10;

In the above case, 1111 and 2222 are my friends and I am trying to get all their friends that is fine but what I want is:

users already my friends who are also friends with either 1111 and 2222 and are shown in the list. I don't want them here because they are in another friends list already.
Users I had blocked i.e Friends.Status for MY_ID and friends_friend_id = 3, I am having one in the case too, a user id 3333 is friend of 2222 and I had blocked him already but he is present in the list.

Please guide me if the searching via IN(1111,2222) would lead to some issue in future too because friends count would definitely increase. I have a comma separated list of my friends using group_concat before the above query. All this is in a stored procedure.

I hope I explained the problem clearly.

Mike Ryan · Accepted Answer

The first thing is that you should not implement my first answer. Instead, you should change or constrain your schema. See below for my suggestions.

As I understand your schema, it is:

create table Friends (
   user_Id1 int,
   user_Id2 int,
   status int);

Where anytime there is a friend relationship, one of the id's is in position 1 and 1 is in position 2.

Now, assuming that my id is 1212, my list of friend's ids is:

 select user_Id1 as my_friends_userId
   from Friends f
   where f.user_Id2 = '1212'
      and status = 1
 union
 select user_Id2 as my_friends_userId
   from Friends f
   where f.user_Id1 = '1212'
     and status = 1;

The list of my Friends' Friends' Ids is:

select f1.user_id1 as friends_of_friends
  from Friends f1
  where f1.user_Id2 in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
union
select user_id2 as friends_of_friends
  from Friends f1
  where f1.user_Id1 in (
      select user_Id1 as my_friends_userId
        from Friends f
        where f.user_Id2 = '1212'
          and status = 1
      union
      select user_Id1 as my_friends_userId
        from Friends f
        where f.user_Id1 = '1212'
          and status = 1);

And then to add exclusions for my own friends and friends I've blocked, this becomes:

select f1.user_id1 as friends_of_friends
from Friends f1
where f1.user_Id2 in (select user_Id1 as my_friends_userId  /* sub-query for friends of friends */
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
and f1.user_id1 not in   /* exclusion of my own friends */
(select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1
 )
and f1.user_id1 != '1212'  /* exclusion of myself. */
and f1.user_id1 not in (select user_Id1 as my_friends_userId  /* exlusion of people I've blocked. */
      from Friends f
      where f.user_Id2 = '1212'
        and status = 3
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 3
 )
union  /* Now do it all over again for user_id2 */
select f2.user_id2 as friends_of_friends
from Friends f2
where f2.user_Id1 in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
and f2.user_id2 not in 
(select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1
 )
and f2.user_id2 != '1212'
and f2.user_id2 not in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 3
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 3
 )

where I've marked the first time for each of these conditions. Now, you can see the mess of union's I had to do for this. (Which should probably be union distinct)

You should not be creating the in-clause with a group-concat. Despite the length here, it's faster.

You can ask what the individual pieces do. But again, my advise is DON'T DO THIS. This is why good table design up front makes things a lot easier.

SQL Fiddle for reference and to show results: http://sqlfiddle.com/#!2/e6376/13

EDIT: Just to add about how I would change this schema.

It's unclear if in your app, relationships between friends is a Google one (asymmetric relationships allowed), or a Facebook one (only symmetric relationships allowed).

In both cases, I'd change the schema to be:

create table Friends (
  individual_userId int,
  friend_userId int,
  status int);

In the Google, case, you're done. In the Facebook case, I'd use this structure, but require that for every relationship, two rows go into the table. So if '1212' is Facebook friends w/ '0415', then there are (individual_userid, friend_userId) rows of ('1212', '0415') & ('0415','1212'). Ensuring that this works and is maintained would require stored procedures for insertions/deletions to make sure both rows are added and deleted. (There's no update -- these are unique ids.)

If we're sure that these relationships are maintained and that the friend initiating a relationship is always present in individual_userId, then, my final query becomes:

select f1.friend_userId as friends_of_friends
from Friends f1
where f1.individual_userId in (   /* retrieve my friend list */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 1)
and f1.friend_userId not in (   /* exclusion of my own friends */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 1
 )
and f1.friend_userId not in ( /* exlusion of people I have blocked. */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 3
 )
and f1.friend_userId != '1212'  /* exclusion of myself. */

which is much easier to deal with. You could also rewrite this as a series of joins instead, but I suspect that as a first step, using in and not in clauses like this is easier to read.

Revised sqlfiddle: http://sqlfiddle.com/#!2/92ff2/1

(I'd have to test it with a large data set, but my gut says that the joins will be faster -- but for code like this, I suspect learning/getting the right answer is more important than optimizing initially for speed.)

Mysql Query Help for Recommended friends

Answers (2)

Related Questions