CODEWITHSUNDEEP
recursionmariadbcommon-table-expression
dafrandle
dafrandle

Reputation: 108

How to write complex recursive maria db query

Im trying to write a recursive query for a use on a old and poorly designed database - and so the queries get quite complex.

Here is the (relevant) table relationships enter image description here

Because people asked - here is the creation code for these tables:


CREATE TABLE CircuitLayout(
    CircuitLayoutID int,
    PRIMARY KEY (CircuitLayoutID)
);

CREATE TABLE LitCircuit (
    LitCircuitID int,
    CircuitLayoutID int,
    PRIMARY KEY (LitCircuitID)
    FOREIGN KEY (CircuitLayoutID) REFERENCES CircuitLayout(CircuitLayoutID)
);

CREATE TABLE CircuitLayoutItem(
    CircuitLayoutItemID int,
    CircuitLayoutID int,
    TableName varchar(255),
    TablePK int,
    PRIMARY KEY (CircuitLayoutItemID)
    FOREIGN KEY (CircuitLayoutID) REFERENCES CircuitLayout(CircuitLayoutID)
);

TableName refers to another table in the database and thus TablePK is a primary key from the specified table

One of the valid options for TableName is LitCircuit

I'm trying to write a query that will select a circuit and any circuit it is related to

I am having trouble understanding the syntax for recursive ctes

my non-functional attempt is this:

WITH RECURSIVE carries AS (
    SELECT LitCircuit.LitCircuitID AS recurseList FROM LitCircuit
    JOIN CircuitLayoutItem ON LitCircuit.CircuitLayoutID = CircuitLayoutItem.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = "LitCircuit" AND CircuitLayoutItem.TablePK IN (00340)
    UNION
    SELECT LitCircuit.LitCircuitID AS CircuitIDs FROM LitCircuit
    JOIN CircuitLayout ON LitCircuit.CircuitLayoutID = CircuitLayoutItem.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = "LitCircuit" AND CircuitLayoutItem.TablePK IN (SELECT recurseList FROM carries)
) 
SELECT * FROM carries;

the "00340" is a dummy number for testing, and it would get replaced with an actual list in usage

What i'm attempting to do is get a list of LitCircuitIDs based on one or many LitCircuitIDs - that's the anchor member, and that works fine.
What I want to do is take this result and feed it back into itself.

I lack an understanding of how to access data from the anchor member: I don't know if it is a table with the columns from the select in the anchor or if it is simply a list of resulting values
I dont understand if or where I need to include "carries" in the FROM part of a query

If I were to write this function in python I would do it like this:

def get_circuits(circuit_list):
    result_list = []
    for layout_item_key, layout_item in CircuitLayoutItem.items():
            if layout_item['TableName'] == "LitCircuit" and layout_item['TablePK'] in circuit_list:
                layout = layout_item['CircuitLayoutID']
                for circuit_key, circuit in LitCircuit.items():
                    if circuit["CircuitLayoutID"] == layout:
                        result_list.append(circuit_key)
    result_list.extend(get_circuits(result_list))
    return result_list

How do I express this in SQL?

Upvotes: 0

Views: 83

Answers (1)

dafrandle
dafrandle

Reputation: 108

danblack's comment made me realize something I was missing:

Here is what I was trying to do:

WITH RECURSIVE carries AS (
    SELECT LitCircuit.LitCircuitID FROM LitCircuit
    JOIN CircuitLayoutItem ON LitCircuit.CircuitLayoutID = CircuitLayoutItem.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = 'LitCircuit' AND CircuitLayoutItem.TablePK IN (00340)
    UNION ALL
    SELECT LitCircuit.LitCircuitID FROM carries
    JOIN CircuitLayoutItem ON carries.LitCircuitID = CircuitLayoutItem.TablePK
    JOIN LitCircuit ON CircuitLayoutItem.CircuitLayoutID = LitCircuit.CircuitLayoutID
    WHERE CircuitLayoutItem.TableName = 'LitCircuit'
)
SELECT DISTINCT LitCircuitID FROM carries;

I did not think of the CTE as a table to query against - rather just a result set, so I did not realize you have to SELECT from it - or in general treat it like a table.

Upvotes: 1

Related Questions