CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
Federico klez Culloca
Federico klez Culloca

Reputation: 27119

WITH RECURSIVE only returning the non-recursive term

I have a table object consisting of the following fields:

The table content is constructed so that every object has a parent, except the top node for a tree that has an idobject_parent value of null. There are many trees, each with a different top node.

Given a leaf node, I want to get the fields of the top node for that tree. So I have the following query:

with recursive parent_object(id) as (
        select id,idobject_parent, label from object o
    union
        select oe.id, oe.idobject_parent, oe.label from object oe join parent_object pc on (oe.id = pc.idobject_parent) where oe.idobject_parent is not null
)
select id,idobject_parent,label from parent_object where id = 340;

Where 340 is an arbitrary leaf id I'm using for this test.

The problem is that this query just returns the leaf node itself, i.e., it seems to just execute the non-recursive case.

By manually doing the recursive query and using the result as "input" for the following one, I can see that I'm ascending the tree correctly:

 db=# select oe.id,oe.idobject_parent,oe.label from object oe join (select 340 as id,480 as idobject_parent, 'hello' as label) as pc on (oe.id = pc.idobject_parent) where oe.idobject_parent is not null;
 id  | idobject_parent |   label
-----+-----------------+-----------
 480 |             105 | xxxxxxx
(1 row)

 db=# select oe.id,oe.idobject_parent,oe.label from object oe join (select 480 as id,105 as idobject_parent, 'hello' as label) as pc on (oe.id = pc.idobject_parent) where oe.idobject_parent is not null;
 id  | idobject_parent |         label
-----+-----------------+-----------------------
 105 |             102 | yyyyyyy
(1 row)

 db=# select oe.id,oe.idobject_parent,oe.label from object oe join (select 105 as id,102 as idobject_parent, 'hello' as label) as pc on (oe.id = pc.idobject_parent) where oe.idobject_parent is not null;
 id | idobject_parent | label
----+-----------------+-------
(0 rows)

What's wrong with my query? Why am I not getting this?

 id  | idobject_parent |         label
-----+-----------------+-----------------------
 105 |             102 | yyyyyyy
(1 row)

Thank you in advance.

Upvotes: 0

Views: 250

Answers (1)

joop
joop

Reputation: 4503

Instead of extending the forest downwards (starting with all the roots), you can walk upwards, towards the root. (this is also more efficient, since you dont have to generate all the paths before selecting the one you want)

-- sample structure
CREATE TABLE thetree
        ( id INTEGER not null primary key
        , parent INTEGER references thetree(id)
        -- , label text
        );

        -- make some data
INSERT INTO thetree(id,parent)
SELECT gs, gs / 3 FROM generate_series(0,20) gs;
UPDATE thetree SET parent = NULL where parent=0;

CREATE UNIQUE INDEX ON thetree(parent,id);

-- SELECT * FROM thetree;

WITH RECURSIVE zzz AS (
        SELECT COALESCE(parent,id) AS root
        , parent AS par
        , id AS me
        , 0::integer AS upsteps
        FROM thetree WHERE id = 17
    UNION ALL
        SELECT COALESCE(t.parent,zzz.root) AS root
        , t.parent AS par
        , zzz.me AS me
        , 1+ zzz.upsteps AS upsteps
        FROM thetree t
        JOIN zzz ON zzz.par = t.id
        )
SELECT *
-- SELECT root,me
FROM zzz
WHERE zzz.par IS NULL -- eliminate partial paths
        ;

FYI The downward treewalk:

    -- treewalk; starting with roots: downward
WITH RECURSIVE omg AS (
        SELECT id AS root
        , id AS me
        , 0::integer AS upsteps
        FROM thetree WHERE parent IS NULL -- this is the root of a tree
    UNION ALL
        SELECT omg.root AS root
        , t.id AS me
        , 1+ omg.upsteps AS upsteps
        FROM thetree t
        JOIN omg ON t.parent = omg.me
        )
SELECT *
FROM omg
WHERE omg.me = 17
        ;

Upvotes: 1

Related Questions