People You May Know Feature - Need Only Friends Friends

[unemployment]

I'm in the process of making a people you may know feature.  To do this I am trying to grab my friends friends that I am not friends with.  I need the SQL to say... If my friends friends ID matches any of my friends Id then exclude it from the select.  I'm not sure how to do this or at least how to do it efficiently.

 

Query:

       

         SELECT
	users.id,
	users.firstname,
	users.lastname,
	myfriend.id,
	myfriend.firstname,
	myfriend.lastname,
	theirfriend.id,
	theirfriend.firstname,
	theirfriend.lastname
FROM users
INNER JOIN partners ON partners.user_id = users.id AND partners.approved = 1
INNER JOIN users myfriend ON myfriend.id = partners.friend_id
INNER JOIN partners partners2 ON partners2.user_id = myfriend.id
INNER JOIN users theirfriend ON theirfriend.id = partners2.friend_id
WHERE users.id = 1

 

Current Results

 

My ID My Name My Friends Id My Friends Name My Friends Friends ID My Friends Friends Name

1           Jason           2               Chelsea                       4                               Davey

1           Jason           2               Chelsea                       6                               Jim

1           Jason           2               Chelsea                       12                               Peter

1           Jason           2               Chelsea                       16                               Cameron

1           Jason           2               Chelsea                       38                               Felicia

1           Jason           4               Davey                              5                               Adam

1           Jason           4               Davey                               14                               Jeffrey

1           Jason           5               Adam                               6                               Jim

1           Jason           5               Adam                               14                               Jeffrey

1           Jason           5               Adam                               17                               Dan

1           Jason         10               Michael                       5                               Adam

1           Jason         13               Jacek                               4                               Davey

1           Jason         20               Victor                               1                               Jason

 

[Muddy_Funster]

could you post your actual tables, that query makes no sense to me.

[unemployment]

could you post your actual tables, that query makes no sense to me.

 

users table

id firstname lastname

 

partners table

partner_id user_id friend_id approved

 

Basically I need to create aliases to be able to get my friends friends.  I just don't know how to restrict the results by removing people i'm already friends with.

[The Little Guy]

I was able to come up with something like this (untested):

 

create temporary table myFriends select friend_id from partners where user_id = 123; -- List of your friends

create temporary table myFriendsFriends select friend_id from myFriends left join partners on (myFriends.friend_id = partners.friend_id); -- List of your friends friends

select mff.friend_id mff_id, partners.friend_id from myFriendsFriends mff left join partners on(mff.friend_id = partners.friend_id) having friend_id null; -- List of your friends friends who are not your friends

[Muddy_Funster]

not sure how deep you can nest IN's, but this should work (in theory):

SELECT firstname, lastname 
FROM users 
LEFT JOIN partners ON users.id = patrners.user_id 
WHERE (users.id IN (SELECT frend_id FROM partners WHERE user_id IN (SELECT friend_id FROM partners where user_id = 1)))
AND 
(users.id NOT IN (SELECT friend_id FROM partners WHERE user_id = 1))

[unemployment]

not sure how deep you can nest IN's, but this should work (in theory):

SELECT firstname, lastname 
FROM users 
LEFT JOIN partners ON users.id = patrners.user_id 
WHERE (users.id IN (SELECT frend_id FROM partners WHERE user_id IN (SELECT friend_id FROM partners where user_id = 1)))
AND 
(users.id NOT IN (SELECT friend_id FROM partners WHERE user_id = 1))

 

This unfortunately doesn't work.  I just get an awkward list of my username over and over with a list of a few other peoples which surely this can't be correct.  I also don't think the NOT IN in the where clause makes sense because there is another side to this.  Your id can also be the friend_id.  Your id is the user_id in the partners table if you sent the request.  If you are receiving the request then your id is the friend_id.  Any other thoughts?

[fenway]

Perhaps you need NOT EXISTS.

[unemployment]

Perhaps you need NOT EXISTS.

 

I'll look into it.  I also tried a few other combinations but all I got were my friends and not my friends friends.

[fenway]

To get your friend's friends, you'll need to join twice -- do you have this query working?

[unemployment]

To get your friend's friends, you'll need to join twice -- do you have this query working?

 

Well the first query I posted will grab my friends friends.  I just need to compare those against my friends and exclude my friends.

[mikosiko]

here is the mock-up of a possible solution (tested)... replace/add your table field names and aliases

SELECT a.uid, x.uname, a.fid, y.uname, b.fid, z.uname
  FROM partners a
  JOIN partners b ON a.fid = b.uid  AND b.fid NOT IN ( SELECT m.fid
                                                         FROM partners m
                                                              JOIN partners n ON m.fid = n.uid
                                                         WHERE m.uid =1)
  JOIN users x ON a.uid = x.uid
  JOIN users y ON a.fid = y.uid
  JOIN users z ON b.fid = z.uid
WHERE a.uid =1;

Note:

uid = user_id

uname = users.firstname or users.lastname // add fields as you need

fid = friend_id

 

The output of that query (using your data as example) is this

1, 'Jason', 2, 'Chelsea',   6, 'Jim'
1, 'Jason', 2, 'Chelsea', 12, 'Peter'
1, 'Jason', 2, 'Chelsea', 12, 'Cameron'
1, 'Jason', 2, 'Chelsea', 38, 'Felicia'
1, 'Jason', 4, 'Davey',   14, 'Jeffrey'
1, 'Jason', 5, 'Adam',     6, 'Jim'
1, 'Jason', 5, 'Adam',   14, 'Jeffrey'
1, 'Jason', 5, 'Adam',   17, 'Dan'
1, 'Jason', 20, 'Victor',   1, 'Jason'

 

the last record (yourself) could be eliminated easily adding the condition to the last where

[unemployment]

I'm not sure I follow this.  You have uname multiple times in the select.  I'll give it a shot though.

[mikosiko]

I'm not sure I follow this.  You have uname multiple times in the select.  I'll give it a shot though.

 

with different aliases  x.uname, y.uname, z.uname;  matching the aliases used for JOINing the table users...

 

in your case you should have x.firstname, x.lastname, y.firstname, y.lastname, z.firstname, z.lastname

[unemployment]

here is the mock-up of a possible solution (tested)... replace/add your table field names and aliases

SELECT a.uid, x.uname, a.fid, y.uname, b.fid, z.uname
  FROM partners a
  JOIN partners b ON a.fid = b.uid  AND b.fid NOT IN ( SELECT m.fid
                                                         FROM partners m
                                                              JOIN partners n ON m.fid = n.uid
                                                         WHERE m.uid =1)
  JOIN users x ON a.uid = x.uid
  JOIN users y ON a.fid = y.uid
  JOIN users z ON b.fid = z.uid
WHERE a.uid =1;

Note:

uid = user_id

uname = users.firstname or users.lastname // add fields as you need

fid = friend_id

 

The output of that query (using your data as example) is this

1, 'Jason', 2, 'Chelsea',   6, 'Jim'
1, 'Jason', 2, 'Chelsea', 12, 'Peter'
1, 'Jason', 2, 'Chelsea', 12, 'Cameron'
1, 'Jason', 2, 'Chelsea', 38, 'Felicia'
1, 'Jason', 4, 'Davey',   14, 'Jeffrey'
1, 'Jason', 5, 'Adam',     6, 'Jim'
1, 'Jason', 5, 'Adam',   14, 'Jeffrey'
1, 'Jason', 5, 'Adam',   17, 'Dan'
1, 'Jason', 20, 'Victor',   1, 'Jason'

 

the last record (yourself) could be eliminated easily adding the condition to the last where

 

So... I am much closer now with the code you provided.  I put in the appropriate column names below.  The problem with your output above is that Jeffrey shows up TWICE in the last column.  I need the column to exclude duplicates.  Then I also need to replicate this code for if my id was the friend_id and create a union for both queries.  One more question:  Why am I included in the last row?  How do I remove that in the WHERE clause? Hopefully we can sort this out soon. Thanks.

 

SELECT a.user_id, x.username, a.friend_id, y.username, b.friend_id, z.username
  FROM partners a
  JOIN partners b ON a.friend_id = b.user_id  AND b.friend_id NOT IN ( SELECT m.friend_id
                                                         FROM partners m
                                                              JOIN partners n ON m.friend_id = n.user_id
                                                         WHERE m.user_id = 1)
  JOIN users x ON a.user_id = x.id
  JOIN users y ON a.friend_id = y.id
  JOIN users z ON b.friend_id = z.id
WHERE a.user_id =1;