PostgreSQL Recursive CTE - Return Empty if false value found

user_2951367 0
Viewed 49

Using a recursive query, I'm querying for all ancestors of a field using a parent id field. If a false value is found in the enabled field for any ancestor, then the query should return empty rows.

Say I have the following table called my_table

Field Type
id integer
parent integer
enabled boolean

It's populated with the following data:

id parent enabled
1 null true
2 1 true
3 1 true
4 2 true

I have the following recursive query:

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    
    UNION ALL

    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)

SELECT *
FROM my_rec

For id = 4 this correctly returns

id parent enabled
4 2 true
2 1 true
1 null true

But say I were to update id 2 so that it's changed from enabled true to false. Example:

id parent enabled
1 null true
2 1 false
3 1 true
4 2 true

Now I want my query to return for id = 4 nothing. This is because in the "ancestry" tree of id 4, enabled is false for id 2.

How can I update my query above to achieve this?

postgresql sql
1 Answers

If I understand correctly you can try to use NOT EXISTS subquery to judge cte if any enabled is false didn't show anything as your expect, otherwise show all of them.

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    UNION ALL
    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)
SELECT *
FROM my_rec
WHERE NOT EXISTS (
   SELECT 1
   FROM my_rec
   WHERE enabled = false
)

or we can use COUNT condition aggregate function to make it, getting false_cnt and judgement.

WITH recursive my_rec AS
(
    SELECT id, parent, enabled
    FROM my_table
    WHERE id = 4
    UNION ALL
    SELECT t.id, t.parent, t.enabled
    FROM my_table t
    INNER JOIN my_rec
    ON t.id = my_rec.parent
)
SELECT *
FROM (
 SELECT *,COUNT(*) FILTER(WHERE enabled = false) OVER() false_cnt
 FROM my_rec
) t1
WHERE false_cnt = 0

sqlfiddle

user_51760710
Related