Finding entity's descendants in a tree with an additional condition (PostgreSQL)

Question

I have an entity called category and each of such categories can have an arbitrary number of subcategories. If I know a category id I can track down all its descendants in the following way:

WITH RECURSIVE subcategories AS (
    select id, is_locked_by
    FROM category
    WHERE id = categoryId
    UNION
    SELECT c.id, c.is_locked_by
    FROM category c
    INNER JOIN subcategories subs on subs.id = c."parentCategoryId"
) 
SELECT id FROM subcategories;

Each category has two parameters: is_locked_by and is_locked. The is_locked_by parameter indicates the locking category. For example, if I have a category with an id = 10 and its is_locked_by = 5, it means that the category with id = 10 is locked by the category with id = 5. Technically it means that the category with id = 5 is locked with a password and it contains the category with id 10. The is_locked parameter indicates that the category is locked itself, it means the password is set directly on that category.

----19---
    |
    25
    |
26* | 27 | 28
|
29

This is the category tree that should be understood as follows: category 19 is the top level category, it contains category 25 which contains categories 26, 27 and 28 and category 26 contains the 29th one, the category with id = 26 is locked (indicated with *).

Given categoryId = 19 the result should be 19, 25, 27 and 28. Given categoryId = 25, the result should be 25, 27 and 28. In other words I want to find all descendants for which is_locked = false that are not locked by those categories for which is_locked = true. How do I write such a query?

I hope I was clear enough....

Answer 1

You want to ignore locked categories, and their children as well. You can just filter them out in the WHERE clause of the recursive member:

WITH RECURSIVE subcategories AS (
    select id, is_locked_by
    FROM category
    WHERE id = ?
    UNION ALL
    SELECT c.id, c.is_locked_by
    FROM category c
    INNER JOIN subcategories subs on subs.id = c."parentCategoryId"
    WHERE c.is_locked IS NULL
    ) 
SELECT id FROM subcategories;

Depending on the datatype and values of the is_locked column, the WHERE predicate might be a bit different. If it's a boolean for example:

WHERE NOT c.is_locked