If I have a setup similar to this in Clickhouse:
CREATE TABLE IF NOT EXISTS nodes (
id UInt64,
from String,
to String
) ENGINE = MergeTree()
PRIMARY KEY id;
INSERT INTO nodes (id, from, to) VALUES
(1, 'a', 'b'),
(2, 'a', 'c'),
(3, 'b', 'd'),
(4, 'd', 'e');
Is it possible to get all the possible paths, something like this?
| paths | ids |
|---|---|
| a,b | 1 |
| a,c | 2 |
| a,b,d | 1,3 |
| a,b,d,e | 1,3,4 |
Looks like Clickhouse does not support recursive CTE, like other dbs. The engine does not have to be MergeTree. I'm flexible on table structure and output, at this point not even sure if paths are possible in Clickhouse?
So far I've been playing around with recursive CTE in Postgres, but it doesn't help here since: https://github.com/ClickHouse/ClickHouse/issues/16907#issuecomment-727798107 Maybe there should be a completely different approach?
