user3202390 user3202390 - 5 months ago 8
SQL Question

More efficient way to find 3rd degree network

I'm trying to develop a feature that with show a user's 3rd degree network (friends, friends of friends, friends of friend's friends). Similar to how Linkedin does it.

I have a solution but it seems inefficient and makes a lot of queries to the database. Is there a known better way than the pseudo-code below to find a user's 3rd degree connections? I'm a php (and programming) novice, so apologies in advance if I've missed something obvious. Thank you.

Query table to find user’s 1st connections.
Push to Array1()

For (x = 0; x < count (Array1); x++){
Query table to find Array1[x]’ s 1st connections and push to Array2();
}

For (y =0; y < count(Array2); y++){
Query table to find Array2[y]’s 1st connections and push to Array3();
}

Array4 = array_unique(Array3);


The 2 tables in the database have this structure:

members
id, username, password, id

connections
connectionId, userID, friendID, confirmed

CSK CSK
Answer

you can query the Array4 by using 3 subqueries with 'in' clause

select friendID from connections where userID  in (
select friendID from connections where userID  in (
select friendID from connections where userID in (
select friendID from connections where userID = 1)))

and don't forget to create index