Jump to content

People You May Know Feature - Need Only Friends Friends


unemployment

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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))

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.