Re: recursive WITH nested union ALL with NOCYCLE logic

On Fri, Mar 18, 2016 at 4:11 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> David,
> If I am understanding you correctly, you are assuming that alternative
> hierarchies are mapped to only ROOT level hierarchies. It's a reasonable
> assumption on your part given my illustration only covered this use case.
> But lets add another mapping.
>
> insert into mike_map (key,child,parent) values
> ('555','kkk','bbb');
> This creates an alternative hierarchy that branches from a CHILD (bbb) of
> the ROOT (aaa) hierarchy.
> So I think I still need the UNION ALL inside the recursive part.
>
>
​You are correct about my assumed premise - and I'm not sure I want to deal
with the brain damage that would result from exploring the model you are
working with...

​If you can change how you model "mike_map" this should be workable.

The following provides the desired result for the original data as well as,
I think, the correct result when adding you kkk/bbb alias.

I think this gets you closer..but probably not all of the way to your goal.

My specific concern is that none of these queries (including your original)
have considered "ddd,cow,null" as a valid result...because it lacks a
parent.

I'll leave to you to copy-paste and make it readable...

​WITH recursive
mike_map_alt (parent, children) AS (
--using an array here is probably not mandatory but makes the logic below
simpler by use of unnest()
VALUES ('aaa',ARRAY['ddd','eee']::text[]), ('bbb',ARRAY['kkk']::text[])
), mike_hier_aliased AS (
--here we "canonical-ize" the data so that for each row we know which
direct aliases are in play
SELECT mh.key::text AS key, mh.val, mh.parent::text AS parent,
mh.key::text || COALESCE(mp.children, ARRAY[]::text[]) AS key_aliases,
mh.parent::text || COALESCE(mk.children, ARRAY[]::text[]) AS parent_aliases
FROM mike_hier mh
LEFT JOIN mike_map_alt mp ON (mh.key = mp.parent)
LEFT JOIN mike_map_alt mk ON (mh.parent = mk.parent)
)
--SELECT * FROM mike_hier_aliased --used for debugging the transformed data

, recursive_part (my_key, my_val, my_parent) AS (

-- unnest the for the original row output the value; we start at the root
so parent should be null for all records
-- for aliases we don't know what their root entry is yet so simply record
null for value
-- and let the first iteration pick up the real record

-- this could be altered to left join the mike_heir_aliased table records
containing null parents
-- and coalesce the joined "val" column. This would cause "cow" from the
"ddd' record to be added to the
-- initial "ddd" unnested alias record.
SELECT me_key, CASE WHEN key = me_key THEN val ELSE null::text END AS
my_val, null::text AS my_parent FROM (
SELECT *, unnest(key_aliases) AS me_key FROM mike_hier_aliased WHERE
mike_hier_aliased.key = 'aaa'
) src

-- on each iteration we again have to look for aliases and create
-- dummy records for them (via unnest) for the next iteration
UNION --let the recursive part take care of duplicates
SELECT me_key,
CASE WHEN key = me_key THEN val ELSE null::text END AS my_val,
CASE WHEN key = me_key THEN parent ELSE null::text END AS my_parent
FROM (
SELECT mha.key, mha.val, mha.parent, unnest(mha.key_aliases) AS me_key
FROM mike_hier_aliased mha
JOIN recursive_part rp
ON (rp.my_key = mha.parent) --fails to match mha.(ddd,cow,null) though
rp.(ddd,null,null) is present
) rec_src
)

SELECT * FROM recursive_part WHERE my_val IS NOT NULL
​--our dummy records all have null for my_val so lets remove them
​
David J.