How to find the need of materials from nested estimates in Postgres

Question

Product estimates contain sub-products. Sub-products can contain also sub-products etc. Finally tree leafs contians materials. Maximum nesting level is 10.

Orders contain also products, sub-products and materials with ordered quantities. How to find the need of materials required to fullfill the orders?

Products, sub-products and materials are in single table:

create table toode (productid char(10) primary key );

Estimate table:

create table dok (
  dokumnr serial primary key,
  productid char(10) not null references toode
);

Sub-products and materials in estimates:

create table rid (
  id serial primary key,
  dokumnr int not null references dok,
  itemid char(10) not null references toode,
  quantity  numeric(12,4) -- quantity required to make one product
);

Orders:

create table orderrows (
  id serial primary key,
  itemid char(10) not null references toode,
  quantity  numeric(12,4)  -- ordered quantity
);

Result should be query which return the need of materials and sub-products:

itemid char(10) not null references toode,
requiredquantity  numeric(12,4) -- total quantity of items required to make ordered products

How to implement this in Postgresql 9.2? Described fields should remain in those tables. It is possible to add additional columns and tables if this helps. Is it possible to make some universal query which works with unilimited nesting level. Or is it best way to create query which repeats some parts 10 times for maximum nensting level ?

Update

estimates

product1
  material1  2 pcs
  subproduct2  3 pcs


subproduct2
  material2 4 pcs

are described as

insert into dok values (1,'product1');
  insert into rid (dokumnr, itemid, quantity) values (1, 'material1', 2);
  insert into rid (dokumnr, itemid, quantity) values (1, 'subproduct2', 3);

insert into dok values (2,'subproduct2');
  insert into rid (dokumnr, itemid, quantity) values (2, 'material2', 4);

If 10 pieces of product1 are ordered this is described as:

insert into orderrows (itemid, quantity ) values ('product1', 10);

Result should be:

material1  20 
material2  120

material1 quantity is calculated as 10*2.

material2 quantity is calculated as 10*3*4

Update 2

Joachim answer gives incorrect result on multi level estimates when last level contains more that one row. Last join LEFT JOIN rid rid2 ON rid2.dokumnr = dok2.dokumnr returns multiple rows and result table is duplicated.

Testcase http://sqlfiddle.com/#!12/e5c11/1/0 :

create table toode (productid char(15) primary key );

create table dok (
  dokumnr serial primary key,
  productid char(15) not null references toode
);

create table rid (
  id serial primary key,
  dokumnr int not null references dok,
  itemid char(15) not null references toode,
  quantity  numeric(12,4) -- quantity required to make one product
);

create table orderrows (
  id serial primary key,
  itemid char(15) not null references toode,
  quantity  numeric(12,4)  -- ordered quantity
);

INSERT INTO toode VALUES ('product1'),('material1'),('subproduct2'), ('material2'), ('material3');
insert into dok values (1,'product1');
insert into dok values (2,'subproduct2');

insert into rid (dokumnr, itemid, quantity) values (1, 'material1', 1);
insert into rid (dokumnr, itemid, quantity) values (1, 'subproduct2', 1);
insert into rid (dokumnr, itemid, quantity) values (2, 'material2', 1);
insert into rid (dokumnr, itemid, quantity) values (2, 'material3', 1);

insert into orderrows (itemid, quantity ) values ('product1', 1);

Expected:

Every quantity is 1 so result quantity must be 1 for every material.

Observed:

Material2 and matererial3 rows are duplicated.

How to fix this ? Query should determine leaf nodes itself. Leaf nodes are not marked specially in data.

Answer 1

This should do it using a recursive query;

WITH RECURSIVE t(itemid,qty) AS (
  SELECT itemid,quantity,false isleaf FROM orderrows
  UNION ALL
  SELECT rid.itemid,(rid.quantity*t.qty)::NUMERIC(12,4),
         dok2.productid IS NULL
  FROM t 
  JOIN dok ON dok.productid=t.itemid
  JOIN rid ON rid.dokumnr=dok.dokumnr
  LEFT JOIN dok dok2 ON dok2.productid=rid.itemid
)
SELECT itemid, SUM(qty) FROM t WHERE isleaf GROUP BY itemid

An SQLfiddle to test with.

Answer 2

Try this query:

;with recursive cte as (
    select r.itemid, r.quantity * o.quantity as quantity, false as is_material
    from orderrows as o
        inner join dok as d on d.productid = o.itemid
        inner join rid as r on r.dokumnr = d.dokumnr

    union

    select r.itemid, r.quantity * o.quantity as quantity, itemid like 'material%'
    from cte as o
        inner join dok as d on d.productid = o.itemid
        inner join rid as r on r.dokumnr = d.dokumnr
)
select * from cte as c
where c.itemid not in (select t.productid from dok as t);

here's SQL FIDDLE example to test it. Here I'm assuming that your define materials as products which name starts with 'material', but I think that you should have an attribute is_material or something like that in your DB, so you could change this condition.

update - test case sql fiddle