php - 按查询加入/分组返回奇怪的结果

Question

我有一个用户表

 id , username
 22 , max
 33 , jack
 44 , joe

和 friend 表

id , u1 , u2 , status , reciver

为了提高阅读 friend 的速度，我决定为每个友谊在表格中添加两行所以事情是这样的

id , u1 , u2 , status , reciver

 1 , 22 , 33 ,  1     ,  22
 2 , 33 , 22 ,  1     ,  22


 3 , 22 , 44 ,  1     ,  22
 4 , 44 , 22 ,  1     ,  22

 5 , 22 , 55 ,  1     ,  22
 6 , 55 , 22 ,  1     ,  22

现在我想获取每个用户的好友，并计算这两个用户之间的共同好友，例如 facebook

这是我的好友列表查询:

         $profile_id = $current_user->id;

         $res = $db->query("
            select 
            u.id as firend_id, 
            u.username  as firend_name,
            f.status    as firendship_status,
            COUNT(f3.u2)  as mutual  

            from friends f      
              JOIN users  u on f.u2 = u.id
              LEFT JOIN friends f2 on f.u2 = f2.u1 and f2.u2 != $profile_id
              LEFT JOIN friends f3 on f3.u1 = $profile_id AND f3.u2=f2.u2     
            WHERE  f.u1 = $profile_id
            group by f2.1
           ");

        echo 'friends list : '
        foreach ($res->result() as $r ){

          echo $r->firend_name;
          echo $r->mutual.' friends';

        }

所以我根据friends.u1检查个人资料ID($profile_id)的所有者并将 Friends.u2(Founded Friends) 加入到 users 表中以获取他的用户名和 id

问题在于计算共同的 friend

我自己加入好友表关于每个建立的 friend

表面上看起来没问题，但它返回了一些奇怪的结果

例如，如果用户有 3 个 friend ，但没有共同的 friend ，则仅返回 3 个 friend 中的一个!

如果有共同的 friend ，它会显示所有 friend ，但大多数情况下共同的 friend 号码是错误的

group by语句有问题

Answer 1

你不能只用 2 个 friend 表来做到这一点，你需要第三个:

• Friends1:选择用户 $profile_id 的好友(如您所知)
• Friends2:选择 $profile_id 的 friend 的 friend (如您所知)
• Friends3:找出好友之间的共同好友$profile_id 的 friend 和 $profile_id 的 friend

我尚未测试以下内容，它适用于我的示例数据 - 只需仔细检查更大的示例即可:

        select  
        u.id as firend_id,  
        u.username  as firend_name, 
        f.status    as firendship_status, 
        COUNT(f3.u2)  as mutual 

        from friends f       
          JOIN users  u on f.u2 = u.id 
          LEFT JOIN friends f2 on f.u2 = f2.u1 and f2.u2 != $profile_id 
          LEFT JOIN friends f3 on f3.u1 = $profile_id AND f3.u2=f2.u2

        WHERE  f.u1 = $profile_id 
        group by f.u2