“您可能认识的人” sql查询 [英] "people you may know" sql query

问题描述

我正在开发一个你可能认识的人功能。我有两个表：

I'm working on a "people you may know" feature. I have two tables:

USERS

id

电子邮件

名称

etc

USERS
id
email
name
etc

FRIENDSHIPS

user_id

friend_id

FRIENDSHIPS
user_id
friend_id

对于每个友谊我做了两个记录。说用户7和9成为朋友...我会在友谊表中记录user_id = 7，friend_id = 9，另一个是user_id = 9，friend_id = 7。

For each friendship I make two records. Say users 7 and 9 become friends... I would make a record where user_id=7,friend_id=9 and another where user_id=9, friend_id=7 in the friendships table.

我如何做一个sql查询，建议我可能知道的人基于我的朋友的朋友？我也希望它基于最共同的朋友排序。

How would I make a sql query that suggests people I'm likely to know based on friends of my friends? I also want it ordered based on the most mutual friends.

推荐答案

select u.id, u.email, u.name, u.etc
-- Get all my friends
from Friendships as f1
-- Get their friends
inner join Friendships as f2
    on f1.friend_id = f2.user_id
-- Get their friends User information
inner join Users as u
    on f2.friend_id = u.id
where f1.user_id = @userId

这是我开始的地方。

这篇关于“您可能认识的人” sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！