I’m working on a mini project for warehouse management, and I need to manage the available quantity of items. Sometimes articles can be composed (made up of one or more different articles), which in turn can be composed of others.

Example: I have 100 L of water and 100 1L bottles in stock. I have the ‘Water bottle’ product made up of 1 L of water and 1 one-litre bottle.

I don’t already have bottles in stock, but the theoretical quantity I could have is 100 bottles of water.

I have already created a Mysql table containing basic product information and a table in which I list the products needed and the quantity of them to create another product.

Example of Json data

{
    "products": [
        {"id": 1, "name": "Water", "quantity": 120, "compound": false, "decimal": true},
        {"id": 2, "name": "Bottles", "quantity": 240, "compound": false, "decimal": false},
        {"id": 3, "name": "Small Water Bottle", "quantity": null, "compound": true, "decimal": false},
        {"id": 4, "name": "Water Box", "quantity": null, "compound": true, "decimal": false},
        {"id": 5, "name": "Water Shelf", "quantity": null, "compound": true, "decimal": false}
    ],

    "compound_list": [
        {"id": 1, "compound_id": 3, "requirement_id": 1, "requirement_quantity": 1},
        {"id": 2, "compound_id": 3, "requirement_id": 2, "requirement_quantity": 1},
        {"id": 3, "compound_id": 4, "requirement_id": 3, "requirement_quantity": 6},
        {"id": 4, "compound_id": 5, "requirement_id": 4, "requirement_quantity": 10}
    ]
}

My problem is to create a recursive Query that calculates the quantity of composite products formed by composite products.

I would like to create a MySQL view but I don’t understand how CTE recursion works with with functions.

All other answers didn’t help me clear my doubts.

I created a query that works, but it only works with products made up of prime products

SELECT 
    art.id          AS product_id,
    art.name        AS product_name,
    art.qty         AS product_requirement_qty,
    art.compound
FROM
    articles art
WHERE 
    art.compound = 0

UNION ALL

SELECT 
    art.id                      AS product_id,
    art.name                    AS product_name,
    MIN(
        CASE
            WHEN art.decimal = 1
            THEN ROUND( (art_calc.qty/lc.requirement_qty), 2)
            ELSE FLOOR( (art_calc.qty/lc.requirement_qty) ) 
        END 
    ) AS product_requirement_qty,

    art.compound
FROM
    articles art
    INNER JOIN 
        compound_list lc 
    ON lc.compound_id = art.id

    INNER JOIN
        articles art_calc 
    ON art_calc.id = lc.requirement_id

WHERE 
    art.compound = 1
GROUP BY 
    art.id;

I created a recursive function (not the most efficient in the world) of what I would like to receive in Node.js –> it works

const { performance } = require('perf_hooks');
const { exit } = require('process');

const products = [
    {"id": 1, "name": "Water",  "quantity": 100, "compound": false, "decimal": true},
    {"id": 2, "name": "Bottles", "quantity": 100, "compound": false, "decimal": false},
    {"id": 3, "name": "Small Water Bottle", "quantity": null, "compound": true, "decimal": false},
    {"id": 4, "name": "Water Box", "quantity": null, "compound": true, "decimal": false},
    {"id": 5, "name": "Water Shelf", "quantity": null, "compound": true, "decimal": false}
];

const compound_list = [
    {"id": 1, "compound_id": 3, "requirement_id": 1, "requirement_quantity": 1},
    {"id": 2, "compound_id": 3, "requirement_id": 2, "requirement_quantity": 1},
    {"id": 3, "compound_id": 4, "requirement_id": 3, "requirement_quantity": 6},
    {"id": 4, "compound_id": 5, "requirement_id": 4, "requirement_quantity": 10}
];


function ViewQty(products, compound_list){
    
    const startTime = performance.now();

    var tableQTY = [];

    products.forEach(product => {
        tableQTY.push(
            recursiveFunction(products, product, compound_list)
        );
    });

    const endTime = performance.now();

    console.table(tableQTY);

    
    const elapsedTime = endTime - startTime;
    console.log(`Execution Time: ${elapsedTime}`);
}



function recursiveFunction(products, product, compound_list){
    if(!product.compound){
        return {
            "id": product.id,
            "name": product.name,
            "quantity": product.quantity
        };
    } else {
        var components = getComponents(product.id, compound_list);
        var minQty;

        if(components.length > 0){
            var possibleQtyList = [];
            components.forEach(component => {
                var x = products.find(art => art.id === component.requirement_id);

                var obj = recursiveFunction(products, x, compound_list);

                obj.quantity /= component.requirement_quantity;

                possibleQtyList.push(obj); 
            });

            minQty = Math.min(...possibleQtyList.map(element => element.quantity));
        } else {
            minQty = 0;
        }
        
        if(product.decimal){
            minQty = minQty.toFixed(2);
        } else {
            minQty = Math.floor(minQty);
        }

        return {
            "id": product.id,
            "name": product.name,
            "quantity": minQty
        };
    }
}

function getComponents(id, compound_list){
    
    var miniList = [];

    compound_list.forEach(element => {
        if(element.compound_id === id){
            miniList.push(element);
        }
    });
    
    if(miniList.length === 0){
        return {"requirement_id": null, "requirement_quantity": 0};
    } else {
        return miniList;
    }
}

ViewQty(products, compound_list);

I hope someone can help me, at least to understand how recursion works in mysql