MYSQL total sales for product based on multiple level products

Question

Looking to determine total sales of a specific product. The product may or may not be part of a parent product. For example, a product may be included in a set of parent product which could in turn be included in a parent product which in turn... etc...

There are actually only 2 tables that need to be referenced:

Orders_Products

orders_products_id
products_id
products_quantity

Layered_Products

parent_id
subproduct_id
subproduct_qty

The table contents that we've used are as follows:

Orders_Products

orders_products_id | products_id | products_qty 
-------------------+-------------+--------------
      8922         |     7232    |      1       
      8711         |     6823    |      2       
      8658         |     6823    |      1      
      8633         |     6823    |      2       
      8702         |     7538    |      1       
      8690         |     7538    |      1      
      8622         |     7538    |      2       

Layered_Products

parent_id + subproduct_id + subproduct_qty +
----------+---------------+----------------+
  6823    +      7232     +        1       +
  7538    +      6823     +        4       +

This process can be accomplished by using individual queries.

SELECT IFNULL(SUM(products_qty),0) AS total_qty 
FROM orders_products 
WHERE products_id = '7232';

Query Result = total_qty = 1

SELECT lp.parent_id, lp.subproduct_qty, 
       IFNULL(SUM(op.products_qty) * lp.subproduct_qty,0) AS total_qty 
FROM layered_products lp 
LEFT JOIN orders_products op ON op.products_id = lp.parent_id 
WHERE lp.subproduct_id = '7232';

Query Result = parent_id = 6823; subproduct_qty = 1; total_qty = 5

SELECT lp.parent_id, lp.subproduct_qty, 
       IFNULL(SUM(op.products_qty) * lp.subproduct_qty * [prior select subproduct_qty],0) AS total_qty 
FROM layered_products lp 
LEFT JOIN orders_products op ON op.products_id = lp.parent_id 
WHERE lp.subproduct_id = [prior select parent_id];

Query Result = parent_id = 7538; subproduct_qty = 4; total_qty = 16

Sum of all queries = total_qty = 22

However, I'm trying to get everything into one SELECT. Any of you SQL gurus have an idea how to accomplish this? Been working on it for over a week now and can only get one level working. The second parent level isn't working at all. Tried subqueries, derived queries, and anything else I could find. Also, would like to be able to handle additional levels if possible. Thanks in advance...

Answer 1

MySQL does not support recursive queries, therefore there is no clean way to do this.

You could use a stored proc, but it is likely to be slow.

EDIT: Most convenient way to do it off the top of my mind... is just like how a DB does "WITH RECURSIVE" queries...

Start: We have a list "L" which contains the elements of which we want to list children.

SELECT child_id WHERE parent_id IN (L)

...results go into list L2...

SELECT child_id WHERE parent_id IN (L2)

...etc... keep going until query returns nothing.

Cumbersome and annoying, but well, it's the only way. Do it in a stored proc on in the app... Considering the slow speed of MySQL stored procs, and the fact a stored proc would have to use a temp table, I'm not sure the stored proc would actually have any performance advantage.

However there is another way. I used that, can't remember where...

Give each element a path made of the ids of its parents. For example:

relation table:
parent_id    child_id
12           34
34           567

objects table (products here, but could be anything)
obj_id   path
12       00000012
34       00000012/00000034
567      00000012/00000034/00000567

Now, if the path is generated by a trigger, you can keep paths up to date, although moving one element which has lots of children to a different parent will need updating all its children paths, so may be slow.

Now, getting the children of an element is done by :

path LIKE '00000012/%'

...and this is indexable if you have a btree on path. It is super fast.

However you will need some post-processing in your case, so there would be a little bit of a headache. Also, it denormalizes, and is kinda ugly. Your choice!