Recursive Cte Stop Condition for Loops

Declare @MaxLevel Int = 5;

With bom (id, parentNumber, warehouse, sequenceNumber, childNumber, childDescription, qtyRequired, childItemClass, childItemType, scrapFactor, bubbleNumber,
          operationNumber, effectivityDate, discontinuityDate, companyID, Level
         )
As (Select *,
           1 As Level
    From   products.BillOfMaterial
    Where  parentNumber In ( 'XXXXXXXXXX' )
           And companyID = '0'
    Union All
    Select c.*,
           Level + 1 As Level
    From   bom b
    Inner Join products.BillOfMaterial c
        On b.childNumber = c.parentNumber
           And c.companyID = '0'
    Where  Level < @MaxLevel
   )
Select Distinct *
From   bom;

Declare @MaxLevel Int = 5;

;With Cte As
(
    Select  Convert(Date, GetDate()) As Date,
            1 As Level
    Union All
    Select  DateAdd(Day, 1, Date) As Date,
            Level + 1 As Level
    From    Cte
    Where   Level < @MaxLevel
)
Select  *
From    Cte

Date        Level
2017-09-14  1
2017-09-15  2
2017-09-16  3
2017-09-17  4
2017-09-18  5

delete from catalog_listings where id in (4,5)
delete from relator where id in (7,8)

child_name	sum
subChild2	8
subChild3	8
subChild1	16

To find infinite recursive loop in CTE

You haven't specified the dialect or your column names, so it is difficult to make the perfect example...

-- Some random data
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1, 'Child'), -- Try changing the second value (1) to 2 
(3, 2, 'SubChild')
-- End random data

;WITH RecursiveCTE (StartingID, Level, Parents, Loop, ID, ParentID, Description) AS
(
    SELECT ID, 1, '|' + CAST(ID AS VARCHAR(MAX)) + '|', 0, * FROM #MyTable
    UNION ALL
    SELECT R.StartingID, R.Level + 1, 
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) + '|',
        CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAR(MAX)) + '|%' THEN 1 ELSE 0 END,
        MT.*
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT StartingID, Level, Parents, MAX(Loop) OVER (PARTITION BY StartingID) Loop, ID, ParentID, Description 
    FROM RecursiveCTE 
    ORDER BY StartingID, Level

Something like this will show if/where there are loops in the recursive cte. Look at the column Loop. With the data as is, there is no loops. In the comments there are examples on how to change the values to cause a loop.

In the end the recursive cte creates a VARCHAR(MAX) of ids in the form |id1|id2|id3| (called Parents) and then checks if the current ID is already in that "list". If yes, it sets the Loop column to 1. This column is checked in the recursive join (the ABD R.Loop = 0).

The ending query uses a MAX() OVER (PARTITION BY ...) to set to 1 the Loop column for a whole "block" of chains.

A little more complex, that generates a "better" report:

-- Some random data
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1, 'Child'), -- Try changing the second value (1) to 2 
(3, 3, 'SubChild')
-- End random data

-- The "terminal" childrens (that are elements that don't have childrens
-- connected to them)
;WITH WithoutChildren AS
(
    SELECT MT1.* FROM #MyTable MT1
        WHERE NOT EXISTS (SELECT 1 FROM #MyTable MT2 WHERE MT1.ID != MT2.ID AND MT1.ID = MT2.ParentID)
)

, RecursiveCTE (StartingID, Level, Parents, Descriptions, Loop, ParentID) AS
(
    SELECT ID, -- StartingID 
        1, -- Level
        '|' + CAST(ID AS VARCHAR(MAX)) + '|', 
        '|' + CAST(Description AS VARCHAR(MAX)) + '|', 
        0, -- Loop
        ParentID
        FROM WithoutChildren
    UNION ALL
    SELECT R.StartingID, -- StartingID
        R.Level + 1, -- Level
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) + '|',
        R.Descriptions + CAST(MT.Description AS VARCHAR(MAX)) + '|', 
        CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAR(MAX)) + '|%' THEN 1 ELSE 0 END,
        MT.ParentID
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT * FROM RecursiveCTE 
    WHERE ParentID IS NULL OR Loop = 1

This query should return all the "last child" rows, with the full parent chain. The column Loop is 0 if there is no loop, 1 if there is a loop.

How to stop a recursive loop when a certain condition is met in PHP

<?php

public function getElements( $parent_id = NULL, $level = 0 ) {
    if( $level == 3 ) return []; // or return $arr;
    $data = models\Categories::find()->where( ['parent_id'=>$parent_id] )->all();
    $arr = array();
    foreach( $data as $data ) {       
        //do something
        $countChilds = models\Categories::find()->where( ['parent_id'=>$data->id] )->count();
        if( $countChilds > 0 ){
            $catData['sub_categories'] = $this->getElements( $parent_id = $data->id, $level + 1);
        }
        $arr[] = $catData;
    }
    return $arr;
}

In the above code, I made 2 changes.

• If $level == 3, then return it instantly. No need to put that in the foreach.
• It should be $catData['sub_categories'] = $this->getElements( $parent_id = $data->id, $level + 1); instead of $level++, since the child will have parent level + 1 and post increment operator doesn't do that. Also, even ++$level(pre-increment operation) would be a bad idea since you are modifying the variable even for the next upcoming entries in the foreach loop. So, $level + 1 is the correct way.

CTE goes in infinite loop?

create table dbo.text_master_test
(
text_id int,
text_details nvarchar(max),
new_text_id int
)
go

insert into text_master_test
values(1, 'det 1',2), (2, 'det 2',3), (3, 'det 3',4), (4, 'det 4',5), (5, 'det 5',5);
go

WITH textHierarchy AS (
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test tm 
    WHERE tm.text_id = 1
    UNION ALL
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 

)
SELECT * FROM textHierarchy;
go

create function dbo.textrecursion(@start_text_id int)
returns table
as
return
(
WITH textHierarchy 
AS 
(
    SELECT tm.text_id, tm.text_details, tm.new_text_id, 
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test tm 
    WHERE tm.text_id = @start_text_id
    UNION ALL
    SELECT tm.text_id, tm.text_details, tm.new_text_id,
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 
)
select text_id, text_details, new_text_id
from textHierarchy
);
go

select *
from dbo.textrecursion(1)

select *
from dbo.textrecursion(4)

select *
from dbo.textrecursion(5)
go

drop function dbo.textrecursion;
go
drop table dbo.text_master_test
go

Understanding the recursive CTE termination check

Here's a better example using dates. Assume we want to build a table of dates. 1 row for every month for the year 2017. We create a @startDate as the anchor and @endDate as the terminator. We set these to 12 months apart, since we want a single year. Then, the recursion will add one month via the DATEADD function to the @startDate until the terminator is met in the WHERE clause. We know it will take 11 recursions to hit 12 months... that is, 11 months + the start date. If we set the MAXRECURSION to anything less than 11, then it will fail since 11 are needed to fulfill the WHEREclause in our recursive CTE, that is the terminator..

declare @startDate datetime = '20170101'
declare @endDate datetime = '20171201'

;WITH Months
as
(
    SELECT @startDate as TheDate       --anchor
    UNION ALL
    SELECT DATEADD(month, 1, TheDate)  --recursive
    FROM Months
    WHERE TheDate < @endDate           --terminator... i.e. continue until this condition is met

)

SELECT * FROM Months OPTION (MAXRECURSION 10) --change this to 11

For your query, a simple join would suffice.

select 
  firstName
  ,lastName
  ,orderDate
  ,productID
from
  customers c
inner join
  orders o on o.customerID = c.id

However, I see that you are trying to return this in an odd format, which should be handled in what ever reporting application you are using. This would get you close without recursion.

with cte as(
select 
  firstName
  ,lastName
  ,orderDate
  ,productID
  ,dense_rank() over(order by c.id) as RN
from
  customers c
inner join
  orders o on o.customerID = c.id)

select distinct
  firstName
  ,lastName
  ,null
  ,null
  ,RN
from 
  cte
union all
select
  ''
  ,''
  ,orderDate
  ,productID
  ,RN
from 
  cte
order by RN, firstName desc

How does a SQL Recursive Query not run into an endless loop?

There is nothing "recursive" in "recursive" queries.

It should have been called "iterative".

There are some differences between vendors but the basic concept is the same:

1. The anchor part (the one that doesn't refer to the "recursive" query name) creates the initial set.

2. The iterative part (the one that refers to the "recursive" query name) is using the last set to creates a new set that now becomes the last set, and so on.
   
   It stops when it gets to an empty set.

---

And here is an endless query:

with recursive t as (select 1 union all select 1 from t) 
select count(*) from t

---

Explanation for the OP example

Initial set created by the anchor part, `VALUES (1)`: 
1 record, n=1

Sets created by the iterative part, `SELECT n+1 FROM t WHERE n < 100`:
1 record, n=2 (the initial set has 1 record with n=1 so `SELECT n+1 from t` returns 2)
1 record, n=3 
1 record, n=4
1 record, n=5
.
.
.
1 record, n=99
1 record, n=100 

When n=100 the WHERE condition `WHERE n < 100` causes an empty set to be created 
and the iteration stops.

---

One way to think on iterative queries:

with        t0 as (select ...)
           ,t1 as (select ... t0 ...)
           ,t2 as (select ... t1 ...)
           ,t3 as (select ... t2 ...)
            .
            .
            .

            select * from t0
union all   select * from t1
union all   select * from t2
union all   select * from t3
union all   ...

t0 is a CTE with no dependencies in other CTE.

t1 is a CTE with dependency in t0.

t2 is a CTE with dependency in t1 (and only t1!).

t3 is a CTE with dependency in t2 (and only t2!).

etc.

t1, t2, t3 etc.. are declared with identical queries, different only in their dependencies.

---

Related Topics