From: | Bob Jones <r(dot)a(dot)n(dot)d(dot)o(dot)m(dot)d(dot)e(dot)v(dot)4+postgres(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Recursive CTE for building menus |
Date: | 2018-04-13 09:09:31 |
Message-ID: | CA+HuS5HFK1sqDe6F1G02o14Va2vnaZgNrdSPY7QMgYDLoW_PBw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Jungwirth | 2018-04-13 15:04:37 | Re: Recursive CTE for building menus |
Previous Message | hmidi slim | 2018-04-13 00:59:54 | Table schema inhancement |