mysql - SQL:建议分离度为 1 的 friend ，其中我的 friend 有超过 2 个共同 friend

Question

当我的最终用户的 friend 有多个共同好友时，我在编写向其推荐 friend 的查询时遇到问题。目前正在使用的模式远非最佳，但我的老板坚持不允许我更改表结构，尽管我已经告诉他为 friend 关系提供 2 列比提供 1 列快得多

目前，我们对每段友谊都有一对值(value)观:

friendID  |  Entity_ID1  |  Entity_Id2
   1              2             3
   2              1             4
   3              2             5

我知道，对该列进行逆操作将使我的查询变得更加简单。到目前为止，我已经设计了以下查询来尝试为用户查找推荐的 friend :

  SELECT DISTINCT Entity_Id, Fb_Id, First_Name, Last_Name, Profile_Pic_Url, Last_CheckIn_Place, Category
  FROM entity
  JOIN friends F1
  ON entity.Entity_Id = F1.Entity_Id2 OR entity.Entity_Id = F1.Entity_Id1
  /* Friends of Friends */
  WHERE F1.Entity_Id2 IN
  (
    SELECT Entity_Id1
      FROM friends F
     WHERE F.Entity_Id2 = :userId
       AND F.Category != 4

     UNION

     SELECT Entity_Id2
      FROM friends F
     WHERE F.Entity_Id1 = :userId
       AND F.Category != 4
  )
  /* Exclude my friends */
  AND F1.Entity_Id1 NOT IN
  (
    SELECT Entity_Id1
      FROM friends F
     WHERE F.Entity_Id2 = :userId
       AND F.Category != 4

     UNION

     SELECT Entity_Id2
      FROM friends F
     WHERE F.Entity_Id1 = :userId
       AND F.Category != 4
  )
  /* Exclude self */
  AND F1.Entity_Id1 != :userId
  GROUP BY Entity_Id

  /* Perform again for userId 2 */
  UNION

  SELECT DISTINCT Entity_Id, Fb_Id, First_Name, Last_Name, Profile_Pic_Url, Last_CheckIn_Place, Category
  FROM entity
  JOIN friends F2
  ON entity.Entity_Id = F2.Entity_Id2 OR entity.Entity_Id = F2.Entity_Id1
  WHERE F2.Entity_Id1 IN
  (
    SELECT Entity_Id1
      FROM friends F
     WHERE F.Entity_Id2 = :userId
       AND F.Category != 4

     UNION

     SELECT Entity_Id2
      FROM friends F
     WHERE F.Entity_Id1 = :userId
       AND F.Category != 4
  )
  /* Exclude my friends */
  AND F2.Entity_Id2 NOT IN
  (
    SELECT Entity_Id1
      FROM friends F
     WHERE F.Entity_Id2 = :userId
       AND F.Category != 4

     UNION

     SELECT Entity_Id2
      FROM friends F
     WHERE F.Entity_Id1 = :userId
       AND F.Category != 4
  )
  AND F2.Entity_Id2 != :userId
  GROUP BY Entity_Id

这种方法可行，但是它返回我已经是 friend 的用户，这不是我想要的，我想通过为我的 friend 使用 NOT IN() 子句，然后使用 UNION 合并结果，这将把我的 friend 们赶出去，但显然事实并非如此。

我在这里做错了什么，有什么方法可以在不修改架构的情况下缩短这个查询，现在它看起来太长而且相当难以管理。

Answer 1

缺少互惠关系确实会让这变得更加困难。它需要检查关系的两个方向。您似乎正在寻求一种使用union来重建双方关系的策略。

或者，您可以使用exists 和子查询。以下版本使用 exists 查找不是 friend 且至少有两个共同 friend 的实体:

select e.*
from entities e
where e.entity_id <> :user_id and
      not exists (select 1
                  from friends f
                  where f.category <> 4 and
                        :user_id in (f.entity_id1, f.entity_id2) and
                        e.entity_id in (f.entity_id1, f.entity_id2)
                 ) and
      (select count(*)
       from friends f1 join
            friends f2
            on f1.entity_id1 = f2.entity_id1 or
               f1.entity_id1 = f2.entity_id2 or
               f1.entity_id2 = f2.entity_id1 or
               f1.entity_id1 = f2.entity_id2
       where :user_id in (f1.entity_id1, f1.entity_id2, f2.entity_id1, f2.entity_id2) and
             e.entity_id in (f1.entity_id1, f1.entity_id2, f2.entity_id1, f2.entity_id2)
      ) >= 2

希望您没有太多数据。此版本和您尝试的版本都不会在处理大量数据时具有良好的性能。