CODEWITHSUNDEEP
sqlpostgresqlinheritancerecursive-query
SiLeNCeD
SiLeNCeD

Reputation: 240

Recursively look up PostgreSQL 10 hierarchy

I've been toying with recursive queries and have most of the information I need, but in our schema, we have a setting that can be inherited to the child of a parent. When the child inherits the parent setting, it's own setting is set to NULL. This relationship can span multiple levels (example: 4th child inherits from a parent at the top.

We're using PostgreSQL 10.

An example table of this data looks like:

Customers (there's no actual 'inheritance' column in our schema... that is just to identify those)

id name parent_id customer_settings inheritance
2 parent customer A 1 1234501 not inherited
3 child of A sub 1 2 null inherited
4 child of A sub 2 2 1234502 not inherited
5 child of A2 sub 1 4 null inherited
6 parent customer B 1 1234503 not inherited
7 child of B sub 1 6 1234504 not inherited
8 child of BS1 sub 1 7 null inherited
9 child of BS1 sub 2 7 null inherited
10 child of BS1S2 sub 1 9 null inherited

This describes the following hierarchy path:

What I'm trying to do is create a query that will perform a reverse lookup from the current child row in my select to the parent in order to find the first customer_setting value that would be inherited, leading to a result set that looks more like this:

Customers

id name parent_id customer_settings inheritance
2 parent customer A 1 1234501 not inherited
3 child of A sub 1 2 1234501 inherited
4 child of A sub 2 2 1234502 not inherited
5 child of A2 sub 1 4 1234502 inherited
6 parent customer B 1 1234503 not inherited
7 child of B sub 1 6 1234504 not inherited
8 child of BS1 sub 1 7 1234504 inherited
9 child of BS1 sub 2 7 1234504 inherited
10 child of BS1S2 sub 1 9 1234504 inherited

There are many different arrangements within these hierarchies, so there are some variations in how many levels inheritance may be coming from. I'd say as a worst case scenario, if something had to be defined, then 10 levels may cover it.

My main issue here is figuring out how to capture those customer_settings from the above objects when that setting is null. Once I have that value for each row, I can then pull other related settings that further define each of those records.

I can recursively look down the tables in order to collect the parent-child relationships and paths, but I have no idea how to look up the hierarchy in that same manner. I kept ending up with a single sub-query to look at the parent object (ex sub-query: SELECT customer_setting FROM customers WHERE id = parent_id), but I can't seem to figure out what I need to do to look up multiple levels when that first find is null.

I appreciate any help you guys can provide.

Upvotes: 2

Views: 337

Answers (1)

Ajax1234
Ajax1234

Reputation: 71461

You can use a recursive CTE to traverse up the table, and then join the CTE to the original table:

with recursive cte(old, id, parent, s) as (
   select id, id, parent_id, customer_settings from customers where customer_settings is null
   union all
   select c.old, c1.id, c1.parent_id, c1.customer_settings from cte c join customers c1 on c.parent = c1.id where c.s is null
)
select c.id, c.name, c.parent_id, case when c.customer_settings is null then c1.s else c.customer_settings end, c.inheritance from customers c left join cte c1 on c.id = c1.old and c1.s is not null;

Upvotes: 1

Related Questions