Recursive query with hasMany relationship

Question

The table representation is a simplification of the actual problem, because it captures the situation but is easier to understand.

Two tables hold the definitions of units of measurement. There is a table with all the unit symbols and a has-many related table that holds the defining segments of all units. For example,

• it defines a yard as 0.9144 meter
• it defines a chain as 22 yards
• it defines a furlong as 220 yards
• it defines an acre as 1 chain times 1 furlong

Tables:

measurement_units
id      name        is_base_unit
----------------------------------
1       meter       1
2       yard        0
3       chain       0
4       furlong     0
5       acre        0


measurement_unit_derivations
id      measurement_unit_id     derives_from_measurement_unit_id    factor
---------------------------------------------------------------------------
1       2                       1                                   0.9144
2       3                       2                                   22
3       4                       2                                   220
4       5                       3                                   1
5       5                       4                                   1

I am trying to write a recursive common table expression where the ratios for each unit of measurement are calculated relative to the base unit for the relevant physical dimension. The result should e.g. contain the ratio of yard to meter and the ratio of acre to m^2. It should look like this:

measurement_unit_id     name        ratio
-------------------------------------------
1                       meter       1
2                       yard        0.9144
3                       chain       20.1168
4                       furlong     201.168
5                       acre        4046.8564224

The problem is that a unit can be derived from multiple other units. For example, in order to find out the ratio of one acre to m^2, we will see that it is derived from one furlong and one chain, which in turn are derived from other units as well.

I fail to proceed beyond:

WITH RECURSIVE cte AS (
    SELECT 
        id AS measurement_unit_id
        name,
        1 AS ratio
    FROM measurement_units
    WHERE is_base_unit = 1
    UNION ALL
    ???
)

Edit: the example tables about meters and acres is conveniently descriptive but is also 'too simple'. I have created another example where the recursive query should also work:

measurement_units
id      name        is_base_unit
----------------------------------
1       A           1
2       B           1
3       C           0
4       D           0
5       E           0

measurement_unit_derivations
id      measurement_unit_id     derives_from_measurement_unit_id    factor
---------------------------------------------------------------------------
1       3                       1                                   2
2       4                       2                                   2
3       4                       3                                   2
4       5                       4                                   2

This result is the goal:

measurement_unit_id     name        ratio
-------------------------------------------
1                       A           1
2                       B           1
3                       C           2
4                       D           8
5                       E           16

Here is the quick and dirty SQL to create these two tables and their contents.

CREATE TABLE `measurement_units` (`id` int(10) UNSIGNED NOT NULL, `name` varchar(190) NOT NULL, `is_base_unit` tinyint(3) UNSIGNED NOT NULL);
CREATE TABLE `measurement_unit_derivations` (`id` int(10) UNSIGNED NOT NULL, `measurement_unit_id` int(10) UNSIGNED NOT NULL, `derived_from_measurement_unit_id` int(10) UNSIGNED NOT NULL, `factor` int(10) UNSIGNED NOT NULL);

INSERT INTO `measurement_units` (`id`, `name`, `is_base_unit`) VALUES (1, 'A', 1), (2, 'B', 1), (3, 'C', 0), (4, 'D', 0), (5, 'E', 0);
INSERT INTO `measurement_unit_derivations` (`id`, `measurement_unit_id`, `derived_from_measurement_unit_id`, `factor`) VALUES (1, 3, 1, 2), (2, 4, 2, 2), (3, 4, 3, 2), (4, 5, 4, 2);

Answer 1

You've got the recursive seed set correctly. We start at base_unit = 1. The next section after UNION ALL is the recursive term. This is where you refer back to the cte and join it to your source tables establish the relationship for the iterations.

Your recursive term will take the form:

SELECT 
    mu.id,
    mu.name,
    cte.ratio * mud.factor
FROM
    cte
    INNER JOIN measurement_unit_derivations mud
        ON cte.measurement_unit_id = mud.derives_from_measurement_unit_id
    INNER JOIN measurement_units mud
        ON mud.measurement_unit_id = mu.id

This will create multiple records for measurements that are derived from two or more dimensions. Since the rule appears to be that we multiply multiple derived measurements together to determine the ratio (chains * furlows = acres) then we can aggregate after this recursive cte is done.

The trick is that there is no multiplication aggregate like there is addition (SUM()). So we have to math it out. I believe the following would work:

SELECT measurement_unit_id, name, EXP(SUM(LOG(ratio))) FROM cte GROUP BY measurement_unit_id, name;

Putting this all together:

WITH RECURSIVE cte AS (
    SELECT 
        measurement_unit_id
        name,
        1 AS ratio
    FROM measurement_units
    WHERE base_unit = 1
    UNION ALL
    SELECT 
        mu.id,
        mu.name,
        cte.ratio * mud.factor
    FROM
        cte
        INNER JOIN measurement_unit_derivations mud
            ON cte.measurement_unit_id = mud.derives_from_measurement_unit_id
        INNER JOIN measurement_units mud
            ON mud.measurement_unit_id = mu.id
)
SELECT measurement_unit_id, name, EXP(SUM(LOG(ratio))) FROM cte GROUP BY measurement_unit_id, name;