How to get a hierarchical tree path in SQLite?

Question

Imagine a simple table that defines a tree structure.

create table nodes (
    id integer primary key,
    name text not null,
    parent integer
)

Some example nodes:

Node 1 is parent of 2 and 3. Node 3 is parent of 4. Is it possible to write a SQL query in SQLite, so that it returns:

id    path
1     foo
2     foo/bar
3     foo/baz
4     foo/baz/stuff

Answer 1

You can perform recursion in SQLite using recursive common table expressions.

An example query that would return the node paths:

with recursive paths(id, name, path) as (
    select id, name, name from nodes where parent is null
    union
    select nodes.id, nodes.name, paths.path || '/' || nodes.name
    from nodes join paths where nodes.parent = paths.id
)
select id, path from paths