0

This is a query using sql CTE's to create a transitive closure of a table containing relations between users.(edges of a graph). How can this be done in mySQL?

WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
( SELECT a, b, 1 AS distance,
         a || '.' || b || '.' AS path_string
  FROM edges
 WHERE a = 1 -- source

  UNION ALL

  SELECT tc.a, e.b, tc.distance + 1,
         tc.path_string || e.b || '.' AS path_string
  FROM edges AS e
  JOIN transitive_closure AS tc ON e.a = tc.b
 WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
)
  SELECT * FROM transitive_closure
  ORDER BY a, b, distance;

or at least find all simple paths between two nodes without creating a transitive closure for all nodes. code taken from: http://techportal.inviqa.com/2009/09/07/graphs-in-the-database-sql-meets-social-networks/

Guy Coder
  • 24,501
  • 8
  • 71
  • 136
user847988
  • 984
  • 1
  • 16
  • 30
  • If this is so important to you, why not migrate to a DBMS that supports this kind of queries out of the box? –  Nov 08 '11 at 23:37
  • I cant i need to go with mySQL is a huge deployed system that cannot change. – user847988 Nov 09 '11 at 14:43

0 Answers0