Basic Recursive Query on SQLite3

basic recursive query on sqlite3?

If you're lucky enough to be using SQLite 3.8.3 or higher then you do have access to recursive and non-recursive CTEs using WITH:

Sample Image

Thanks to lunicon for letting us know about this SQLite update.

In versions prior to 3.8.3, SQLite didn't support recursive CTEs (or CTEs at all for that matter) so there was no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:

Grab the initial row and the sub-part IDs.
Grab the rows and sub-part IDs for the sub-parts.
Repeat until nothing comes back.

How to make recursive query in SQLite?

SQLite doesn't support recursive CTEs (or CTEs at all for that matter),

there is no WITH in SQLite. Since you don't know how deep it goes, you can't use the standard JOIN trick to fake the recursive CTE. You have to do it the hard way and implement the recursion in your client code:

Grab the initial row and the sub-part IDs.
Grab the rows and sub-part IDs for the sub-parts.
Repeat until nothing comes back.

Recursive Query WITH - Parents regardless of levels

With this recursive cte:

with parent_of as (
  select process, parent from dependencies
  union all
  select p.process,
    (select parent from dependencies where process = p.parent) parent_parent
  from parent_of p
  where parent_parent is not null
)  

select * from parent_of
order by process, parent

See the demo.

Results:

| process | parent |
| ------- | ------ |
| B       | A      |
| C       | A      |
| E       | D      |
| F       | D      |
| F       | E      |
| G       | D      |
| G       | E      |
| G       | F      |

How to find levels in a hierarchy by dates using SQLite

Remove the WHERE clause from your code so that you query for all ids and in the 2nd part of the recursive CTE select supervisor_of.id and not org_1.id.

Finally you must group by id also:

WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
  SELECT org_1.id, org_1.boss_id, org_1.date_interest
  FROM org org_1
  UNION
  SELECT so.id, org_1.boss_id, org_1.date_interest
  FROM org org_1 JOIN supervisor_of so
  ON so.boss_id = org_1.id AND so.date_interest = org_1.date_interest
)
SELECT *, COUNT(*) AS level
FROM supervisor_of
GROUP BY date_interest, id
ORDER BY date_interest, id

See the demo.

Results:

id	boss_id	date_interest	level
1	null	1	1
2	1	1	2
3	1	1	2
1	null	2	1
2	1	2	2
3	1	2	2
4	2	2	3
1	null	3	1
2	1	3	2
3	1	3	2
4	2	3	3
5	4	3	4

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

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.

recursive sql search

In sqlite we can use a recursive CTE to pull this off.

WITH RECURSIVE reccte AS
(
    SELECT 
        uid as initialID,
        uID,
        parentID,
        someData,
        1 as depth 
    FROM table
    WHERE uID = 1 /*starting point for recursive stuff*/

    UNION ALL

    /*Recursive statement*/
    SELECT
        reccte.initialID,
        t1.uID,
        t1.parentID,
        someData,
        depth + 1
    FROM
        reccte
        INNER JOIN table as t1 ON
            recCTE.uID = t1.parentID /*joining up the parent*/
    WHERE depth < 15 /*keep from endless loops*/
)

/*Select all the someData's that resulted from the recursive lookup*/
SELECT someData FROM recCTE;

SQLite: avoiding cycles in depth-limited recursive CTE

By encoding the visited nodes on each row, one can avoid cycling on a node within a single traversal. If the node names are predictable, such as integers, then a simple delimited list will work:

sqlite> WITH RECURSIVE children(id, path, level) AS
   ...>   (SELECT 1, ",1,", 0  -- initial ',' is so instr() check below works
   ...>    UNION ALL SELECT target, children.path || target || ",", children.level+1
   ...>                FROM edges
   ...>                JOIN children ON edges.source = children.id
   ...>               WHERE children.level < 5
   ...>                 AND NOT instr(children.path, "," || target || ","))
   ...> SELECT * FROM children;
1|,1,|0
2|,1,2,|1
3|,1,2,3,|2

This does not prevent traversing to the same node through different paths, though. Consider adding another row:

sqlite> INSERT INTO edges VALUES (1, 3, "down");

Then the statement above would produce the following results:

1|,1,|0
2|,1,2,|1
3|,1,3,|1
3|,1,2,3,|2
2|,1,3,2,|2

Also the use of string concatenation and substring checking might not be the most performant.

Basic Recursive Query on SQLite3