SQLite recursive CTE to calculate number of items in list (of lists)-CodePudding

I have a table List ...

and a second table ListItem.

The column "parentId" in ListItem points to the List that the item belongs to. When I run the SQLite query below...

WITH RECURSIVE x (id, name, parentId, totalNumberOfItems)
AS (
SELECT ID, Name, parentId, (SELECT COUNT(*) FROM listitem WHERE parentId = list.id) as totalNumberOfItems
FROM List 
WHERE parentId is NULL
UNION ALL
SELECT e.id, e.name, e.parentId, (SELECT COUNT(*) FROM listitem WHERE parentId = e.id) as totalNumberOfItems
FROM list e INNER JOIN x on e.parentId = x.ID
)
select * from x

Then I get the following result:

However, what I want is to know how many items there are in List1 its child lists(List2 List3 ... etc), so for List1 the totalNumberOfItems is 6.

Below is a script that allows to create the Db-Schema and insert the data.

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "List" (
    "id"    INTEGER NOT NULL,
    "name"  TEXT NOT NULL,
    "parentId"  INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "ListItem" (
    "id"    INTEGER NOT NULL,
    "name"  TEXT NOT NULL,
    "parentId"  INTEGER NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT),
    FOREIGN KEY("parentId") REFERENCES "List"("id")
);
INSERT INTO "List" ("id","name","parentId") VALUES (1,'List1',NULL),
 (2,'List2',1),
 (3,'List3',2);
INSERT INTO "ListItem" ("id","name","parentId") VALUES (1,'Item1',1),
 (2,'Item2',1),
 (3,'Item3',2),
 (4,'Item4',1),
 (5,'Item5',2),
 (6,'Item6',3);
COMMIT;

CodePudding user response：

Use a recursive CTE that returns the ids of the list that you want and all its children below up the lowest level.

Then aggregate in the table ListItem by using the results of that query:

WITH cte AS (
  SELECT id FROM List WHERE name = 'List1' 
  UNION ALL
  SELECT l.id
  FROM List l INNER JOIN cte c
  ON l.parentId = c.id
)
SELECT COUNT(*) count FROM ListItem WHERE parentId IN cte;

See the demo.