get last node given the full path of all ancestor's node attributes using cte

Question

Given the following PostgreSQL table:

items
  integer id
  integer parent_id
  string name

unique key on [parent_id, name]

parent_id is null for all root nodes

Currently I build the sql query manually, doing a join for every path element. But is seems quite ugly to me and of course it limits the possible depth.

Example:

path: holiday,images,spain
SELECT i3.* 
FROM items AS i1
   , items AS i2
   , items AS i3
WHERE i1.parent_id IS NULL AND i1.name = 'holiday'
  AND i2.parent_id=i1.id AND i2.name = 'images'
  AND i3.parent_id=i2.id AND i3.name = 'spain'

I wonder if there's a better way, probably using CTE?

You can see how my current code works and what the expected output is here:
http://sqlfiddle.com/#!1/4537c/2

Answer 1

update2 here's a function, it peforms well, because search goes only within the path, starting from parent:

create or replace function get_item(path text[])
returns items
as
$$
    with recursive cte as (
        select i.id, i.name, i.parent_id, 1 as level
        from items as i
        where i.parent_id is null and i.name = $1[1]

        union all

        select i.id, i.name, i.parent_id, c.level + 1
        from items as i
            inner join cte as c on c.id = i.parent_id
        where i.name = $1[level + 1]
    )
    select c.id, c.parent_id, c.name
    from cte as c
    where c.level = array_length($1, 1)
$$
language sql;

sql fiddle demo

update I think you can do recursive traversal. I've written sql version of this, so it's a bit messy because of cte, but it's possible to write a function:

with recursive cte_path as (
    select array['holiday', 'spain', '2013'] as arr
), cte as (
    select i.id, i.name, i.parent_id, 1 as level
    from items as i
        cross join cte_path as p
    where i.parent_id is null and name = p.arr[1]

    union all

    select i.id, i.name, i.parent_id, c.level + 1
    from items as i
        inner join cte as c on c.id = i.parent_id
        cross join cte_path as p
    where i.name = p.arr[level + 1]
)
select c.*
from cte as c
    cross join cte_path as p
where level = array_length(p.arr, 1)

sql fiddle demo

or you can build path for all of the elements using recursive cte for that and accumuate your path into array or string:

with recursive cte as (
    select i.id, i.name, i.parent_id, i.name::text as path
    from items as i
    where i.parent_id is null

    union all

    select i.id, i.name, i.parent_id, c.path || '->' || i.name::text as path
    from items as i
        inner join cte as c on c.id = i.parent_id
)
select *
from cte
where path = 'holiday->spain->2013';

or

with recursive cte as (
    select i.id, i.name, i.parent_id, array[i.name::text] as path
    from items as i
    where i.parent_id is null

    union all

    select i.id, i.name, i.parent_id, c.path || array[i.name::text] as path
    from items as i
        inner join cte as c on c.id = i.parent_id
)
select *
from cte
where path = array['holiday', 'spain', '2013']

sql fiddle demo

Answer 2

Too late to post my answer (very equivalent to Roman's and Erwin's) But an improvement on the table definition instead:

CREATE TABLE items
        ( id integer NOT NULL PRIMARY KEY
        , parent_id integer REFERENCES items(id)
        , name varchar
        , UNIQUE (parent_id,name) -- I don't actually like this one
        );                        -- ; UNIQUE on a NULLable column ...

INSERT INTO items (id, parent_id, name) values
        (1, null, 'holiday')
        , (2, 1, 'spain'), (3, 2, '2013')
        , (4, 1, 'usa'), (5, 4, '2013')
        ;

Answer 3

This should perform very well, as it eliminates impossible paths immediately:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, name
         ,'{holiday,spain,2013}'::text[] AS path  -- provide path as array here
         ,2 AS lvl                                -- next level
   FROM   items
   WHERE  parent_id IS NULL
   AND    name = 'holiday'                        -- being path[1]

   UNION ALL
   SELECT i.id, i.parent_id, i.name
         ,cte.path, cte.lvl + 1
   FROM   cte 
   JOIN   items i ON i.parent_id = cte.id AND i.name = path[lvl]
)
SELECT id, parent_id, name
FROM   cte
ORDER  BY lvl DESC
LIMIT  1;

Assuming you provide a unique path (only 1 result).

->SQLfiddle demo