CODEWITHSUNDEEP
sqlpostgresql
user1804599
user1804599

Reputation:

Selecting a tree and ordering children using PostgreSQL

I have the following table.

CREATE TABLE categories (
    id          integer serial primary key
    parent_id   integer
    name        text    not null
    ordering    integer not null
);

I want to select all categories, but indent the names of child categories and put the child categories under their parents. Within the same level, they should be ordered by ordering.

I currently have the following query, but the returned child categories are not in the correct order. Instead, they are being ordered absolutely rather than relative to their siblings.

WITH RECURSIVE recurse_categories(id, level, name, ordering) AS (
    SELECT id, 0 AS level, name, ordering
      FROM categories
     WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, level + 1, c.name, c.ordering
      FROM categories AS c
      JOIN recurse_categories rc
        ON rc.id = c.parent_id
)

  SELECT id, repeat('→', level) || ' ' || name AS name
    FROM recurse_categories
ORDER BY ordering

How can I do the ordering correctly?

I'm using PostgreSQL 9.2.4.

Upvotes: 1

Views: 219

Answers (1)

roman
roman

Reputation: 117380

accumulate ordering into string:

WITH RECURSIVE recurse_categories(id, level, name, ordering) AS (
    SELECT id, 0 AS level, name, lpad(ordering::text, 10, '0') as ordering
      FROM categories
     WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, level + 1, c.name, rc.ordering || '→' || lpad(c.ordering::text, 10, '0') as ordering
      FROM categories AS c
      JOIN recurse_categories rc
        ON rc.id = c.parent_id
)

  SELECT id, repeat('→', level) || ' ' || name AS name, ordering
    FROM recurse_categories
ORDER BY ordering

sql fiddle demo

Upvotes: 1

Related Questions