Reverse connect by prior level value for arbitrarily-deep hierarchy

Question

Background

Using a menu hierarchy to drive a login process for users. Users have the ability to set their preferred menu item. When they log in, if they have a preferred menu item set, the system directs them to that item. If no preferred menu item is set, they log into the default menu item for their "most important" role.

Code

The query uses connect by prior to get the list of menus:

  SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'USERNAME' AND
        ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to 
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id

Problem

A root menu item has NULL for its parent (mim_id). The user's menu preference is a menu item leaf node, which can be found at any level in the hierarchy (the maximum depth is 3, in this case).

When the data is returned, the values for the LEVEL pseudocolumn (alias MENU_LEVEL) are in reverse order:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          1 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          3 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

This should actually return:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          3 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          1 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

However, since the hierarchy is connected by starting from the user's preferred menu item, and worked back up to the root menu item, it makes sense that LEVEL is counting "backwards".

Having the level reversed means we can ask, "What is the 3rd-level menu item for the user named 'USERNAME'"? Expressed in as a SQL where clause:

WHERE menu_level = 3 AND username = 'USERNAME';

Question

How would you reverse the value of LEVEL for an arbitrarily-deep hierarchy?

For example, something like:

SELECT
  LEVEL AS MENU_LEVEL_UNUSED,
  max(LEVEL) - LEVEL + 1 AS MENU_LEVEL
FROM ...

Obviously that won't work because max is an aggregate function.

Fiddle

http://sqlfiddle.com/#!4/60678/3/0

Strangely, I'm seeing different behaviours in Fiddle's 11g R2 instance than the local Oracle instance -- the ROWNUM is picking up "1" in the Fiddle when it should be picking up "3". This prevents seeing the menu hierarchy, and hence the LEVEL. Not sure why.

Ideas

• We could add a column to jhs_menu_items that stores the depth. This is a bit redundant, though, because the hierarchy itself contains that information.
• We could wrap the jhs_menu_items table in a view that calculates the depth. This could get computationally expensive.
• Is this a good candidate for WITH?

Answer 1

You can't ever query for ROWNUM = 3; ROWNUM 1 gets filtered out, so the next record is now ROWNUM = 1, and so on.

I suspect being worried about controlling the computational depth is premature optimization. Unless you have millions of options being returned for a single user, pulling the highest level out of the data set - or even pulling the highest level for each dataset should be pretty simple. It's not as if you can't fully explore the hierarchy, so the additional work in the SQL engine of prioritizing/ranking each window of rows is pretty light on top of that.

So using aggregate/window functions seems like a natural choice here.

Your SQLFiddle doesn't return the dataset expected, but I've modified the SQL below to show how you can prioritize them, assuming I understand the requirements you're looking for.

Analytics rock.

select menu_level, menu_name, menu_id
from
(
select menu_level, menu_name, menu_id,
       row_number() over (partition by menu_name order by menu_level desc) as menu_priority
from
(
SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'adminname' 
--      AND ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to 
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id
  )
)
 where menu_priority = 1
 order by menu_name

MENU_LEVEL  MENU_NAME   MENU_ID
        3   MenuTab1    1
        2   MenuTab2    2
        1   MenuTab3    3