Aware there are limitations to what Base can do I’m seeking general guidance on how to use Base to process data in sqlite tables as a tree. I’d prefer to leave as much as possible in sql and use python if a supporting language is required.
Here’s a nice example [updated to sqlite following comment below by @Wanderer] demonstrating a tree reproduced from the sqlite documentation, particularly The WITH Clause using the “DESC” modifier.
query.sql
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2 DESC
)
SELECT substr('..........',1,level*3) || name FROM under_alice;
output
Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail
