Fusionner les résultats CTE récursifs dans SQL Select secondaire

Question

J'ai une requête SQL complexe qui extrait avec succès le coût des enregistrements de produits composants et calcule le coût global du produit parent/bundle. Cette approche fonctionne lorsque chaque composant a un coût fournisseur et n'est pas lui-même un produit parent/groupé.

Requête n°1

SET @parentid = 36;
SELECT
    sub.product_sku AS product_sku,
    sub.product_label AS product_label,
    c2p.bundle_parentid AS bundle_parentid,
    c2p.componentid AS comp_product_id,
    sub.qty AS qty,
    sub.preferred AS preferred,
    sub.supply_qty AS supply_qty,
    sub.cost AS cost,
    ROUND((SELECT cost)/(SELECT supply_qty),2) AS adjusted_cost
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN (
        /* Get the preferred/cheapest supplier date for this component */
        SELECT
            p2.product_sku AS product_sku,
            p2.product_label AS product_label,
            IFNULL(s2p2.cost, NULL) AS cost,
            s2p2.productid AS product_id,
            s2p2.supplier_preferred AS preferred,
            s2p2.product_quantity AS supply_qty,
            c2p2.componentid AS comp_product_id,
            c2p2.component_quantity AS qty,
            c2p2.bundle_parentid AS bundle_parentid
            FROM products AS p2
                INNER JOIN supplier2product AS s2p2 ON s2p2.productid = p2.product_id
                INNER JOIN component2bundle AS c2p2 ON s2p2.productid = c2p2.componentid
            WHERE c2p2.bundle_parentid = @parentid
                 AND c2p2.c2p_archive = 0
                AND COALESCE(s2p2.s2p_archive,0) = 0
            ORDER BY c2p2.componentid ASC, s2p2.supplier_preferred DESC, s2p2.cost ASC
    ) AS sub
    ON (sub.product_id = c2p.componentid)
    WHERE c2p.bundle_parentid = @parentid;

Mon objectif est de modifier ou de réécrire la requête afin qu'elle élimine le coût de tous les composants groupés, donc une requête CTE récursive semble être la voie à suivre.

J'ai écrit avec succès une requête CTE qui extrait chaque ID de produit de composant d'un tableau montrant une relation parent -> enfant et attribue à chaque composant un niveau dans la hiérarchie. Ce avec quoi j'ai du mal, c'est comment intégrer les deux.

Requête CTE

WITH RECURSIVE components AS 
(
    SELECT componentid, 1 AS level
    FROM component2bundle
    WHERE bundle_parentid = 'target_productID'
    UNION ALL
        SELECT c2b.componentid, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
)
SELECT * FROM components ORDER BY level DESC;

J'ai créé un violon MySQL 8.0 ici pour aider à fournir un meilleur contexte :

https://dbfiddle.uk/M6HT_R13

REMARQUE : j'ai réduit la requête n°1 pour la rendre plus facile à traiter, afin que certains champs du violon puissent être ignorés.

*Edit : définissez la variable parentid dans Fiddle pour voir comment la requête actuelle est extraite :

• 34 => Aucun produit trouvé contenant un composant de sous-bundle (ZWK180)
• 36 =​> Produits avec tous les composants à un seul niveau trouvés. *

Quelques notes supplémentaires.

La

sous-requête dans la requête n°1 est destinée à extraire le coût du fournisseur préféré ou (s'il n'est pas défini)le plus bas de la table supplier2cost, et je ne sais pas comment implémenter cette sous-requête dans le contexte CTE (s'il y en a un) .

Si un contexte supplémentaire est utile, veuillez le demander et je modifierai la requête pour fournir cette information.

Résultat attendu/attendu

SKU du produit	Étiquettes du produit	BundleParentID	ID_composant	Niveau	Quantité	Préféré	Quantité fournie	Coût	Coûts ajustés
Sous-composant #1	CMP#2	36	35	2	1	1	1	Frais	Coût par unité
Sous-composant #2	CMP#3	36	37	2	1	1	1	Frais	Coût par unité
Sous-composant #3	CMP#4	36	38	2	1	1	1	Frais	Coût par unité
Composant n°1	CMP#1	34	33	1	1	1	1	Frais	Coût par unité
Sous forfait	Le Bund #1	36	33	1	1	1	1	Frais	Coût par unité

Les données seront finalement utilisées pour fournir le tableau de coûts des composants suivant :

Answer 1

Vous voudrez peut-être quelque chose comme ceci :

Sur le deuxième cte, ajoutez une condition qui joint votre requête corporelle à la requête récursive pour extraire uniquement la requête sélectionnée

SET @parentid = 34;
WITH RECURSIVE components AS 
(
    SELECT componentid, p.product_sku, 1 AS level
    FROM component2bundle
    JOIN products p ON componentid = p.product_id
    WHERE bundle_parentid = @parentid
    UNION ALL
        SELECT c2b.componentid, product_sku, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
),
CTE AS (
    SELECT
    sub.product_sku AS product_sku,
    sub.product_label AS product_label,
    c2p.bundle_parentid AS bundle_parentid,
    c2p.componentid AS comp_product_id,
    sub.qty AS qty,
    sub.preferred AS preferred,
    sub.supply_qty AS supply_qty,
    sub.cost AS cost,
    ROUND((SELECT cost)/(SELECT supply_qty),2) AS adjusted_cost,
    c.level
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN components c on c.componentid = c2p.componentid
    JOIN (
        /* Get the preferred/cheapest supplier date for this component */
        SELECT
            p2.product_sku AS product_sku,
            p2.product_label AS product_label,
            IFNULL(s2p2.cost, NULL) AS cost,
            s2p2.productid AS product_id,
            s2p2.supplier_preferred AS preferred,
            s2p2.product_quantity AS supply_qty,
            c2p2.componentid AS comp_product_id,
            c2p2.component_quantity AS qty,
            c2p2.bundle_parentid AS bundle_parentid
            FROM products AS p2
                INNER JOIN supplier2product AS s2p2 ON s2p2.productid = p2.product_id
                INNER JOIN component2bundle AS c2p2 ON s2p2.productid = c2p2.componentid
            WHERE c2p2.c2p_archive = 0
                AND COALESCE(s2p2.s2p_archive,0) = 0
            ORDER BY c2p2.componentid ASC, s2p2.supplier_preferred DESC, s2p2.cost ASC
    ) AS sub
    ON (sub.product_id = c2p.componentid)
)
SELECT *
FROM CTE c
WHERE preferred = 1

Démo ici