sqlmariadbunioncommon-table-expressionhierarchical-data

How to UNION product_groups and specifications of different hierachy level to deepest one?


Scenario - All valid classes / product groups and their valid specifications for all products in them are to be determined.

  • Products have different specifications / features
  • Products always hang on the lowest hierarchy level in the tree of product groups.
  • Specifications can be attached to different levels, in which case it can be assumed that they are only attached to the highest (very general) and the lowest level (rather very special dimensions, pressure specifications, ...).

Since the products are linked at the lowest level, it would be simplest, in my view, to combine all the specifications existing at the upper levels in the path at the lowest level.


product_groups
id parent_id
1 NULL
2 1
3 2
4 3
5 NULL
6 5
7 6
8 7
specification_to_group
id product_group_id specification_id
1 1 1
2 1 2
3 4 10
4 4 11
5 4 12
6 5 1
7 5 3
8 7 12
result
product_group_id specification_id
4 1
4 2
4 10
4 11
4 12
8 1
8 3
8 12

One approach would be to try to use help tables and be less generic.

SELECT
    lst.id AS lst,
    up1.id AS up1,
    up2.id AS up2,
    up3.id AS up3,
    up4.id AS up4
FROM
    product_groups lst 
        LEFT JOIN
    product_groups up1 ON lst.parent_id = up1.id
        LEFT JOIN
    product_groups up2 ON up1.parent_id = up2.id
        LEFT JOIN
    product_groups up3 ON up2.parent_id = up3.id
        LEFT JOIN
    product_groups up4 ON up3.parent_id = up4.id
WHERE
    lst.id NOT IN
    (SELECT DISTINCT parent_id
        FROM product_groups
        WHERE parent_id IS NOT NULL
)
        
SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.lst = s.product_group_id
               UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up1 = s.product_group_id
UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up2 = s.product_group_id
UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up3 = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up4 = s.product_group_id;
;

Since there are no permissions for creating tables and also no other option to persist the data already prepared, I would think about a CTE.


Solution

  • This is a classic opportunity to use recursive CTE

    WITH recursive  CTE AS(
    SELECT `id`, `parent_id`, ROW_NUMBER() OVER (ORDER By id) rn, 1 lvl
    FROM  product_groups
    WHERE `parent_id` IS NULL
      UNION ALL
    
      SELECT P1.id, P1.parent_id , M.rn, M.lvl+1 AS LVL
      FROM product_groups P1  
      INNER JOIN CTE M
      ON M.id = P1.parent_id),
      getmax as(
    SELECT `id`, MAX(id) OVER(PARTITION BY rn) m FROM CTE
    ORDER BY rn)
    SELECT sp.id, g.m, sp.`specification_id`
      FROM specification_to_group sp JOIN getmax g ON sp.`product_group_id` = g.id
    ORDER By sp.id
    
    id m specification_id
    1 4 1
    2 4 2
    3 4 10
    4 4 11
    5 4 12
    6 8 1
    7 8 3
    8 8 12

    fiddle