sql - Select on self-referencing table where all chains meet a condition - Stack Overflow

admin管理员组

文章数量:1313347

I need some help with PostgreSQL CTEs. I have a task to get entities from postgre with some interesting conditions:

Self-referencing table, let it be called ordr(ordr_id bigint, parent_ordr_id bigint, is_terminated boolean).

Need to get ordr (basically a flat list of orders) which are met the condition is_terminated = true. But if any entity from chain have is_terminated = false full chain shouldn't be in result.

For example

INSERT INTO ordr_tst.ordr (id,parent_id, is_terminated) VALUES  
    (0, NULL, true),  
    (-1,NULL,true),  
    (-2,-1,true),  
    (-3,-2,true),  
    (-11,NULL,false),  
    (-12,-11,true),  
    (-13,-12,true),  
    (-21,NULL,true),  
    (-22,-21, false),  
    (-23,-22, true),  
    (-31,NULL, true),  
    (-32,-31, false),  
    (-33,-32, true),  
    (-34,-32, true),
    (-41,NULL, true),
    (-42,NULL, true),
    (-43,NULL, false);

The result should be: entities with ids 0, -1, -2, -3

My approach on this only works for the assumption that parent ordrs are always terminated only after child ordrs but unfortunately, it's not true in my case.

WITH RECURSIVE r AS (  
    SELECT o.ordr_id as id  
    FROM   ordr_tst.ordr o  
    WHERE  o.parent_ordr_id is null  
           AND o.is_terminated = true

    UNION

    SELECT o.ordr_id as id  
    FROM   ordr_tst.ordr o  
    JOIN   r ON o.parent_ordr_id = r.id
    WHERE  o.is_terminated = true  
)  
SELECT * FROM ordr.ordr o WHERE o.id in (SELECT r.id FROM r);

I tried some obviously not working stuff like self-joining CTE results.

Making arrays in CTE like

...  
select array[o.ordr_id]  
...  
UNION  
select array[o.ordr_id] || cte.id
...

And I was trying to add second CTE but my brain started throttling.

I need some help with PostgreSQL CTEs. I have a task to get entities from postgre with some interesting conditions:

Self-referencing table, let it be called ordr(ordr_id bigint, parent_ordr_id bigint, is_terminated boolean).

Need to get ordr (basically a flat list of orders) which are met the condition is_terminated = true. But if any entity from chain have is_terminated = false full chain shouldn't be in result.

For example

INSERT INTO ordr_tst.ordr (id,parent_id, is_terminated) VALUES  
    (0, NULL, true),  
    (-1,NULL,true),  
    (-2,-1,true),  
    (-3,-2,true),  
    (-11,NULL,false),  
    (-12,-11,true),  
    (-13,-12,true),  
    (-21,NULL,true),  
    (-22,-21, false),  
    (-23,-22, true),  
    (-31,NULL, true),  
    (-32,-31, false),  
    (-33,-32, true),  
    (-34,-32, true),
    (-41,NULL, true),
    (-42,NULL, true),
    (-43,NULL, false);

The result should be: entities with ids 0, -1, -2, -3

My approach on this only works for the assumption that parent ordrs are always terminated only after child ordrs but unfortunately, it's not true in my case.

WITH RECURSIVE r AS (  
    SELECT o.ordr_id as id  
    FROM   ordr_tst.ordr o  
    WHERE  o.parent_ordr_id is null  
           AND o.is_terminated = true

    UNION

    SELECT o.ordr_id as id  
    FROM   ordr_tst.ordr o  
    JOIN   r ON o.parent_ordr_id = r.id
    WHERE  o.is_terminated = true  
)  
SELECT * FROM ordr.ordr o WHERE o.id in (SELECT r.id FROM r);

I tried some obviously not working stuff like self-joining CTE results.

Making arrays in CTE like

...  
select array[o.ordr_id]  
...  
UNION  
select array[o.ordr_id] || cte.id
...

And I was trying to add second CTE but my brain started throttling.

• sql
• postgresql

Share Improve this question Follow edited Jan 31 at 14:18 Chait 1,31522 gold badges2222 silver badges3737 bronze badges asked Jan 31 at 12:02 DayDreamDayDream 333 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

The following query first finds all bad rows and their children, calculates the rest by eliminating these rows and then applies your recursive CTE to the rest:

WITH RECURSIVE bad AS (
   SELECT o.id, o.parent_id
   FROM ordr_tst.ordr AS o
   WHERE NOT o.is_terminated
   UNION ALL
   SELECT o.id, o.parent_id
   FROM ordr_tst.ordr AS o
      JOIN bad ON o.id = bad.parent_id
), rest AS (
   SELECT o.id, o.parent_id, o.is_terminated
   FROM ordr_tst.ordr AS o
   WHERE NOT EXISTS (SELECT FROM bad
                     WHERE bad.id = o.id)
), r AS (
   SELECT rest.id
   FROM rest
   WHERE rest.parent_id IS NULL
     AND rest.is_terminated
   UNION
   SELECT rest.id
   FROM rest
      JOIN r ON rest.parent_id = r.id
   WHERE rest.is_terminated
)
SELECT * FROM ordr_tst.ordr AS o
WHERE EXISTS (SELECT FROM r WHERE o.id = r.id);

本文标签： sqlSelect on selfreferencing table where all chains meet a conditionStack Overflow