Is it possible to write an SQLite query that recursively receives all elements that are children of the root node

Question

I have 2 tables. items and itemItems

itemItems describes the relationship of many to many between items . That is, the items member can have many children, and they can have many children, which in turn can have many children, etc.

paragraph:

 itemID | more stuff ...... 1 ... 2 ... 3 ... 4 ... 

itemItems:

 parentItemID | childItemID 1 2 1 3 2 4 

I want to write a query that recursively retrieves all children under the same root node.

I believe this is possible with something called a recursive union, but I find the concept very confusing ... (similar to this question, but with sqlite not sql server and many for many not one for many)

I can get the first level (i.e. all children under one element) by doing the following

 SELECT * FROM items INNER JOIN itemItems ON items.itemID = itemItems.childItemID WHERE itemItems.parentItemID = 1 

How can I expand this to recursively get all child children, etc ??

Answer 1

I just got a similar request to work using the with recursive syntax. General form:

 with recursive tc( i ) as ( select [... initial-query ...] union [... recursive-part (include tc) ...] ) select * from tc; 

The key in my case was to make sure tc was specified in the recursive part. In addition, this final choice is just to show the full content of the transitive closure, the real one must select the necessary lines.

I think this recipe applies to your case as follows. I have not tested this, I just copy / paste from my query and replace your table names. This works for me, but I could translate it wrong. I am also not quite sure of the effectiveness, etc., This is just what I have to work on.

 with recursive tc( i ) as ( select childItemID from itemItems where parentItemID = 1 union select childItemID from itemItems, tc where itemItems.parentItemID = tc.i ) select * from item where itemID in tc; 

NOTE. This worked for me on version 3.8.3.1, but not on 3.7.2.

Answer 2

The recursive version above requires ANSI SQL: 1999, which is supported by regular DBMSs, but there is also an ANSI SQL-92 method to achieve limited recursion. This approach can be extended arbitrarily.

In the example below, up to 7 levels are supported. If you want more, add more.

 SELECT DISTINCT I.* FROM item I INNER JOIN ( SELECT I1.itemID as Level1, I2.itemID as Level2, I3.itemID as Level3, I4.itemID as Level4, I5.itemID as Level5, I6.itemID as Level6, I7.itemID as Level7 FROM item I1 LEFT JOIN item I2 ON EXISTS (SELECT NULL FROM itemItems II1 WHERE II1.parentItemID = I1.itemID AND I2.itemID = II1.childItemID) LEFT JOIN item I3 ON EXISTS (SELECT NULL FROM itemItems II2 WHERE II2.parentItemID = I2.itemID AND I3.itemID = II2.childItemID) LEFT JOIN item I4 ON EXISTS (SELECT NULL FROM itemItems II3 WHERE II3.parentItemID = I3.itemID AND I4.itemID = II3.childItemID) LEFT JOIN item I5 ON EXISTS (SELECT NULL FROM itemItems II4 WHERE II4.parentItemID = I4.itemID AND I5.itemID = II4.childItemID) LEFT JOIN item I6 ON EXISTS (SELECT NULL FROM itemItems II5 WHERE II5.parentItemID = I5.itemID AND I6.itemID = II5.childItemID) LEFT JOIN item I7 ON EXISTS (SELECT NULL FROM itemItems II6 WHERE II6.parentItemID = I6.itemID AND I7.itemID = II6.childItemID) WHERE I1.itemID = 1 -- root node condition ) D ON I.itemID = D.Level1 OR I.itemID = D.Level2 OR I.itemID = D.Level3 OR I.itemID = D.Level4 OR I.itemID = D.Level5 OR I.itemID = D.Level6 Or I.itemID = D.Level7