SQLite Nested Table – Order by Path Index and List Order

cteorder-byrecursivesqlite

I have a SQL table with this nested structure:

CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);

My SQLite version does not support window function, currently my query at link SQLite query are returning as follow the result, but it's not ordered according to list_order, another detail is that the list_order column could come with all values equal to zero.

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   1        Pois           0          0     1           4
   2        Então          0          0     2           5
   3        Teste          0          0     3           3
   11       Teste 3        3          1     3.1         2
   12       Teste 2        3          1     3.2         0
   13       Teste 1        3          1     3.3         1
   4        Fundo          0          0     4           2
   7        Profundo       4          1     4.1         1
   8        Dhdhd          4          1     4.2         0
   9        Gagagaga       8          2     4.2.1       1
   10       Fsfsfsfs       8          2     4.2.2       0
   5        Profundo       0          0     5           1
   6        Gigante        0          0     6           6
   14       Teste          0          0     7           0

What I want the path_index column to follow list_order and level columns, like here:

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   14       Teste          0          0     1           0
   5        Profundo       0          0     2           1
   4        Fundo          0          0     3           2
   8        Dhdhd          4          1     3.1         0
   10       Fsfsfsfs       8          2     3.1.1       0
   9        Gagagaga       8          2     3.1.2       1
   7        Profundo       4          1     3.2         1
   3        Teste          0          0     4           3
   12       Teste 2        3          1     4.1         0
   13       Teste 1        3          1     4.2         1
   11       Teste 3        3          1     4.3         2
   1        Pois           0          0     5           4
   2        Então          0          0     6           5
   6        Gigante        0          0     7           6

How can I align the path_index sorted according to list_order and level columns?

Best Answer

Needed ordering may be performed by the next query:

WITH
cte AS ( SELECT *, CAST(list_order AS TEXT) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || '.' || items.list_order
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT * 
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a7957edeabb5a9a1c9dff2d2e92ffe0c

If list_order values above 9 are present then you must apply zero-padding expression to its value. For example

WITH
cte AS ( SELECT *, printf('%04d', list_order) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf('%04d', items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT * 
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=52d6d43db054cb64754c37d3820c0b1e

do you have any trick to make the zero-padding dynamic? – alexandre9865

WITH
printf_format AS ( SELECT '%0' || LENGTH(MAX(list_order)) || 'd' AS format
                   FROM items ),
cte AS ( SELECT items.*, printf(printf_format.format, list_order) as ord
         FROM items
         CROSS JOIN printf_format
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf(printf_format.format, items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id
         CROSS JOIN printf_format )
SELECT * 
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=728695f41db0913bcde76370f70c885a

Related Question