I am having trouble formulating a query to meet the following criteria (have a basic CTE query working) but am thinking I am missing/overlooking something and am hoping SO can help (much appreciated in advance!). I've tried looking at some of the other SO posts about recursive CTE's, but unfortunately don't feel like I've been able to answer my question(s) while following along with those posts.
Given a categories table with the following columns:
`id` int - primary key
`limit_to` text[] - array of text / is nullable
`parent_id` int - references `id` / is nullable
with values:
id | limit_to | parent_id
-------------------------
1 | {'tc'} | null
-------------------------
2 | null | 1
-------------------------
3 | null | 2
-------------------------
4 | null | null
-------------------------
5 | {'ta'} | null
I am trying to write a query that will filter out records (parent/child associated) having limit_to IS NOT NULL. In this instance, I would expect rows 1,2,3 and 5 to be filtered out, as 1 has a limit_to value of {'tc'} and its descendants are 2 and 3, respectively. Row 5 also has a value that isn't null {'ta'}. So in this case only row 4 would be returned in the final select.
Query I've tried so far:
with recursive tree as (
(
select
*
from
categories
where
limit_to is null
)
union all
(
select
categories.*
from
categories
inner join tree on tree.id = categories.parent_id
where
tree.limit_to is null
)
)
select
id,
parent_id
from
categories
where
id in (
select
id
from
tree
)
and limit_to is null
order by
id asc
However with this example query, I am still seeing the descendants of row 1 (2 and 3), even though they are recursive children on parent_id = 1, which is being filtered out because its limit_to record has a value. I have a feeling I am missing placing an additional WHERE filter expression, but am not entirely sure on where that is.
I was also working on a query to match where limit_to IS NULL OR '''tc''' = ANY(limit_to) to match on records only having that criteria (so in this case row 5 would be eliminated/not returned, and the other records would return), but I have a feeling I can't get that query working fully until I understand what condition I am missing for filtering entirely on where limit_to IS NULL and ensuring child rows are properly filtered out.
I greatly appreciate any insight on what I might be missing / any pointers ( but not null :P ) in the right direction on how to get the queries properly filtering as I would expect based on what I describe above. Thank you!
As you walk the tree recursively, you need to keep some sort of flag which denotes whether or not an instance of categories.limit_to is not null has been seen in any parent records to the current record. This flag is updated at every join to be true if the new record has categories.limit_to is not null. Then, when you query from the cte, you can simply scan for records containing a flag of false:
with recursive cte as (
select c.*, c.limit_to is not null f from categories c where c.parent_id is null
union all
select c.*, c1.f or c.limit_to is not null f from cte c1
join categories c on c.parent_id = c1.id
)
select id, limit_to, parent_id from cte where not f