SQLite Forum

Say I have a graph like:

```
1
|\
2 3
|/
4

5
|
6
```

stored in a sqlite database as:

```
create table edges(fromNode integer, toNode integer);
insert into edges(fromNode, toNode) values
 (1, 2),
 (1, 3),
 (2, 4),
 (3, 4),
 (5, 6);
```

I would like to generate a table of the breadth-first traversal of this graph  from a specific starting node.

I can currently do this using a recursive cte and then performing an aggregate query grouping on the id __outside__ of the recursive cte

```
with recursive
 bfs_tree(id, parent, distance) as (
  select 1, NULL, 0
  union all
  select edges.toNode, bfs_tree.id, bfs_tree.distance + 1
  from edges, bfs_tree
  where edges.fromNode = bfs_tree.id
  order by 3
 )
select id, parent, min(distance) from bfs_tree
group by id;
```

but this is very slow for larger graphs because the same nodes are being visited multiple times when there are multiple paths to a given node.

Is there any way to avoid revisiting nodes __inside__ the recursive cte?

Thanks in advance