0

This is a bit of a Brain Twister!

Using Postgres, I am trying to recursively calculate the average progress values of all descendants items. Parent Items have a progress calculation of PARENT and children have a progressCalculation of CHILD; however, children can also be parents having a progressCalculation of PARENT and in turn have their own children with progressCalculation of CHILD and so on and so forth until there are no more descendants.

Setup:

  1. Create Item Table In Postgres
CREATE TABLE item (
  id INT,
  title VARCHAR(255),
  progress INT,
  "progressCalculation" VARCHAR(255),
  "parentItemId" INT
);
  1. Insert Items
insert into item (id,title,progress,"progressCalculation","parentItemId") 
values (1,'Item 1',NULL,'PARENT',NULL),
       (2,'Item 1.1',100,'CHILD',1), 
       (3,'Item 1.2',100,'CHILD',1), 
       (4,'Item 1.3',100,'CHILD',1), 
       (5,'Item 1.4',NULL,'PARENT',1), 
       (6,'Item 1.4.1',50,'CHILD',5), 
       (7,'Item 1.4.2',50,'CHILD',5);

What I've tried

select 
    id,
    title,
    CASE
        when "progressCalculation" = 'PARENT'
        then (select round(avg(COALESCE(progress,0))) from item i where item."id" = i."parentItemId")
        else progress   
    END as progress,
    "progressCalculation",
    "parentItemId"
from 
    item;

this only works for one level deep! it should be recursive no matter how many levels it needs to go.

I really need help setting up a CTE (Common Table Expression) using RECURSIVE query.

The output should look like the following: enter image description here

Essentially Item 1 is the GRANDPARENT, Item 1.4 is PARENT and items 1.4.1 and 1.4.2 are CHILDREN (grandchildren)

Any help would be GREATLY appreciated!

Rick
  • 31
  • 3
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Sep 12 '22 at 15:19

0 Answers0