Re: CTE with multiple references to the recursive table

[Oliver Demetz]

Well, I simplified my actual code.
In fact I have a base class DataCategory with several derived classes UnaryDataCategory, BinaryDataCategory,...

I am using class table inheritance, that means there is a table data_category(id, discriminator, name, common_field, ...), and one table for each child class, e.g. unary_data_category(id, child_data_category_id,...) where child_data_category_id is a foreign key into data_category. Same holds for binary_data_category(id, child_data_category_1_id, child_data_category_2_id, ...) also here child_data_category_1_id and child_data_category_2_id are FKs into data_category.

This means given an id, I cannot get the children without a join onto the child tables.

A non-recursive implementation that has to loop over all depth levels might look like this:

WITH childidarray AS ( SELECT id from data_category where id IN (:ids_on_current_level))
SELECT cat1 AS cid
FROM binary_data_category
WHERE id IN (select * FROM childidarray)
UNION
SELECT cat2 AS cid
FROM binary_data_category
WHERE id IN (select * FROM childidarray)
UNION
SELECT child_data_category_id AS cid
FROM unary_data_category
WHERE id IN (select * FROM childidarray)