5 months, 2 weeks ago Mike Reiche

How to get the depth of the graph?

WITH RECURSIVE graph as (
    SELECT pr.*,@depth as depth FROM rcte_people_relationships pr WHERE source_id=3
  UNION
    SELECT pr.*,(@depth:=@depth+1) as depth FROM rcte_people_relationships pr, graph WHERE graph.target_id=pr.source_id
) 
SELECT * FROM graph
CROSS JOIN (SELECT @depth := 0) AS depth;

Creates an infinite loop.

 
2 months, 1 week ago Jon Armstrong

From the documentation above, I've added n to indicate the level within the hierarchy:

CREATE TABLE bus_routes (origin varchar(50), dst varchar(50));
INSERT INTO bus_routes VALUES
  ('New York', 'Boston'),
  ('Boston', 'New York'),
  ('New York', 'Washington'),
  ('Washington', 'Boston'),
  ('Washington', 'Raleigh')
;

WITH RECURSIVE paths (n, cur_path, cur_dest) AS (
    SELECT 1 AS n, origin, origin FROM bus_routes WHERE origin='New York' 
     UNION
    SELECT n + 1
         , CONCAT(paths.cur_path, ',', bus_routes.dst)
         , bus_routes.dst 
      FROM paths, bus_routes 
     WHERE paths.cur_dest = bus_routes.origin
       AND LOCATE(bus_routes.dst, paths.cur_path) = 0 
) 
SELECT * FROM paths
;
 
2 months, 1 week ago Jon Armstrong

The result:

+---+-----------------------------+------------+
| n | cur_path                    | cur_dest   |
+---+-----------------------------+------------+
| 1 | New York                    | New York   |
| 2 | New York,Boston             | Boston     |
| 2 | New York,Washington         | Washington |
| 3 | New York,Washington,Boston  | Boston     |
| 3 | New York,Washington,Raleigh | Raleigh    |
+---+-----------------------------+------------+