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
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?