Thread: Recursive CTE for building menus
Hello, Whilst researching current thinking on hierarchical queries in Postgres, I stumbled accross this excellent blog post: https://illuminatedcomputing.com/posts/2014/09/postgres-cte-for-threaded-comments/ But try as I might, my SQL-foo is not up to scratch to adapt it to my needs, I keep on loosing child nesting and other weird bug-dom. My table looks like this : menu_title text menu_item_id text menu_priority integer menu_parent text The adaptions I am trying to make are as follows: - Higher priority moves the item higher up the menu (i.e. adapting from the original "votes" concept). - Default alphabetical ordering of titles - Use of alphanumeric IDs instead of numeric The only thing that I can get consistently working is the alphanumeric menu IDs. For menu priorities, postgres does not seem to like mixing numeric and alphanumeric in an array: ERROR: ARRAY types integer and text cannot be matched LINE 3: array[-menu_priority,menu_itemid] as path,1 as depth insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Home','H',1000,NULL); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('About','A',900,NULL); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('FOOBAR','F',800,NULL); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Resources','R',NULL,'A'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Background','B',NULL,'A'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Foo','Ff',NULL,'F'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('About Bar','Fba',NULL,'Fb'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Team Bar','Fbt',NULL,'Fb'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Bar','Fb',NULL,'F'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('Foo World','Ffw',NULL,'Ff'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('About World','FFwa',NULL,'Ffw'); insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent) values('World Introduction','FFwi',1000,'Ffw'); N.B. Although I show NULL as a default priority, I have experimenting with setting default priorities with no success. The expected outcome from the above would be (ignore the pretty-print elements, its just to help human parsing !): •Home •About -> Background -> Resources •FOOBAR -> Bar ->-> About Bar ->-> Team Bar -> Foo ->-> Foo World ->->-> World Introduction ->->-> About World
On 04/13/2018 02:09 AM, Bob Jones wrote:
> The adaptions I am trying to make are as follows:
> - Higher priority moves the item higher up the menu (i.e. adapting
> from the original "votes" concept).
> - Default alphabetical ordering of titles
> - Use of alphanumeric IDs instead of numeric
Hi, I wrote that blog post! :-)
This works for me:
WITH RECURSIVE cte (menu_item_id, menu_title, path, menu_parent, depth,
menu_priority) AS (
SELECT menu_item_id,
menu_title,
ARRAY[(-menu_priority, menu_title, menu_item_id)] AS path,
menu_parent,
1 AS depth,
menu_priority
FROM test_table
WHERE menu_parent IS NULL
UNION ALL
SELECT m.menu_item_id,
m.menu_title,
cte.path || (-m.menu_priority, m.menu_title, m.menu_item_id),
m.menu_parent,
cte.depth + 1,
m.menu_priority
FROM test_table m
JOIN cte ON m.menu_parent = cte.menu_item_id
)
SELECT menu_item_id, menu_title, path, depth, menu_priority
FROM cte
ORDER BY path
;
menu_item_id | menu_title |
path | depth |
menu_priority
--------------+--------------------+----------------------------------------------------------------------------------------------+-------+---------------
H | Home | {"(-1000,Home,H)"}
| 1 |
1000
A | About | {"(-900,About,A)"}
| 1 |
900
B | Background |
{"(-900,About,A)","(,Background,B)"}
| 2 | NULL
R | Resources |
{"(-900,About,A)","(,Resources,R)"}
| 2 | NULL
F | FOOBAR | {"(-800,FOOBAR,F)"}
| 1 |
800
Fb | Bar | {"(-800,FOOBAR,F)","(,Bar,Fb)"}
| 2 |
NULL
Fba | About Bar |
{"(-800,FOOBAR,F)","(,Bar,Fb)","(,\"About Bar\",Fba)"}
| 3 | NULL
Fbt | Team Bar |
{"(-800,FOOBAR,F)","(,Bar,Fb)","(,\"Team Bar\",Fbt)"}
| 3 | NULL
Ff | Foo | {"(-800,FOOBAR,F)","(,Foo,Ff)"}
| 2 |
NULL
Ffw | Foo World |
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)"}
| 3 | NULL
FFwi | World Introduction |
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)","(-1000,\"World
Introduction\",FFwi)"} | 4 | 1000
FFwa | About World |
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)","(,\"About
World\",FFwa)"} | 4 | NULL
(12 rows)
So basically the sort is by menu_priority, breaking ties with
menu_title, then breaking ties with menu_item_id. I think that's what
you want, right?
The hard part was dealing with mixed types (integer for priority, text
for the others), because an array has to be all one type. Fortunately
you can build an array of tuples and the sorting will work as you expect.
I was a little worried to see those tuples appearing like strings in the
output, but then I remembered that in Postgres ' is a string and " is
not. Or to prove it:
select * from unnest( array[(1, 'a'::text), (2, 'b'::text)] ) x(a int, b
text);
a | b
---+---
1 | a
2 | b
Anyway, I hope that gets you what you need!
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
On 13 April 2018 at 16:04, Paul Jungwirth <pj@illuminatedcomputing.com> wrote: > On 04/13/2018 02:09 AM, Bob Jones wrote: >> >> The adaptions I am trying to make are as follows: >> - Higher priority moves the item higher up the menu (i.e. adapting >> from the original "votes" concept). >> - Default alphabetical ordering of titles >> - Use of alphanumeric IDs instead of numeric > > > Hi, I wrote that blog post! :-) ><snip> Accidentally hit the wrong reply button and sent a reply direct to Paul instead of list. I won't repeat my message here, but instead I will just leave a brief expression of public gratitude to the great man himself for taking the time to reply. For the record: A quick simple test indicates (as might be expected) that the proposed query works. Thanks again. Bob