Improved friend list query: counting mutual friends

Question

I have two tables in my database to maintain user information (users_table) and the other is tracking friends

users_table:

id username avatar 1 max max.jpg 2 jack jack.jpg 

friends_table:

 id u1_id u2_id 1 1 2 2 1 3 

in each user profile I show a list of my friends

here is my request

 select u.id, u.username, u.avatar from friends_table f join users_table u on f.u1_id = u.id || f.u2_id = u.id where u.id <> $profile_id and (f.u1_id = $profile_id || f.u2_id = $profile_id) 

this request selects friends of the profile owner ($ profile_id)

and attach them to the user table to get each friend’s username and avatar

now I want to calculate mutual friends between each friend and the owner of the profile, is it possible in one request or do I need to make a long and probably slow request similar to this for each friend based (this is just an example and it may have some syntax error) :

  foreach ( $friends_list_query_resul as $qr ){ $friend_id = $qr['id']; $mutual_count = mysql_query ( "select count(*) from friends_table where ($u1_id = $friend_id || $u2_id = $friend_id ) && ( $u1_id IN ( SELECT `u1_id`,`u2_id` from friends_table where ($u1_id = $profile_id || $u2_id = $profile_id ) ) || $u2_id IN ( SELECT `u1_id`,`u2_id` from friends_table where ($u1_id = $profile_id || $u2_id = $profile_id ) ) ") } 

Answer 1

I decided to add two rows for each friends relationship to the table.

 id u1_id u2_id 1 10 20 2 20 10 

It simplifies and speeds up the process.

Answer 2

Your first request may also be written as:

 select distinct u.id, u.username, u.avatar from users_table u where u.id in (select case when u1_id=$profile_id then u2_id else u1_id end from friends_table f where case when u1_id=$profile_id then u1_id else u2_id end =$profile_id); 

A friend request can be written as one request in the same way:

 select u.id, (select count(f.id) from friends f where case when f.u1_id=u.id then u2_id else u1_id end in (select distinct case when u1_id=$profile_id then u2_id else u1_id end from friends where case when u1_id=$profile_id then u1_id else u2_id end =$profile_id) and u1_id=u.id or u2_id=u.id and (u1_id <> $profile_id and u2_id <> $profile_id)) as mutual_frnds from user u where u.id <> $profile_id; 

but you might want to run a performance test of any of them before using.

Answer 3

All you need is one request:

 select id, username, avatar, -- ... ( select count(*) from friends_table f1 inner join friends_table f2 on f1.u2_id = f2.u1_id and f2.u2_id = f1.u1_id where f1.u1_id = users_table.id ) as mutual_friend_count from users_table 

Subquery Value:

Give me a count of “each other” relationships in which the user participates, so that the goal of the first relationship of the friend is the source of the second relationship of the friend, and the goal of the second relationship of friends is the source of the first.

Answer 4

Firstly, I don’t understand why the query is so complicated to retrieve the user's friends ... It just needs to be reached by this query:

 select u.id, u.username, u.avatar from friends_table f left join users_table u on f.u2_id = u.id where f.u1_id = $profile_id 

Explain that the registered user is the one whose identifier matches the identifier f.u1_id . Therefore, we select only friends whose identifiers are in f.u2_id .

Then, to count the common friends of my friends, we can use this query:

 select count(*) as mutual_count, f.u1_id as mutual_friend_id from friends_table f where f.u1_id IN (select f.u2_id from friends_table where f.u1_id = {$profile_id}) 

where $ profile_id is the ID of the registered user ...

Is it correct?