StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do i create a MySQL Query for 'People you may know' (friends of friends)?
text
Body
copied!<p>I have the following tables:</p> <pre><code>members(id, name, surname, username, password) friends(uid, friend_uid, confirmed_uid, confirmed_friend_uid) </code></pre> <p>in the friends table the <code>uid</code> and <code>friend_uid</code> relate to <code>members.id</code>, where <code>uid</code> is always lower then <code>friend_uid</code>. <code>friends.confirmed</code> is true when <code>friends.uid</code> has agreed to be friends and <code>friends.friend_confirmed</code> is true when <code>friends.friend_uid</code> has confirmed the friendship.</p> <p>User A is only friends with B and C, they have both confirmed the friendship. I want a query that will show A a list of B and C's (all A's confirmed friends) confirmed friends that are not already friends with A. Making sure that that if B and C have common friends they are top of the list.</p> <p>As my MySQL is very rusty I am currently doing this with multiple queries, but this must be very inefficient and I need to create a query to it all for me before it starts effecting the performance of my web site.</p> <p>I have started reading MySQL documentation and I will update this if I get anywhere, but if someone can help me speed up the process, I will greatly appreciate it.</p> <p>Here is how i am doing it with a bit pseudo code:</p> <pre><code>member_id_for_A = 1; friends_of_A = mysql_query(" SELECT m.*, f.* FROM friends f inner join members m on m.id = f.uid WHERE f.friend_uid = " + member_id_for_A + " AND confirmed_uid=1 AND confirmed_friend_uid=1 UNION SELECT m.*, f.* FROM friends f inner join members m on m.id = f.friend_uid WHERE f.uid = " + member_id_for_A + " AND confirmed_uid=1 AND confirmed_friend_uid=1"); foreach(row : friends_of_A) { friend_id = get_friened_id(row, member_id_for_A); friends_of_friend = get_friends(friend_id); // Performs a query like above friends_of_friend result = remove_matches(friends_of_A, friends_of_friend); // Remove common friends // Do something with results and move onto the next friend } </code></pre> <p>I know this is very bad code and not efficient, but it was a quick hacky way I could get the functionality I needed until I had the time to learn more MySQL. Hopefully it gives a clear picture of what I am aiming for with one single query.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload