PostgreSQL Hierarchical, category tree

Question

ENV : postgresql-8.4

I'm trying to build a category tree . Basically I'm expecting a final output such :

categoryName 
categoryPath
leafcategory

e.g. :

Digital Camera
Electronics ::: Digital Camera 
true

The table structure is

CREATE TABLE categories (
    id  SERIAL PRIMARY KEY,
    categoryid bigint,
    categoryparentid bigint,
    categoryname text,
    status integer DEFAULT 0,
    lang text,
    eysiteid text,
    country text,
    tempid text,
    leafcategory boolean
);

So far I've got this but is not working. Any help would be highly appreciated :

WITH RECURSIVE tree (CategoryID, CategoryParentID, CategoryName, category_tree, depth) 
AS ( 
    SELECT 
        CategoryID,
        CategoryParentID,
        CategoryName,
        CategoryName AS category_tree,
        0 AS depth 
    FROM categories 
    WHERE CategoryParentID IS NULL 
UNION ALL 
    SELECT 
        c.CategoryID,
        c.CategoryParentID,
        c.CategoryName,
        tree.category_tree  || '/' || c.CategoryName AS category_tree,
        depth+1 AS depth 
    FROM tree 
        JOIN categories c ON (tree.category_tree  = c.CategoryParentID) 
) 
SELECT * FROM tree ORDER BY category_tree;

Sample from database

cat=> select * from categories;
  id   | categoryid | categoryparentid |          categoryname          | status | lang | eysiteid | country | tempid | leafcategory 
-------+------------+------------------+--------------------------------+--------+------+------------+---------+--------+--------------
     1 |         -1 |                0 | Root                           |      1 | en   | 0          | us      |        | f
     2 |      20081 |               -1 | Antiques                       |      1 | en   | 0          | us      |        | f
    17 |       1217 |            20081 | Primitives                     |      0 | en   | 0          | us      |        | t
    23 |      22608 |            20081 | Reproduction Antiques          |      0 | en   | 0          | us      |        | t
    24 |         12 |            20081 | Other                          |      0 | en   | 0          | us      |        | t
    25 |        550 |               -1 | Art                            |      1 | en   | 0          | us      |        | f
    29 |       2984 |               -1 | Baby                           |      1 | en   | 0          | us      |        | f

You have a strange way of marking the root nodes of the tree (normally one would have a NULL parent pointer for the roots). See my update. — wildplasser, Aug 06 '12 at 21:18

wildplasser · Accepted Answer · 2012-08-06T21:17:06.157

It appears you were joining on the wrong field.

 --  create some test data
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE categories
    -- ( id  SERIAL PRIMARY KEY
    ( categoryid SERIAL PRIMARY KEY
    , categoryparentid bigint REFERENCES categories(categoryid)
    , categoryname text
    -- , status integer DEFAULT 0
    -- , lang text
    -- , ebaysiteid text
    -- , country text
    -- , tempid text
    -- , leafcategory boolean
        );
INSERT INTO categories(categoryid,categoryparentid) SELECT gs, 1+(gs/6)::integer
FROM generate_series(1,50) gs;

UPDATE categories SET categoryname = 'Name_' || categoryid::text;
UPDATE categories SET categoryparentid = NULL WHERE categoryparentid <= 0;
UPDATE categories SET categoryparentid = NULL WHERE categoryparentid  >= categoryid;


WITH RECURSIVE tree (categoryid, categoryparentid, categoryname, category_tree, depth)
AS (
    SELECT
        categoryid
        , categoryparentid
        , categoryname
        , categoryname AS category_tree
        , 0 AS depth
    FROM categories
    WHERE categoryparentid IS NULL
UNION ALL
    SELECT
        c.categoryid
        , c.categoryparentid
        , c.categoryname
        , tree.category_tree  || '/' || c.categoryname AS category_tree
        , depth+1 AS depth
    FROM tree
        JOIN categories c ON tree.categoryid  = c.categoryparentid
    )
SELECT * FROM tree ORDER BY category_tree;

EDIT: the other ("non-function") notation for recursive seems to work better:

WITH RECURSIVE tree AS (
    SELECT
        categoryparentid AS parent
        , categoryid AS self
        , categoryname AS treepath
        , 0 AS depth
    FROM categories
    WHERE categoryparentid IS NULL
UNION ALL
    SELECT
        c.categoryparentid AS parent
        , c.categoryid AS self
        , t.treepath  || '/' || c.categoryname AS treepath
        , depth+1 AS depth
    FROM categories c
    JOIN tree t ON t.self  = c.categoryparentid
    )
SELECT * FROM tree ORDER BY parent,self
   ;

UPDATE: in the original query, you should replace

WHERE CategoryParentID IS NULL

by:

WHERE CategoryParentID = 0

or maybe even:

WHERE COALESCE(CategoryParentID, 0) = 0

must mention that the categoryparentid of the root category is 0 . — themihai, Aug 06 '12 at 20:03
You must set the parent to NULL where parent <= 0; (or change the where clause in the first leg of the union) — wildplasser, Aug 06 '12 at 20:07
I cannot find the actual error; the recursive fails here (pg 9.1.2) if I use the "functional" notation. — wildplasser, Aug 06 '12 at 20:27
I think parent cannot be null because the type is bigint. Should I change the categoryparentid type to text ? — themihai, Aug 06 '12 at 20:56
Absolutely not. A bigint type should be nullable (by default), just like an integer type, or any other type. BTW: it appears you already have too many candidate keys. What is the purpose of the ID field ? — wildplasser, Aug 06 '12 at 21:02
There may be duplicates of categoryId(s) so that's why I added an unique "id" field. Seems that the NULL fixed the function as now works. — themihai, Aug 06 '12 at 21:15
That is *essentially* a normalisation problem. If there are possibly more items that belong to a category, the {category, parentcategory, categoryname} and the {item, categoryid, itemname} should be in two separate tables with an 1:N relationship. (otherwise the {category, parentcategory} pairs would repeat in the single table) — wildplasser, Aug 06 '12 at 21:35

score 1 · Answer 2 · answered Aug 21 '12 at 15:51

1

Have a look at this gist it is more or less what you want to do. In your case I would better have used LTree materialized path Postgresql's extension.

answered Aug 21 '12 at 15:51

greg

3,354
1
24
35

PostgreSQL Hierarchical, category tree

2 Answers2