SQL selecting people you may know
The question you're asking appears subjective and is likely to be closed.
当我填写 标题字段时，看到上述可怕的警告时，我并不感到惊讶
。
我读了几乎所有谈论的话题，friends of friends或者mutual friends不确定我是否找到了我想做的正确解决方案。
对不起，我不擅长英语或SQL。
在不擅长两种语言的情况下，如何找到正确的答案？
我决定要问。我不会为down-votes或任何duplication warnings失望。
当我想要答案时，我将尽可能真诚地写下来，以帮助您解决其他任何类似的问题。
I have a table for friend relations.
FRIEND (TABLE)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
1 3 // 1 knows 3
2 3 // 2 knows 3
2 4 // 2 knows 4
2 5 // 2 knows 5 // updated
3 5 // 3 knows 5 // updated
1 100
1 200
1 300
100 400
200 400
300 400
两者composite primary keys也是PLAYER表中的外键。
我问并从这样的好人那里得到答案，因为“人们彼此认识”。
表中的熟人的SQL视图。
我有这样的看法。
ACQUAINTANCE (VIEW)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
您可能会注意到，这种关系的业务逻辑具有以下两个
目的。
一个玩家可以说他或她认识其他人。
当两个人都说彼此认识时，可以说是相识。
而且，现在，我想知道有什么好的方法可以
选择其他PLAYER_ID
使用给定的PLAYER（PLAYER_ID）（例如1）
每个人都是“给定玩家直接朋友的朋友”之一
每个都不是玩家本人（不包括1-> 2-> 1）
每个都不是玩家的直接朋友（不包括1-> 2-> 3 x 1-> 3中的3）
如果可能，请按共同朋友的数量订购。
我认为Justin Niessner在“您可能认识的人” sql
查询中的答案是我必须遵循的最接近的路径。
提前致谢。
如果此主题确实重复且不必要，我将关闭线程。
更新 - - - - - - - - - - - - - - - - - - - - - - - - - -------------
对于Rapha毛l Althaus的评论whose name is same with my future daughter（是男孩的名字吗？），
3 is a candidate for friends of friends of 1 because
1 knows 2
2 knows 3
but excluded because
1 already knows 3
Basically I want to serve for the given player the
people he or she may know
which is not himself or herself // this is nothing but obvious
which each is not already known to himself
With above table
by 1 -> 2 -> 4 and 1 -> 3 -> 5
4 and 5 can be suggested for 1 as 'people you may know'
order by number of mutual friends will be perfect
but I don't think I can understand even if someone show me how. sorry.
Thank you.
UPDATE ---------------------------------------------------------------------
I think I must try step by step by myself from what I’ve learned FROM HERE WITH VARIOUS PEOPLE even if it’s not the right answer. Please let me know if
I’m doing anything wrong.
First of all, let me self join the FRIEND table itself.
SELECT *
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
prints
+-----------+-----------+-----------+-----------+
| PLAYER_ID | FRIEND_ID | PLAYER_ID | FRIEND_ID |
+-----------+-----------+-----------+-----------+
| 1 | 2 | 2 | 1 |
| 1 | 2 | 2 | 3 |
| 1 | 2 | 2 | 4 |
| 1 | 2 | 2 | 5 |
| 1 | 3 | 3 | 5 |
| 2 | 1 | 1 | 2 |
| 2 | 1 | 1 | 3 |
| 2 | 3 | 3 | 5 |
+-----------+-----------+-----------+-----------+
F2.FRIEND_ID only
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
prints
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
| 2 |
| 3 |
| 5 |
+-----------+
for 1 only
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1;
prints
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
not 1
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1;
prints
+-----------+
| FRIEND_ID |
+-----------+
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
not 1’s direct knowns
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
prints
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
+-----------+
I think I’m getting there.
UPDATE -----------------------------------------------------------------
Following paths added
1 -> 100 -> 400
1 -> 200 -> 400
1 -> 300 -> 400
And the last query prints (again)
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
| 400 |
| 400 |
| 400 |
+-----------+
at last, I got the candidates: 4, 5, 400
Putting distinct surely work for the primary goal
SELECT DISTINCT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
prints
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 400 |
+-----------+
And, now, ordering by mutual counts needed.
Here comes the number of mutual friends for each candidates.
+-----------+
| FRIEND_ID |
+-----------+
| 4 | 1 (1 -> 2 -> 4)
| 5 | 2 (1 -> 2 -> 5, 1 -> 3 -> 5)
| 400 | 3 (1 -> 100 -> 400, 1 -> 200 -> 400, 1 -> 300 -> 400)
+-----------+
How can I calculate and order by those number of mutual friends?
SELECT F2.FRIEND_ID, COUNT(*)
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID;
prints
+-----------+----------+
| FRIEND_ID | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 5 | 2 |
| 400 | 3 |
+-----------+----------+
I got it!
SELECT F2.FRIEND_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
prints
+-----------+-----+
| FRIEND_ID | MFC |
+-----------+-----+
| 400 | 3 |
| 5 | 2 |
| 4 | 1 |
+-----------+-----+
Can anybody please confirm this? Is that query optimal? Any possible
performance problem when make it as a view?
Thank you.
UPDATE
I created a view as
CREATE VIEW FOLLOWABLE AS
SELECT F1.PlAYER_ID, F2.FRIEND_ID AS FOLLOWABLE_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F2.FRIEND_ID != F1.PLAYER_ID
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = F1.PLAYER_ID)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
and tested.
mysql> select * from FOLLOWABLE;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 2 | 100 | 1 |
| 2 | 200 | 1 |
| 2 | 300 | 1 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
6 rows in set (0.01 sec)
mysql> select * from FOLLOWABLE WHERE PLAYER_ID = 1;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
3 rows in set (0.00 sec)
问题答案：
use this EDIT
SELECT `friend_id` AS `possible_friend_id`
FROM `friends`
WHERE `player_id` IN ( --selecting those who are known
SELECT `friend_id` --by freinds of #1
FROM `friends`
WHERE `player_id` = 1)
AND `friend_id` NOT IN ( --but not those who are known by #1
SELECT `friend_id`
FROM `friends`
WHERE `player_id` = 1)
AND NOT `friend_id` = 1 --and are not #1 himself
--if one is known by multiple people
--he'll be multiple time in the list
GROUP BY `possible_friend_id` --so we group
ORDER BY COUNT(*) DESC --and order by amount of repeatings