Início / dba / Perguntas / 48562
Accepted
Dave Jarvis
Asked: 2013-08-23 12:49:39 +0800 CST

Conexão reversa por valor de nível anterior para hierarquia arbitrariamente profunda

  • 772

Fundo

Usando uma hierarquia de menu para conduzir um processo de login para usuários. Os usuários podem definir seu item de menu preferido. Quando eles fazem login, se tiverem um item de menu preferido definido, o sistema os direciona para esse item. Se nenhum item de menu preferido for definido, eles farão login no item de menu padrão para sua função "mais importante".

Código

A consulta usa connect by priorpara obter a lista de 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

Problema

Um item de menu raiz tem NULLcomo pai ( mim_id). A preferência de menu do usuário é um nó de folha de item de menu, que pode ser encontrado em qualquer nível na hierarquia (a profundidade máxima é 3, neste caso).

Quando os dados são retornados, os valores da LEVELpseudocoluna (alias MENU_LEVEL) estão na ordem inversa:

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

Isso realmente deve retornar:

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

No entanto, como a hierarquia connected bycomeça no item de menu preferido do usuário e trabalha de volta ao item de menu raiz, faz sentido LEVELcontar "para trás".

Ter o nível invertido significa que podemos perguntar: "Qual é o item de menu de terceiro nível para o usuário chamado 'USERNAME'"? Expresso como uma cláusula where do SQL:

WHERE menu_level = 3 AND username = 'USERNAME';

Pergunta

Como você inverteria o valor de LEVELpara uma hierarquia arbitrariamente profunda?

Por exemplo, algo como:

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

Obviamente isso não vai funcionar porque maxé uma função agregada.

violino

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

Estranhamente, estou vendo comportamentos diferentes na instância 11g R2 do Fiddle do que na instância local do Oracle - o ROWNUM está captando "1" no Fiddle quando deveria estar captando "3". Isso evita ver a hierarquia do menu e, portanto, o arquivo LEVEL. Não sei por quê.

Ideias

  • Poderíamos adicionar uma coluna para jhs_menu_itemsarmazenar a profundidade. Isso é um pouco redundante, porque a própria hierarquia contém essas informações.
  • Poderíamos envolver a jhs_menu_itemstabela em uma exibição que calcula a profundidade. Isso pode ficar computacionalmente caro.
  • Este é um bom candidato para WITH?
oracle hierarchy

1 respostas

  1. Best Answer

    Você nunca pode consultar ROWNUM = 3; ROWNUM 1 é filtrado, então o próximo registro agora é ROWNUM = 1 e assim por diante.

    Suspeito que estar preocupado em controlar a profundidade computacional é uma otimização prematura. A menos que você tenha milhões de opções sendo retornadas para um único usuário, extrair o nível mais alto do conjunto de dados - ou mesmo extrair o nível mais alto de cada conjunto de dados deve ser bem simples. Não é como se você não pudesse explorar totalmente a hierarquia, então o trabalho adicional no mecanismo SQL de priorizar/ classificar cada janela de linhas é bastante leve em cima disso.

    Portanto, usar funções de agregação/janela parece ser uma escolha natural aqui.

    Seu SQLFiddle não retorna o conjunto de dados esperado, mas modifiquei o SQL abaixo para mostrar como você pode priorizá-los, supondo que eu entenda os requisitos que você está procurando.

    Rocha analítica.

    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
    • 1

relate perguntas