Hello, Dear,
I got some result with this, adapted from SQLite.org docs; a little simpler for a beginning.
But the result isn't yet correct (see 'sort order' under SQL code):
PageTreeTT1_TreeVw02 :
WITH RECURSIVE
under_root(ID_Page,level, lbl,slug,OnOff,sort) AS (
SELECT
PagesTreeTT1.ID_Page, 0, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.OnOff,PagesTreeTT1.PgSort
FROM PagesTreeTT1
WHERE PagesTreeTT1.ParentPage_Id ISNULL AND PagesTreeTT1.OnOff = 1
UNION ALL
SELECT PagesTreeTT1.ID_Page, under_root.level+1, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.OnOff,PagesTreeTT1.PgSort
FROM PagesTreeTT1 JOIN under_root ON PagesTreeTT1.ParentPage_Id=under_root.ID_Page
WHERE PagesTreeTT1.OnOff = 1
ORDER BY 2 DESC
)
SELECT
(substr('..........',1,level*3) || lbl) AS BranchString,
slug, sort
FROM
under_root;
+-------------------------------+------------+------+
| BranchString | slug | sort |
+-------------------------------+------------+------+
| Histoire d'une recherche | HistRchch | 10 |
| ...Gedinne - et rien d'autre | GDN_ | 20 |
| ...Trouver un lieu | TrvLieu | 10 |
| ...J'ai trouvé | gTrv | 30 |
| Découverte | decouvert | 20 |
| ...Sous la Forge - Copinette- | SsLaForge | 30 |
| ...Inauguration | inaug | 40 |
| Un chantier? | chantier_ | 50 |
| Et autour de ce site | autour | 60 |
| Infos utiles | infos | 70 |
| ...Gedinne (Commune) | GdnCmn | 20 |
| ...Vie locale et régionale | VieLocale- | 50 |
| ......Socio-économie | SocioEco | 10 |
| ......Culturel | culturel | 20 |
| ......Nature | nature | 50 |
| ......Patrimoine | patrimn | 40 |
| ......Politique | politik | 30 |
| ...Willerzie (village) | WillVlg | 10 |
| ...Commerces | commerces | 30 |
| ...Services | services | 40 |
| ......Sports - Activités | SportAct | 30 |
| ......Hébergements | hebergt | 10 |
| ......Restauration | restaur | 20 |
| A propos | about | 95 |
+-------------------------------+------------+------+
Almost ok but the sort order (example lines 2,3,4) are not following the sort value in this subgroup.
The second problem is (see PageTreeTT1_TreeVw02a below):
when adding the ParentPage_Id field (alias : parid) in the query, the output BranchString field is buggy for the rows of 1st level (top parents level : "Histoire d'une recherche") ; The BranchString is NULL.
PageTreeTT1_TreeVw02a :
WITH RECURSIVE
under_root(ID_Page,level, lbl,slug, sort, OnOff, parid) AS (
SELECT
PagesTreeTT1.ID_Page, 0, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.PgSort, PagesTreeTT1.OnOff, PagesTreeTT1.ParentPage_Id
FROM PagesTreeTT1
WHERE PagesTreeTT1.ParentPage_Id ISNULL AND PagesTreeTT1.OnOff = 1
UNION ALL
SELECT PagesTreeTT1.ID_Page, under_root.level+1, PagesTreeTT1.PgDescr, PagesTreeTT1.TxtID_Page, PagesTreeTT1.PgSort, PagesTreeTT1.OnOff, PagesTreeTT1.ParentPage_Id
FROM PagesTreeTT1 JOIN under_root ON PagesTreeTT1.ParentPage_Id=under_root.ID_Page
WHERE PagesTreeTT1.OnOff = 1
ORDER BY 2 DESC
)
SELECT (substr('..........',1,level*3) || lbl || ' ' || ID_Page || ' \< ' || parid ) as BranchString, slug, sort FROM under_root;
+-----------------------------------------+------------+------+
| BranchString | slug | sort |
+-----------------------------------------+------------+------+
| NULL | HistRchch | 10 |
| ...Gedinne - et rien d'autre 4 \< 2 | GDN_ | 20 |
| ...Trouver un lieu 3 \< 2 | TrvLieu | 10 |
| ...J'ai trouvé 5 \< 2 | gTrv | 30 |
| NULL | decouvert | 20 |
| ...Sous la Forge - Copinette- 10 \< 7 | SsLaForge | 30 |
| ...Inauguration 11 \< 7 | inaug | 40 |
| NULL | chantier_ | 50 |
| NULL | autour | 60 |
| NULL | infos | 70 |
| ...Gedinne (Commune) 16 \< 14 | GdnCmn | 20 |
| ...Vie locale et régionale 19 \< 14 | VieLocale- | 50 |
| ......Socio-économie 20 \< 19 | SocioEco | 10 |
| ......Culturel 21 \< 19 | culturel | 20 |
| ......Nature 24 \< 19 | nature | 50 |
| ......Patrimoine 23 \< 19 | patrimn | 40 |
| ......Politique 22 \< 19 | politik | 30 |
| ...Willerzie (village) 15 \< 14 | WillVlg | 10 |
| ...Commerces 17 \< 14 | commerces | 30 |
| ...Services 18 \< 14 | services | 40 |
| ......Sports - Activités 28 \< 18 | SportAct | 30 |
| ......Hébergements 26 \< 18 | hebergt | 10 |
| ......Restauration 27 \< 18 | restaur | 20 |
| NULL | about | 95 |
+-----------------------------------------+------------+------+
This 2d problem could be solved with this endinc SQL string :
... ... ) SELECT
( substr( '..........', 1, level * 3 ) || lbl || ' ' || ID_Page ) AS BranchString,
slug, sort , parid FROM under_root;
But I 'd like to understand why buggy in a calculated/concatenated field, and not as plain field ('parid').
QUESTION / DEMAND :
is there a way to correct this SQL?
Or should I switch on Ryan's example (which is a bit too difficult for me for the while)?
Thanks on forward