improving a friends list query : counting the mutual friends

Question

i have two tables in my database one is to keep users info (users_table ) and the other one keeps track of the 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 every user profile i show his/her friends list

here is my query

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 query selects friends of the profile owner ($profile_id)

and join them with the user table to get each friend username and avatar

now i want to count the mutual friends between each friend and the profile owner is it possible to this in one query or should i do some long and probably slow query like this for each founded friend( it's just a example and it might 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 ) )


       ")
        }

my advice is to extract the list of friend of each ID, and with php make the match between the common friends, MSql is slower than others programming languages when it's about manipulate data. — jcho360, May 22 '12 at 14:32
@jcho360 Bad advice. Databases tend to scale much better than this kind of in-memory approach ever could. Databases are not "slower than other programming languages when it's about manipulate data", in fact they are typically much faster, *if* used correctly. — Branko Dimitrijevic, May 23 '12 at 13:08
@BrankoDimitrijevic after google for a while, you are right, it looks like SQL it's faster than programming languages, but that depend a lot about the structure of the DB, index, data types, PK, etc. can make a huge difference. I don't know why I thought that. THANKS. http://stackoverflow.com/questions/655226/php-sql-order-by-or-sortarray — jcho360, May 23 '12 at 13:27

Jeshurun · Answer 1 · 2012-05-20T09:01:54.330

Your first query could 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);

The mutual friends query can be written as a single query in similar fashion:

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 performance test either of them before using.

i was hoping to do this without using sub querys . i'm going to check the performance and see . thanx for replay — max, May 20 '12 at 16:36

Walter Tross · Answer 2 · 2012-05-24T08:07:57.523

1

All you need is one query:

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

The meaning of the subquery is:

Give me the count of the "friend of a friend" relations the user participates in, such that the target of the first friend relation is the source of the second friend relation, and the target of the second friend relation is the source of the first one.

edited May 24 '12 at 08:07

answered May 22 '12 at 22:05

Walter Tross

12,237
2
40
64

1

thanx but i think for this to work i have to change the way that data is being stored in database . right now each user can either be in u1_id or u2_id for each relation so i have to check both and i can't just say select u1_id . – max May 23 '12 at 22:20
If you have to count mutual frienship relations, this means that the friendship relations that you are storing don't commute, i.e., if u1 is a friend of u2, this does not imply that u2 is a friend of u1. If they don't commute, the pair (u1,u2) is not the same thing as the pair (u2,u1). The way I see it, one column in your friends_table must be the "source" of the relation, and the other must be the "target". What am I missing? – Walter Tross May 24 '12 at 06:49
the thing is friendship does commute . if i ask you to become my friend and you accept , you are my friend and i'm yours . at least this is how it works in my code – max May 24 '12 at 14:29
ok, now I think I understand: your question confused me (and others) because you used the word "mutual" instead of "common". I guess this is what you meant. I will have to completely revise my answer, if so. – Walter Tross May 24 '12 at 15:51

score 0 · Answer 3 · answered May 17 '12 at 10:49

0

First, I do not understand why so complicated query to retrieve the user's friends... It should be simply achieved 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: the logged in user is the one whose id is the same as f.u1_id. Therefore we only select friends whose ids are in f.u2_id.

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

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 logged in user...

Is this correct?

answered May 17 '12 at 10:49

shadyyx

15,825
6
60
95

actually, about the first query, the `$profile_id` could also be in the `f.u2_id` column – pomeh May 17 '12 at 10:55
Yes, of course, cos it's MANY TO MANY relation, but when it is in `f.u2_id` it means that I AM THE FRIEND OF somebody whose ID is in `f.u1_id` - this relation is not a matter to us (or shouldn't be) when picking up just my friends... Not the users whose I am the friend of... – shadyyx May 17 '12 at 11:05
that's not sure ! I think it depends on the use case. @max what do you think ? – pomeh May 17 '12 at 11:10
thanx for answer , about the first query as pomeh pointed out each user in the friends table can be in the u1_id or u2_id based on who has requested the friendship , so i cant just do "$query =...where f.u1_id = $profile_id" cuz i have to check both columns . – max May 17 '12 at 11:16

score 0 · Accepted Answer · answered May 24 '12 at 14:48

0

i've decided to add two rows for each friend relation to the table .

id    u1_id      u2_id  
1         10         20  
2         20         10

it makes the process easier and faster .

answered May 24 '12 at 14:48

max

3,614
9
59
107

1

wow, didn't know that it's legal to answer one's own questions... Anyhow, make sure to save the information of who requested the friendship, in case you need it – Walter Tross May 24 '12 at 16:06

improving a friends list query : counting the mutual friends

4 Answers4