社交网络-推荐朋友

Question

我有两个简单的MySQL表:用户和关系。

关系表：

user_id int(10) unsigned    NO  PRI     
friend_id   int(10) unsigned    NO  PRI 

(部分)用户表：

id  int(10) unsigned    NO  PRI     
username    varchar(128)    NO

我使用以下查询选择朋友的朋友：

SELECT f2.friend_id, u.username
FROM relations f1
JOIN relations f2 ON f1.friend_id=f2.user_id
LEFT JOIN user u ON u.id = f2.friend_id
WHERE f2.friend_id NOT IN (select friend_id from relations where user_id=@user_id) AND f1.user_id= 2 AND f2.friend_id!= 2

但我也需要得到建议的朋友..。(小组中认识两个或更多直接朋友的人)，我对此有意见。什么是好方法(查询，还是应该使用PHP)？去找建议的朋友？

Answer 1

考虑以下几点..。此示例假定交互作用是通过每段友谊插入两行来建立的。然而，为了简单起见，下面的例子并不能证明友谊是有回报的！

 DROP TABLE IF EXISTS friends;

 CREATE TABLE friends
 (initiator VARCHAR(12) NOT NULL
 ,reciprocator VARCHAR(12) NOT NULL
 ,PRIMARY KEY (initiator,reciprocator)
 );

 INSERT INTO friends VALUES 
 ('Adam','Ed'),
 ('Ed','Adam'),
 ('Adam','Ben'),
 ('Ben','Adam'),
 ('Adam','Charlie'),
 ('Charlie','Adam'),
 ('Adam','Dan'),
 ('Dan','Adam'),
 ('Ed','Ben'),
 ('Ben','Ed'),
 ('Ben','Charlie'),
 ('Charlie','Ben'),
 ('Charlie','Dan'),
 ('Dan','Charlie'),
 ('Dan','Fred'),
      ('Fred','Dan'),
 ('Adam','Fred'),
      ('Fred','Adam');

为了得到本的“朋友朋友”名单我们可以这么做.

 SELECT y.reciprocator
   FROM friends x
   JOIN friends y
     ON y.initiator = x.reciprocator
    AND y.reciprocator <> x.initiator
   LEFT
   JOIN friends z
     ON z.reciprocator = y.reciprocator 
    AND z.initiator = x.initiator
  WHERE x.initiator = 'Ben'
    AND z.initiator IS NULL;
 +--------------+
 | reciprocator |
 +--------------+
 | Dan          |
 | Fred         |
 | Dan          |
 +--------------+

正如你所看到的，因为丹是亚当和查理(本的朋友)的朋友，他的名字出现了两次。

因此，要想得到一个不同朋友的列表，只需包含一个不同的操作符。

同样地，为了得到一个人名单，谁是本的陌生人，但朋友至少两个本的朋友，我们可以这样做.

SELECT y.reciprocator
  FROM friends x
  LEFT
  JOIN friends y
    ON y.initiator = x.reciprocator
   AND y.reciprocator <> x.initiator
  LEFT
  JOIN friends z
    ON z.reciprocator = y.reciprocator
   AND z.initiator = x.initiator
 WHERE x.initiator = 'Ben'
   AND z.initiator IS NULL
 GROUP
    BY y.reciprocator
HAVING COUNT(*) >= 2;
 +--------------+
 | reciprocator |
 +--------------+
 | Dan          |
 +--------------+

可能有几种方法来处理这个问题的对等方面，就像处理互惠本身有几种方法一样。

一种方法是用一个简单的子查询替换上面的friends表。

SELECT y.reciprocator
  FROM (SELECT a.* FROM friends a JOIN friends b ON b.reciprocator = a.initiator AND b.initiator = a.reciprocator) x

  LEFT
  JOIN (SELECT a.* FROM friends a JOIN friends b ON b.reciprocator = a.initiator AND b.initiator = a.reciprocator) y
    ON y.initiator = x.reciprocator
   AND y.reciprocator <> x.initiator

  LEFT
  JOIN (SELECT a.* FROM friends a JOIN friends b ON b.reciprocator = a.initiator AND b.initiator = a.reciprocator) z
    ON z.reciprocator = y.reciprocator
   AND z.initiator = x.initiator

 WHERE x.initiator = 'Ben'
   AND z.initiator IS NULL
 GROUP
    BY y.reciprocator
HAVING COUNT(*) >= 2; 

Answer 2

谢谢，草莓，可惜我不能马上用你的解决方案.我的设计(奥克斯沃尔)完全不同：

+--------------+--------------+--------------+--------------+
| userId       | friendId     | status       | whatever     | 
+--------------+--------------+--------------+--------------+
| 1            | 3            | request      | Dan          |
| 3            | 6            | active       | 
| 1            | 7            | ignore
+--------------+

没有dup 1->2，2->1类

所以我的问题是：

    SELECT
        y.friendId,
        COUNT(*) AS totalFriends
    FROM
        `ow_friends_friendship` x
    LEFT JOIN `ow_friends_friendship` y 
                ON y.userId = x.friendId         

    WHERE
        x.userId = xxx
    AND  y.friendId not in (SELECT userId FROM ow_friends_friendship WHERE friendId = xxx)
    AND  y.friendId not in (SELECT friendId  FROM ow_friends_friendship WHERE userId = xxx)

    GROUP BY
        y.friendId
    HAVING
        totalFriends >= 2
    ORDER BY
        totalFriends DESC

希望它能对将来的人有所帮助