Slide 7
Slide 7 text
People you might know (SQL)
Source: http://techportal.inviqa.com/2009/09/07/graphs-in-the-database-sql-meets-social-networks/
SELECT a AS you,
b AS mightknow,
shared_connection,
CASE
WHEN (n1.feat1 = n2.feat1 AND n1.feat1 = n3.feat1) THEN 'feat1 in common'
WHEN (n1.feat2 = n2.feat2 AND n1.feat2 = n3.feat2) THEN 'feat2 in common'
ELSE 'nothing in common'
END AS reason
FROM (
WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
( SELECT a, b, 1 AS distance,
a || '.' || b || '.' AS path_string,
b AS direct_connection
FROM edges2
WHERE a = 1 -- set the starting node
UNION ALL
SELECT tc.a, e.b, tc.distance + 1,
tc.path_string || e.b || '.' AS path_string,
tc.direct_connection
FROM edges2 AS e
JOIN transitive_closure AS tc ON e.a = tc.b
WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
AND tc.distance < 2
)
SELECT a,
b,
direct_connection AS shared_connection
FROM transitive_closure
WHERE distance = 2
) AS youmightknow
LEFT JOIN nodes AS n1 ON youmightknow.a = n1.id
LEFT JOIN nodes AS n2 ON youmightknow.b = n2.id
LEFT JOIN nodes AS n3 ON youmightknow.shared_connection = n3.id
WHERE (n1.feat1 = n2.feat1 AND n1.feat1 = n3.feat1)
OR (n1.feat2 = n2.feat2 AND n1.feat2 = n3.feat2);