php - PostgreSQL根据共同 friend 的数量选择您可能认识的人

我正在建立一个社交网络，我希望我的会员能够很容易地找到新朋友。
就像在Facebook上一样，我想向他们推荐一些他们认识的人，他们的共同朋友的数量。
我的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中找到的查询，用于查找两个配置文件（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)

现在我试图转换这个查询，让它找到与我最共同的朋友，但谁不是我的朋友的个人资料。但是如果没有成功。。。我也在这个网站上研究了很多例子，但大多数都是在友情表中使用双记录。就像24是26的朋友，有两个记录：（24，26）和（26，24）。这使他们更容易加入和找到共同的朋友，但这不是我想建立我的数据库。
如果有人能帮助我开始这个问题，我将非常感谢。

最佳答案

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;

关于php - PostgreSQL根据共同 friend 的数量选择您可能认识的人，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19789545/