Recursing through heirarchy in a dataset

Question

Let's say I have a DB table with the following structure

There are three columns in the table- case id, dependent case id(s) and deployment status of case id.

I want to query a list of dependent case ids for a case id using a SQL query written below.

select distinct dep_case_id from tbl_name where case_id = 1

As per the above example, 1 is dependent on 2. In turn 2 is dependent on 3, 4, 5. And, 3 is dependent on 4, and so on. I want to traverse till the chain related with case id 1 ends (i.e) the dep_case_id must become 0.

The python snippet (written below) is constructing list of lists. My SQL query is expecting only 1 parameter and the list of lists is not satisfying that criteria. As a result it is failing with the below mentioned result.

import pyodbc    
cms_id_list = [1]
for i in cms_id_list:
        cursor.execute('select distinct dep_cms_id from bfs_Test where cms_id = ?', i)
        ids = cursor.fetchall()
        print(ids)
        first_tuple = [a_tuple[0] for a_tuple in ids]
        cms_id_list.append(first_tuple)
        print(cms_id_list)

OUTPUT

[(2, )] #first query result using case id 1...a tuple result
[1, [2]] #cms_id_list after 2 got appended to the list.
[(3, ), (4, )] #second query result using case id 2 (obtained in previous iteration...a tuple result)
[1, [2], [3, 4]] #cms_id_list after 3,4 got appended to the list.

Traceback (most recent call last):
  File "C:\Users\Kris\eclipse-workspace\Utilities\Windows\recursion.py", line 22, in <module>
    cursor.execute('select distinct dep_cms_id from bfs_Test where cms_id = ?', i)
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')

The expected output is cms_id_list = [1, 2, 3, 4, 5].

All i want is to store the dep_case_ids to a cms_id_list which is flattened out. The code I have written won't work and is wrong because list item duplication would happen.

I have read somewhere BFS algorithm is perfect for such scenarios. I don't know how to implement it. Which is why I have implemented this basic code snippet.

I am open to code modification and improvement.

This has nothing to do with algorithms. The code has bugs. 1) it stores both single values and tuples in one container, which means you have to check what you're reading. That's bad. 2) It's modifying a list while iterating it. You can end up reading the same value twice, or miss a value. It's highly inefficient too. SQL is a set-oriented language, it doesn't need loops. If you want to retrieve hierarchical data, use a *single* recursive CTE to retrieve all rows from the root all the way to the branches — Panagiotis Kanavos, Jun 29 '20 at 13:05
BTW recursive CTEs are a standard SQL feature available in most databases, including MySQL 8+ — Panagiotis Kanavos, Jun 29 '20 at 13:12
@PanagiotisKanavos I have answered my own question based on your suggestion. Thank you for the suggestion :) — CK5, Jun 29 '20 at 13:48

score 0 · Answer 1 · answered Jun 29 '20 at 13:47

I have implemented SQL CTE based on @Panagiontis Kanavos.

This is the query.

with qcte (cms_id, dep_cms_id, [Level])
    as
    (
        select cms_id, dep_cms_id, 1 from bfs_Test where cms_id = 1

        union all

        select bfs_Test.cms_id, bfs_Test.dep_cms_id, qcte.[Level] + 1 from bfs_Test join qcte on bfs_Test.cms_id = qcte.dep_cms_id
    )
    select distinct * from qcte

It has worked. Now I am able to see the hierarchy of the tree.

Recursing through heirarchy in a dataset

1 Answers1