我正在建立一个社交网络,我希望我的会员能够轻松地找到新朋友。就像在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中发现的查询,用于查找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)
现在我一直在尝试转换这个查询,让它找到我最共同的朋友的个人资料,但他们不是我的朋友。但没有成功。。。我也在这个网站上研究了很多例子,但大多数都是在友谊表中使用双重记录。就像如果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;
步骤1让所有不是朋友的
Select *
From profiles
where (from/to_friendship is not myID)
步骤2包括一个列有#共同的朋友和订单通过它
select *,
(select count(*) from [mutual friends query]) as NrOfMutualFriends)
From profiles
where (from/to_friendship is not myID)
Order by NrOfMutualFriends
编辑:共同好友查询:
步骤1选择我的所有朋友和他的所有朋友
select if(from = myId, to, from) as myfriendids
from PROFILES_FRIENDSHIPS where from = myid or to = myid
select if(from = hisId, to, from) as hisfriendids
from PROFILES_FRIENDSHIPS where from = hisId or to = hisId
步骤2将这些查询合并为1个
select count(*)
from
( select if(from = myId, to, from) as myfriendids
from PROFILES_FRIENDSHIPS where from = myid or to = myid) myfriends
inner join
( select if(from = hisId, to, from) as hisfriendids
from PROFILES_FRIENDSHIPS where from = hisId or to = hisId) hisfriends
on myfriendsids = hisfriendsids
您可以轻松创建双记录格式的内联视图:
with cte_friends(user_id, friend_id) as (
select
fri_profile_from, fri_profile_to
from PROFILES_FRIENDSHIPS
where fri_accepted = 1
union all -- or union if there could be duplicates
select
fri_profile_to, fri_profile_from
from PROFILES_FRIENDSHIPS
where fri_accepted = 1
)
select
f2.friend_id, count(distinct f2.user_id)
from cte_friends as f1
inner join cte_friends as f2 on f2.user_id = f1.friend_id
left outer join cte_friends as f3 on f3.user_id = f2.friend_id and f3.friend_id = f1.user_id
where
f1.user_id = 1 and f3.user_id is null and
f2.friend_id != 1
group by f2.friend_id
order by 2 desc
sql-fiddle演示