Taking the most distant meaning from the root in the hierarchy
I am trying to simulate a "settings" hierarchy where values are defined at the root level and can be overridden by more specific children. If the child does not specify a value, its value must be used.
I'll use a trivial example to illustrate the problem. The hierarchy is only three levels here, but I need a solution that works for N levels.
Given that I am saving information like this:
id| parent_id| setting
----------------------
1| NULL| false
2| 1| true
3| 2| NULL
What I want from a procedural point of view is to get the child of a node in the tree, and if its "settings" value is NULL, look at its parent for a recursive value until the value, or root, is found. Essentially, for the information provided, I want to create the following set, so I can attach a simplest suggestion WHERE
to get the appropriate settings for any given id
.
id| setting
-----------
1| false
2| true
3| true
I have a view that "flattens" the ancestor and descendant hierarchy:
ancestor| descendant| ancestor_setting| descendant_setting
----------------------------------------------------------
1| 2| false| true
1| 3| false| NULL
2| 3| true| NULL
NULL| 1| NULL| false
NULL| 2| NULL| true
NULL| 3| NULL| NULL
This way you can query all levels of the hierarchy as a set, which I hoped would be helpful in getting a response.
Until now, I could select a "branch" from the tree using this view:
SELECT COALESCE(ancestor, descendent) id,
CASE WHEN ancestor IS NULL THEN descendant_setting
ELSE ancestor_setting
END setting
FROM hierarchy
WHERE descendant = 3
id| setting
-----------
1| false
2| true
3| NULL
I tried to figure out how to use this "flattened" structure to form a simple set of joins, and while I can return all records this way (and then filter them procedurally on the client), I want to see if there is a way to create the expected set so that i could return the expected settings for one id.
source to share
WITH RECURSIVE
q AS
(
SELECT id, parent_id, id ancestor_id, setting
FROM mytable
WHERE parent_id IS NULL
UNION ALL
SELECT m.id, q.id, ancestor_id, COALESCE(m.setting, q.setting)
FROM q
LEFT JOIN
mytable m
ON m.parent_id = q.id
WHERE q.id IS NOT NULL
)
SELECT *
FROM q
WHERE id IS NULL
source to share