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@illuminatedcomputing.com