SQLite from android get path like indexes with recursive query

Question

I have this table named items with following columns and rows.

id   item_name   parentId
--------------------------
1    Item 1         0
2    Item 2         1
3    Item 3         2
4    Item 4         1
5    Item 5         2
6    Item 6         3
7    Item 7         6
8    Item 8         7

I could measure the level of rows parent by recursive CTE, but I still need to get the path as this format: 1.1.1, 1.2.1 ... This is my current query:

with recursive items_cache (lvl, id, item_name, parent_id) as (
    select 0 as lvl, id, item_name, parent_id
    from items
    where parent_id = 0
  union all
    select items_cache.lvl + 1, items.id, items.item_name, items.parent_id
    from items_cache    
    join items  on items_cache.id = items.parent_id
    order by items_cache.lvl+1 desc, items.id
)
select
    lvl,
    *
from items_cache

Desired results:

id   item_name   parentId   lvl       path_index
-------------------------------------------------
1    Item 1         0        0        1
2    Item 2         1        1        1.1
3    Item 3         2        2        1.1.1
6    Item 6         3        3        1.1.1.1
7    Item 7         6        4        1.1.1.1.1
8    Item 8         7        5        1.1.1.1.1.1
5    Item 5         2        2        1.1.2
4    Item 4         1        1        1.2
9    Item 9         0        0        2

How can I do that in SQLite? The SQLite version that android runs is 3.21, so it does not support window functions.

Answer 1

Use a recursive cte to get the level of each id, then ROW_NUMBER() window function to get the rank of each id under its parent and another recursive cte that will conatenate the row numbers:

WITH 
  levels AS (
    SELECT *, 0 lvl FROM items
    UNION ALL
    SELECT i.*, l.lvl + 1 
    FROM items i INNER JOIN levels l
    ON l.id = i.parentId 
  ),
  row_numbers AS (
    SELECT id, item_name, parentId, MAX(lvl) lvl,
           ROW_NUMBER() OVER (PARTITION BY parentId, lvl ORDER BY id) rn 
    FROM levels
    GROUP BY id, item_name, parentId
  ),
  cte AS (
    SELECT id, item_name, parentId, lvl, rn || '' path_index
    FROM row_numbers
    UNION ALL
    SELECT r.id, r.item_name, r.parentId, r.lvl, 
           c.path_index || '.' || r.rn
    FROM row_numbers r INNER JOIN cte c
    ON r.parentId = c.id 
  )
SELECT * 
FROM cte  
GROUP BY id
HAVING MAX(LENGTH(path_index))
ORDER BY path_index

See the demo.

If your version of SQLite does no support window functions use:

(SELECT COUNT(*) FROM levels l2 
 WHERE l2.parentId = l1.parentId AND l2.lvl = l1.lvl AND l2.id <= l1.id) rn

instead of:

ROW_NUMBER() OVER (PARTITION BY parentId, lvl ORDER BY id) rn

See the demo.

Results:

id	item_name	parentId	lvl	path_index
1	Item 1	0	0	1
2	Item 2	1	1	1.1
3	Item 3	2	2	1.1.1
6	Item 6	3	3	1.1.1.1
7	Item 7	6	4	1.1.1.1.1
8	Item 8	7	5	1.1.1.1.1.1
5	Item 5	2	2	1.1.2
4	Item 4	1	1	1.2
9	Item 9	0	0	2