Attached is a link to a Squirrel extension, so that it is also possible to use CTEs for database queries. An interesting way to easily get tree structures from a database.
I tested this with Corteza and Percona. I was able to read tree structures based on the concept described here:
Finding the Depth of the Nodes
In case there are other interested users, here is the link to the extended squirrel code. It may have to be further adapted to support CTEs in general, test cases welcome.
Background, or why i think its worth a look:
In previous projects, the hierarchical representation of data has proven to be advantageous for me. In particular, large amounts of data with an increased need for structuring (measures, processes, projects,…) benefit when accessed due to the logical segmentation. Unfortunately, there is currently no component in Corteza, no tree grid that could display large amount of structured data properly. From my point of view, the advantages described, as well as the possibilities of simply inheriting access rights at database level, would be worth considering. In a test setup I have recently added a treegrid compose component to Corteza and brought it together with a VUE treegrid (syncfusion). I can currently add a tree grid to pages and display module data. Overall, however, my efforts are still at the beginning, especially since I only want to use the treegrid for structuring and display without messing with the corteza data storage concept. (As a transparent layer without affecting the functionality of the flat data storage).
Unfortunately I still have to get my hands on the API. Compatibility with (https://www.odata.org) in general would be nice. I have not checked whether there are better or more sensible standards.