2
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Do you have any flexibility in the data representation? Consider using a nested set model to avoid recursion. \$\endgroup\$ Oct 20, 2014 at 22:05
  • \$\begingroup\$ @200_success Unfortunately, I cannot change the data representation, because other developers have written tools that depend on the current schema (or minor modifications thereof). In general, most of the data access that we need to do is well-suited to using an adjacency list; it's just this one query that is the issue. \$\endgroup\$
    – Moshe Katz
    Oct 20, 2014 at 22:14

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.