MySQL recursive query for finding all parents of a many-to-many relationship

Question

Edit Please consider this before marking as duplicate

The common CTE or repeated joins or similar solutions does not fully satisfy the premise of the problem. Those solutions work for one and only one root (product) while the question at hand, asks for all roots within a table or query to be traversed and flattened, without a loop.

Problem Definition

I have three tables that define the characteristics of some products:

specifications

+----|------------------|----------|--------+
| id |             name |     type | status |
|----|------------------|----------|--------|
|  1 |           height |    float |      0 |
|  2 |            width |    float |      0 |
|  3 |           length |    float |      0 |
|  4 |           weight |    float |      0 |
|  5 |           colour |      int |      0 |
|  6 |         material |      int |      0 |
|  7 |     manufacturer |      int |      0 |
|  8 |       durability |    float |      0 |
|  9 |     battery_type |      int |      0 |
| 10 | battery_capacity |    float |      0 |
| 11 |     connectivity | set<int> |      0 |
| 12 |             page |      int |      0 |
| 13 |             name |   string |      0 |
| 14 |      description |   string |      0 |
+----|------------------|----------|--------+

groups

+----|-------------------|--------+
| id |              name | status |
|----|-------------------|--------|
|  1 |         cellphone |      0 |
|  2 |          notebook |      0 |
|  3 |          portable |      0 |
|  4 |       workstation |      0 |
|  5 |                pc |      0 |
|  6 |          computer |      0 |
|  7 | electronic_device |      0 |
|  8 |              book |      0 |
|  9 |          sizeable |      0 |
| 10 |         volumable |      0 |
| 11 |           general |      0 |
+----|-------------------|--------+

specification_groups

+----|----------|------------------|--------+
| id | group_id | specification_id | status |
|----|----------|------------------|--------|
|  1 |       11 |               13 |      0 |
|  2 |       11 |               14 |      0 |
|  3 |       11 |                5 |      0 |
|  4 |       10 |                1 |      0 |
|  5 |        9 |                2 |      0 |
|  6 |        9 |                3 |      0 |
|  7 |        8 |               12 |      0 |
|  8 |        3 |                6 |      0 |
|  9 |        3 |                9 |      0 |
| 10 |        3 |               10 |      0 |
| 11 |        7 |                7 |      0 |
| 12 |        7 |               11 |      0 |
+----|----------|------------------|--------+

group_groups

+----|----------|--------------------|--------+
| id | group_id | group_reference_id | status |
|----|----------|--------------------|--------|
|  1 |        3 |                  1 |      0 |
|  2 |        3 |                  2 |      0 |
|  3 |        3 |                  8 |      0 |
|  4 |        6 |                  4 |      0 |
|  5 |        6 |                  5 |      0 |
|  6 |        7 |                  1 |      0 |
|  7 |        7 |                  2 |      0 |
|  8 |        7 |                  4 |      0 |
|  9 |        7 |                  5 |      0 |
| 10 |        9 |                  7 |      0 |
| 11 |        9 |                  8 |      0 |
| 12 |       10 |                  7 |      0 |
| 12 |       11 |                  7 |      0 |
| 12 |       11 |                  8 |      0 |
+----|----------|--------------------|--------+

product_groups

+----|--------|-------|--------+
| id |   name | group | status |
|----|--------|-------|--------|
|  1 | phone1 |     1 |      0 |
|  2 |  book1 |     8 |      0 |
+----|--------|-------|--------+

Ideally, I want to get all specification attributes for a product which the status along all tree-lines would be 0, but just to know what groups does a product is in is acceptable.

A result may look like this:

result

+---------|-------------|--------------|-------------------|----------|--------+
| row_num |  product_id | product_name |     product_group | group_id | status |
|---------|-------------|--------------|-------------------|----------|--------|
|       1 |           1 |       phone1 |         cellphone |        1 |      0 |
|       2 |           1 |       phone1 |          portable |        3 |      0 |
|       3 |           1 |       phone1 | electronic_device |        7 |      0 |
|       4 |           1 |       phone1 |           sizable |        9 |      0 |
|       5 |           1 |       phone1 |         volumable |       10 |      0 |
|       6 |           1 |       phone1 |           general |       11 |      0 |
|       7 |           2 |        book1 |              book |        8 |      0 |
|       8 |           2 |        book1 |          portable |        3 |      0 |
|       9 |           2 |        book1 |           sizable |        9 |      0 |
|      10 |           2 |        book1 |           general |       11 |      0 |
+---------|-------------|--------------|-------------------|----------|--------+

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Sep 03 '19 at 10:47
@RaymondNijland It's actually a bit different because this incorporates a join over another table (rather the child-parent relationship table) and is not for a particular product, but all products in `product_groups` in one query. the main goal here is to avoid looping through `product_groups` — Tala, Sep 03 '19 at 10:50
@RaymondNijland I have tried those solutions. The fact is on a join, whether 2 recursive CTE is defined or using a subquery, the root is not distinguished in the result of the join. I have many queries that do achieve at least some part of the solution but either lack connectivity with root or cross join results which make them incorrect. Sadly I cannot share those because the problem I defined here is an analogy for what I am actually trying to achieve. — Tala, Sep 03 '19 at 11:35
*" It's actually a bit different because this incorporates a join over another table (rather the child-parent relationship table) "* In methodes are in the duplicated all methods do or simulate recursion to do child-parent relationships... The main problem is if you don't provide MySQL version we can't help you better -> `SELECT VERSION();` .. also if you don't use MySQL 8 consider upgrading as this problem is much more easy solved on MySQL 8 — Raymond Nijland, Sep 03 '19 at 11:35
@RaymondNijland Oh, I see! I'm using MySQL Community 8.0.17 for production and MariaDB Community 10.3.11 for development — Tala, Sep 03 '19 at 11:38
Offtopic: Ok note to that never use different RDMS vendor software for production and development... As that is asking for trouble even running different minor versions for example 5.6 and 5.7 might be trouble if the vendor software is the same.. As optimizers work different between versions you cant predict annymore what happens the performance might be good in localhost but worst on production because the optimizer chooses a more worst plan assumming the data size is the same offcource.. — Raymond Nijland, Sep 03 '19 at 11:44

score 0 · Accepted Answer · answered Sep 12 '19 at 14:12

Here's the answer if anyone else wants to do the same. The trick was to use the non-recursive initial selection parameters as constants in the recursive one:

    with recursive 
        q as (
            select * from product_groups as pg where ur.`status` = 0 
        )
        , ancestors as (
            (
                select q.id as `product_id`, q.product_name as `product_name`, gg.group_id as `group_id`, gg.group_reference_id as `group_reference_id`
                from group_groups as gg
                    join q on gg.group_id = q.group_id
                where gg.`status` = 0
            )
            union
            (
                select q.id as `product_id`, q.product_name as `product_name`, null as `group_id`, q.group_id as `group_reference_id`
                from q
            )
            union
            (
                select a.id as `product_id`, a.product_name as `product_name`, gg.group_id as `group_id`, gg.group_reference_id as `group_reference_id`
                from group_groups as gg, ancestors as a
                where gg.group_id = a.group_reference_id and gg.`status` = 0
            )
        )
    select distinct
        t.id as `product_id`, t.`group_id` as `group_id`, t.specification_id as `specification_id`, t.product_name as `product_name`, t.group_name as `group_name`, s.name as `specification_name`
    from 
    (
        select a.product_id, a.group_reference_id as `group_id`, sg.specification_id as `specification_id`, a.product_name, g.name as `group_name`
        from ancestors as a
            right join specification_groups as sg on a.group_reference_id = sg.group_id
        where a.product_id is not null and sg.`status` = 0
    ) as t
        join specifications as s on t.specification_id = s.id
    order by product_id asc, group_id, asc, specification_id asc
    ;

MySQL recursive query for finding all parents of a many-to-many relationship

1 Answers1