Query tree like data similar to Oracle's Connect By Prior db statement

SQL++
1

In Oracle, there is a connect by prior statement that orders hierarchy easily. Such as HR data
EmpID Manager Level
100 — 1
101 100 2
108 101 3
109 108 4
110 108 4
111 108 4
112 108 4
113 108 4
200 101 3
203 101 3

select emp.employee_id, emp.manager_id, LEVEL
from employees emp where rownum <= 10
Connect by prior emp.employee_id = emp.manager_id Start with emp.employee_id = 100

Wondering if there is a built-in function to do something similar in CB? Otherwise, I’ll have to walk the tree myself - and that could be expensive in connections/ queries, then iterating through some large record sets. I’d like to be able to express a N1QL statement, similar to the query above - but understand that it may not be possible yet.

I hope that made sense, thank you in advance.

Will

2

Hi @thayerw, great question. We plan to add common table expressions (CTEs) in N1QL. This is the SQL standard way to traverse trees / hierarchies. For example, here is the Postgresql page on CTEs:

3

any update for the plan?