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.