Finding Top Parent (Recursive)

Question

Product table:

id | id_parent | margin
------------------------
1  | NULL      | 10
2  | 1         | 20
3  | NULL      | 15
4  | 2         | 10
5  | 4         | 25

From data above, how to get margin total of product tree (id: 5)? It doesn't have fixed depth, it is dynamic.

I currently achieve this with iteration from PHP (recursive mysqli_query), but it consumes huge hosting memory (error 508).

Here is the PHP function:

function get_margintotal($product) {
    global $link;

    $query = "SELECT id_parent, margin FROM `products` WHERE id = ".$product['id_parent'];
    if (!$temp_products = mysqli_query($link, $query)) {
        die(json_encode(array("errmsg" => "Selecting supplier's margin.")));
    }
    $temp_product = mysqli_fetch_assoc($temp_products);
    $temp_product['margin'] = $product['margin'] + $temp_product['margin'];

    if (isset($temp_product['id_parent'])) {
        return get_margintotal($temp_product);
    }
    else {
        return $temp_product['margin'];
    }
}

So I'm wondering if dynamic iteration can be done in MySQL or how to optimise the function to decrease server memory usage?

Thank you.

In sql CTE helps, so in mysql probably this post will help https://dba.stackexchange.com/q/7147 — TechGirl, Sep 12 '17 at 08:48
If your PHP code is well-written, there's no reason it should consume huge memory. You might want to ask a separate question to get help with fixing that. — Simba, Sep 12 '17 at 08:48

score 1 · Accepted Answer · answered Sep 12 '17 at 09:28

MySQL solution using recursion with variables.

CREATE TABLE `finding_top_parent_recursive_46170847` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_parent` int(11) DEFAULT NULL,
  `margin` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


INSERT INTO `finding_top_parent_recursive_46170847` (`id`, `id_parent`, `margin`)
VALUES
    (1, NULL, 10),
    (2, 1, 20),
    (3, NULL, 15),
    (4, 2, 10),
    (5, 4, 25);


-- set the seed leaf id
set @id = 5 ;
select sum(margin) margin from (
    -- include the seed 
    select id, margin from finding_top_parent_recursive_46170847 products
    where id = @id
    union all
    -- get the parents
    select @id := ( 
        select id_parent 
        from finding_top_parent_recursive_46170847 products
        where id = @id ) id,
        ( select margin 
        from finding_top_parent_recursive_46170847 products
        where id = @id ) margin
    from 
    finding_top_parent_recursive_46170847 products
    where @id is not null
) margins

-- results
margin
65

Ideas for recursive mysql results from

https://www.google.com.au/search?q=mysql+recursive+left+join

https://dba.stackexchange.com/questions/46127/recursive-self-joins

Hierarchical queries in MySQL

score 0 · Answer 2 · answered Sep 12 '17 at 08:32

0

If you know the maximum depth, let say 5, you can count sum in one query with 4 LEFT JOINS:

SELECT p1.margin + IFNULL(p2.margin, 0) + IFNULL(p3.margin, 0) + IFNULL(p4.margin, 0) + IFNULL(p5.margin, 0)
FROM product AS p1
    LEFT JOIN product AS p2 ON p1.id_parent = p2.id
    LEFT JOIN product AS p3 ON p2.id_parent = p3.id
    LEFT JOIN product AS p4 ON p3.id_parent = p4.id
    LEFT JOIN product AS p5 ON p4.id_parent = p5.id
WHERE p1.id = :givenId

answered Sep 12 '17 at 08:32

icoder

145
2
5

No, It doesn't have fixed depth. It is dynamic. – Jeaf Gilbert Sep 12 '17 at 08:34
At least can run 5 times less queries when you are doing calculations recursively in PHP. A good solution in such case is to use some nested tree structure in your table, check Nested Sets DB Tree [Nested Sets DB Tree](https://www.phpclasses.org/package/2547-PHP-Manipulate-database-records-in-hierarchical-trees.html). – icoder Sep 12 '17 at 08:40

Finding Top Parent (Recursive)

2 Answers2