Sorting of nested categories inside a Common Table Expression

Question

I have a PostgreSQL database which contains (among other things) the following table definition:

CREATE TABLE al_categories
(
  id serial NOT NULL,
  name character varying(400) NOT NULL,
  banner text,
  parent_id integer NOT NULL,
  display_order integer NOT NULL,
  se_name character varying(100) NOT NULL, -- This is what WordPress calls a "slug"
  is_active boolean NOT NULL
  CONSTRAINT al_categories_pkey PRIMARY KEY (id)
)

This creates a category hierarchy stored using adjacency lists. Top-level categories are stored with parent_id set to 0.

One requirement that we have is to retrieve a list of all of the category IDs, along with the full textual path from the top of the order. To get this data, I have written a view which executes the following Common Table Expression:

WITH RECURSIVE cats AS (
    SELECT
        id,
        name,
        CAST(name AS varchar(1000)) AS path_name,
        0 AS parent_id,
        1 AS level_indicator,
        display_order::INT8 * 1000000000000 AS global_sort,
        1000000000000 / 1000 AS sort_part_upto,
        is_active
    FROM al_categories
    WHERE parent_id = 0
UNION
    SELECT
        c1.id,
        c1.name,
        CAST(c0.path_name || ' / ' || c1.name AS varchar(1000)) AS path_name,
        c0.id AS parent_id,
        c0.level_indicator + 1 AS level_indicator,
        c0.global_sort + (display_order::INT8 * sort_part_upto) AS global_sort,
        c0.sort_part_upto / 1000 AS sort_part_upto,
        c1.is_active
    FROM al_categories AS c1
        INNER JOIN cats AS c0
        ON c1.parent_id = c0.id
)
SELECT id, name, path_name, parent_id, level_indicator, global_sort
FROM cats
ORDER BY global_sort, path_name

As you can see, one of the requirements is that the data is sorted using the display_order columns from all of the categories, such that each category's global_sort number is also based on the display_order of its parent.

I feel like the global_sort is a big mess, and there has got to be a better way to do it. Is the way I am doing it a good way, or is there a better way to write this recursive query?

---

An example dataset (simplified from http://comfortfirst.com/) is here, and an example of the returned data from the view is here.

Answer 1

Instead of using some large number base, I'd use an array.

Start with ARRAY[display_order] AS global_display_order. For each additional level, array_append(global_display_order, display_order). Arrays are compared element by element; ties are resolved by putting the shorter array first — which is what you want.

For readability, instead of the table aliases c0 and c1, I suggest parent and child.