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
