Postgres: Best way to query hierarchy structures by name

Question

Suppose I have a hierarchy of categories as follows:

id |     name   | parent_id
---+------------+-----------
 1 | Computers  |  
---+------------+-----------
 2 | Laptops    | 1
---+------------+-----------
 3 | Desktops   | 1
---+------------+-----------
 4 | Big        | 2
---+------------+-----------
 5 | Small      | 2
 ---+------------+-----------
 4 | Big        | 3
---+------------+-----------
 5 | Small      | 3

Now, suppose someone gives me the input ['Computers', 'Laptops', 'Small']. What is the best way in Postgres to query the hierarchy and arrive at the correct end category (e.g. id 5)?

I know you can use recursive CTEs to traverse the tree, but what is the best way to parameterize the input array into the query?

The following more or less works, but feels really sub-par because you have to split up the parameter array:

WITH RECURSIVE path(n, id, name, parent_id) AS (
  SELECT 
    1, c.id, c.name, c.parent_id
  FROM 
    categories c
  WHERE c.name = 'Computers' AND parent_id IS NULL
  UNION ALL
  SELECT n+1, c.id, c.name, c.parent_id 
  FROM categories c,
    (SELECT * FROM unnest(ARRAY['Laptops', 'Small']) WITH ORDINALITY np(name, m)) np,
    path p
    WHERE c.parent_id = p.id AND np.m = n AND np.name = c.name
)
SELECT * FROM path;

Thats why I miss so much the `level` and `connect by` from Oracle into postgresql — Jorge Campos, Sep 22 '15 at 01:21

score 1 · Accepted Answer · answered Sep 22 '15 at 01:11

The CTE should look like this:

WITH RECURSIVE search AS (
  SELECT ARRAY['Computers', 'Laptops', 'Small'] AS terms
), path (n, id, name, parent_id) AS (
  SELECT 1, id, name, parent_id
  FROM categories, search
  WHERE name = terms[1]
  UNION
  SELECT p.n+1, c.id, c.name, c.parent_id
  FROM categories c, path p, search s
  WHERE c.parent_id = p.id
    AND c.name = (s.terms)[p.n+1]
)
SELECT * FROM path;

The neat thing is that you specify the array just once and the other terms of the CTE then simply traverse the array, no matter how long the path. No unnesting required. Note that this also works for partial trees: ['Desktop', 'Big'] will nicely produce the right path (excluding, obviously, 'Computer').

SQLFiddle here

Thank you! Added `AND parent_id IS NULL` to make sure we're always going from a root category. — Jake, Sep 22 '15 at 03:35

Postgres: Best way to query hierarchy structures by name

1 Answers1