php - 不使用php的高效友建议sql查询

Question

我有一个成员表，其中每个站点成员都是唯一 ID。例如

id  firstname   secondname  emailaddress    country city    gender  

第二张表是friends表，结构如下

id  meid    friendid    date

我将使用什么查询来根据共同好友获取特定用户的好友建议并进行相应排序。在我使用 php 循环访问并收集共同的 friend 之前，但随着网站的增长，php 开始出现问题并耗尽内存。

这是我使用的函数

//-----------------------------------------------    
function getFriendSuggestions($id)    
{
            $friendids=getFriendIdArray($id);  //returns list of your friends
            $networkids=getNetworkIdArray($id);//returns list of all members in your network(friends and their friends)
            $diff=array_merge(array(),array_diff($networkids,$friendids));
            $diff_mutual=array();
            $diff_mutual_total=array();
            for ($n=0;$n<count($diff);$n++)
            {
                $ff=getFriendIdArray($diff[$n]);
                $mf=array_merge(array(),array_intersect($ff,$friendids));
                $diff_mutual[]=$mf;
                $diff_mutual_total[]=count($mf);
            }
            $diff=array_merge(array(),$diff);
            $diff_mutual=array_merge(array(),$diff_mutual);
            $diff_mutual_total=array_merge(array(),$diff_mutual_total);    
            $w=$diff_mutual_total;
            arsort($w);
            $d=array();
            $dm=array();
            foreach ($w as $key => $value)
            {
                $d[]=$diff[$key];
                $dm[]=$diff_mutual[$key];
            }
            $cv=array($d,$dm);
            return $cv;
}

Answer 1

如果存在大量此类条目，我们称 B 可能是 A 的 friend

(B, someguy) (someguy, A)

在数据库中，没有(B，A)条目。

我们知道用户A的ID，设AID。那么我们可以这样做:

SELECT b.meid, COUNT(b.meid) AS incommon
FROM friends AS b
JOIN friends AS a ON (b.friendid = a.meid AND a.friendid = AID)
GROUP BY b.meid ORDER BY incommon DESC;

这将告诉我们 A 的所有“可能的 friend ”，包括那些已经是 A 的 friend 。那么我们必须排除他们:

SELECT maybe.meid, maybe.incommon FROM
( SELECT b.meid, COUNT(b.meid) AS incommon
FROM friends AS b
JOIN friends AS a ON (b.friendid = a.meid AND a.friendid = AID)
GROUP BY b.meid ORDER BY incommon DESC ) AS maybe
LEFT JOIN friends AS already ON (maybe.friendid = already.meid AND already.friendid = AID) WHERE already.friendid IS NULL;

然后我们需要填充其余字段:

SELECT members.firstname, members.secondname, maybe.incommon FROM
( SELECT b.meid, COUNT(b.meid) AS incommon
FROM friends AS b
JOIN friends AS a ON (b.friendid = a.meid AND a.friendid = AID)
GROUP BY b.meid ORDER BY incommon DESC ) AS maybe
LEFT JOIN friends AS already ON (maybe.friendid = already.meid AND already.friendid = AID) 
    JOIN members ON (members.id = maybe.meid)
    WHERE already.friendid IS NULL;

这将返回 AID 的好友建议，包括每个选择有多少共同点(例如“John Doe(15 个共同好友)”等)。