Friend of Friend


———-
UID | FID
———-
1 | 2
1 | 3
1 | 4
2 | 4
4 | 5
Dst…

UID: User ID
FID: Friends ID

—————————————————————————–

join similar table

select u1.uid as people, u2.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

——————————————————–

select u1.uid as people, u3.fid as friend
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
inner join tabel u3
on u2.fid=u3.uid
where u1.uid=1;

—————————————————————————–
search whose uid=1 :

SELECT fid FROM t WHERE uid=1

search friend of friend whose uid=1 :

SELECT fid FROM t WHERE uid IN (SELECT fid FROM t WHERE uid=1)

or (maybe) :

SELECT DISTINCT t2.fid FROM t t1
INNER JOIN t t2 ON t2.uid=t1.fid
WHERE t1.uid=1

merge friend + friend of friend, use UNION.
—————————————————————————–

display it like friendster do :

select u1.uid as first, u1.fid as second, u2.fid as third
from tabel u1
inner join tabel u2
on u1.fid=u2.uid
where u1.uid=1;

—————————————————————————–

1 level: (result: You > XXX > Target)

$sql=”SELECT friend_2.uid as XXX
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
WHERE
friend_3.uid=$uid and
friend_2.fid=$fid and
limit 1″;
“);

2 level: (result: You > XXX > YYY > Target)

$sql=”SELECT friend_2.uid as XXX, friend_1.uid as YYY
FROM friend_3, _users
INNER JOIN friend_2
ON friend_3.fid = friend_2.uid
INNER JOIN friend_1
ON friend_2.fid = friend_1.uid
WHERE
friend_3.uid=$uid and
friend_1.fid=$fid and
limit 1”;

note: all friend table friend_1, teman_2, friend_3 has 75.000 rows,

problem:

1.very slow especially for 2 level
2. added with order by rand() sometimes time-out
3. how to make it simpler?

—————————————————————————

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s