Re: Recursive CTE for building menus

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(at)illuminatedcomputing(dot)com