Question

我有一个用户表

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

和朋友桌桌

id , u1 , u2 , status , reciver

For increasing the speed on reading the friends i ve decided to add two row to the table for each friendship so it goes like this

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

现在我想让每个用户朋友都像脸书一样数清他们之间的共同朋友

以下是我的朋友列表查询:

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

        }

so i check the owner of profile id ($profile_id) against friends.u1 and join the friends.u2(founded friend) to the users table to get his username and id

问题在于如何计算共同的朋友

I join the friends table with it self on each founded friend

报纸上看起来不错,但结果怪怪怪的

Like if the users has 3 friends and there is no mutual friends it return only one of the 3 friends !

如果有共同的朋友,它会显示所有的朋友, 但大多数情况下,共同的朋友号码是错的

存在以声明形式分组的问题

Answer 1

只有两个朋友桌你做不了, 你需要第三个:

Friends1: select friends of user $profile_id (as you have)
Friends2: select the friends of the friend of $profile_id (as you have)
Friends3: work out the mutual friends between the friends of the the friend of $profile_id and the friends of $profile_id

我还没有测试过以下数据, 它对我的样本数据有效, 只是对更大的样本进行双倍检查:

        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

友情链接