php - 改进好友列表查询 : counting the mutual friends

Question

我的数据库中有两个表，一个是保存用户信息(users_table)另一个跟踪 friend

用户表:

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

friend 表:

id    u1_id      u2_id  
1         1          2  
2         1          3  

在每个用户个人资料中，我都会显示他/她的好友列表

这是我的问题

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)

此查询选择个人资料所有者的 friend ($profile_id)

并将他们加入到用户表中以获取每个 friend 的用户名和头像

现在我想计算每个 friend 和个人资料所有者之间的共同 friend 是否可以在一个查询中完成，或者我应该为每个已建立的 friend 做一些像这样的长而可能很慢的查询(这只是一个例子，它可能有一些语法错误):

       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

您的第一个查询也可以写成:

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

可以用类似的方式将共同好友查询写成单个查询:

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;

但您可能希望在使用前对它们中的任何一个进行性能测试。