php - Postgresql 选择您可能认识的人，按共同 friend 的数量排序

Question

我正在构建一个社交网络，我希望我的成员能够轻松找到新 friend 。就像在 Facebook 中一样，我想根据他们拥有的共同 friend 的数量向他们推荐一些他们可能认识的人。

我的 PostgreSQL 数据库结构如下:

> PROFILES_FRIENDSHIPS
> ----------------------
> - fri_profile_from // int, Person who sent the friendship request
> - fri_profile_to // int, Person who received the friendship request
> - fri_accepted // tinyint, has the person accepted the friendship request?

这是我在 PostgreSQL 中找到的查询，用于查找 2 个个人资料(ID 为 24 的个人资料和 ID 为 26 的个人资料)之间的共同好友数量:

SELECT COUNT(*)
FROM profiles AS p
INNER JOIN (
        SELECT (
        CASE    WHEN ( 26 = f.fri_profile_from ) THEN f.fri_profile_to 
                    ELSE f.fri_profile_from END) AS fri_profile_from 
        FROM profiles_friendships AS f 
        WHERE 1 = 1
        AND (f.fri_profile_to = 26 OR f.fri_profile_from = 26) 
        AND fri_accepted = 1) 
AS f1 
ON (f1.fri_profile_from = p.pro_id) 
INNER JOIN (
        SELECT (
        CASE    WHEN ( 24 = f.fri_profile_from ) THEN f.fri_profile_to 
                    ELSE f.fri_profile_from END) AS fri_profile_from 
        FROM profiles_friendships AS f 
        WHERE 1 = 1
        AND (f.fri_profile_to = 24 OR f.fri_profile_from = 24) 
        AND fri_accepted = 1) 
AS f2 
ON (f2.fri_profile_from = p.pro_id)

现在我一直在尝试转换此查询以使其找到与我最共同的 friend 但不是我 friend 的个人资料。但没有成功......我也在这个网站上研究了很多例子，但大多数都是在友谊表中使用双重记录。就像 24 和 26 是 friend 一样，有 2 条记录:(24, 26) 和 (26, 24)。这让他们更容易加入并找到共同的 friend ，但这不是我想要建立数据库的方式。

如果有人可以帮助我开始这个查询，我将不胜感激。

Answer 1

WITH friends AS(
  SELECT p.pro_id, CASE WHEN f.fri_profile_from = p.pro_id THEN f.fri_profile_to 
                    ELSE f.fri_profile_from END AS friend_id
  FROM profiles
)
SELECT f2.pro_id, count(*) as friend_count
FROM friends AS f1
  JOIN friends AS f2
    ON f1.friend_id=f2.friend_id
       AND f1.pro_id != f2.pro_id
       AND f1.pro_id != f2.friend_id
WHERE f1.pro_id = :user_id
GROUP BY f2.pro_id
ORDER BY friend_count;