mysql - 根据关注我的人建议我应该关注谁，关注他们自己

Question

我不能用英语很好地总结这个问题，所以转换为 SQL 令我感到困惑。

我有一个用户表和一个关注表

USER
id | first_name | last_name
10 | Homer      | Simpson
12 | Marge      | Simpson
14 | Bart       | Simpson
16 | Lisa       | Simpson

FOLLOW
id | follow_id | follower_id | status
1  | 10        | 12          | APPROVED
2  | 10        | 14          | APPROVED
3  | 16        | 12          | APPROVED
4  | 16        | 14          | APPROVED  

基本上，我想向用户推荐他可能想关注的其他人。

我将基于:

• 找到所有关注我的人，如果我还没有关注他们，建议我关注他们
• 从第一个列表(关注我的人)中，找到他们关注的所有人
• 对于其中的每一个，建议我关注这些人(如果我还没有这样做的话)

有人能在正确的方向上帮助我吗？

在上面的示例数据中

• Marge 和 Bart 已经在关注 Homer
• 反过来，Marge 跟着 Lisa(Bart 也跟着 Lisa)

所以，在这个例子中，我想建议 Homer 遵循:

• Marge(因为她在跟着他)
• 巴特(因为他在跟着他)
• Lisa(因为她被 Marge 跟踪)

在 RICHARD 的帮助下，这是最终的解决方案(作为存储过程)

唯一的添加是删除已经被关注的用户。

BEGIN
    SELECT DISTINCT t1.ID,first_name,last_name FROM
    (
        #Users that are following me
        SELECT u.id as ID, u.first_name , u.last_name 
        FROM mchq_user u
        INNER JOIN follow AS f ON u.id = f.follower_id
        WHERE f.follow_id = in_userID
        UNION

        #Users followed by followers of me
        SELECT  f2.follow_id as ID, u2.first_name, u2.last_name
        FROM mchq_user u
        INNER JOIN follow AS f ON u.id = f.follower_id
        INNER JOIN follow AS f2 ON f2.follower_id = u.id
        INNER JOIN mchq_user AS u2 ON u2.id = f2.follow_id
        WHERE f.follow_id = in_userID AND f2.follow_id != in_userID
    ) AS t1
    WHERE t1.Id NOT IN (
        # Users that I follow already
        SELECT u.id
        FROM mchq_user u
        INNER JOIN follow AS f ON u.id = f.follow_id
        WHERE f.follower_id = in_userID
    );
END

Answer 1

CREATE TABLE #user (id INT, first_name VARCHAR(50), last_name VARCHAR(50 ))
CREATE TABLE #follow (user_id INT, following_id INT, f_status VARCHAR(50))


INSERT INTO #user ( id, first_name, last_name )
SELECT 10, 'Homer','Simpson' UNION ALL
SELECT 12, 'Marge','Simpson' UNION ALL
SELECT 14, 'Bart','Simpson'  UNION ALL
SELECT 16, 'Lisa','Simpson'

INSERT INTO #follow ( user_id, following_id, f_status )
SELECT  12  ,   10   , 'APPROVED' union all
SELECT  14  ,   10   , 'APPROVED' union all
SELECT  12  ,   16   , 'APPROVED' union all
SELECT  14  ,   16   , 'APPROVED'   

-- Usrs that follow Homer
SELECT id, first_name , last_name 
FROM #user u
INNER JOIN  #follow AS F ON u.id = F.user_id
WHERE f.following_id = 10
UNION
-- Users followed by followers of Homer
SELECT  F2.following_id, u2.first_name, U2.last_name
FROM #user u
INNER JOIN #follow AS F ON u.id = F.user_id 
INNER JOIN #follow AS F2 ON F2.user_id = u.id
INNER JOIN #user AS U2 ON U2.id = F2.following_id
WHERE f.following_id = 10 AND F2.following_id != 10



DROP TABLE #follow
DROP TABLE #user