Announcement

Collapse
No announcement yet.

Help with hierarchical query output (PL/SQL)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with hierarchical query output (PL/SQL)

    So I've got a large hierarchical permissions table with about 2000 rows across 5 levels. I've written something to get the structure out of this, but I'd like to clean it up a little.

    The structure table (PERM_CLASSES) has columns CLASS_ID (PK), PARENT_CLASS_ID, DESCRIPTION

    The user table (PERM_USERS) has columns CLASS_ID and ROLE

    A cut down version of the code used for the hierarchy:

    Code:
    SELECT description, role, SYS_CONNECT_BY_PATH(description, '/') path, LEVEL, pc.class_id, parent_class_id
    FROM perm_classes pc
    LEFT JOIN perm_roles pr
    ON pc.class_id=pr.class_id
    CONNECT BY PRIOR pc.class_id=parent_class_id
    START WITH parent_class_id IS NULL
    ORDER by path, role
    And a quick fiddle with some example data: http://sqlfiddle.com/#!4/dbb7e/1
    (there are normally permission under all levels of the tree, not just children, I was just lazy)

    What I want is for, in cases where the class has a matching permissions role, to only display the top level class once. So for example:

    DESCRIPTION ROLE PATH
    GrandParent class 1 /GrandParent class 1
    Parent class 1 /GrandParent class 1/Parent class 1
    Child class 1 Admin /GrandParent class 1/Parent class 1/Child class 1
    Child class 1 Read-Only Admin /GrandParent class 1/Parent class 1/Child class 1
    Child class 1 SuperUser /GrandParent class 1/Parent class 1/Child class 1
    Child class 2 Admin /GrandParent class 1/Parent class 1/Child class 2
    Child class 2 SuperUser /GrandParent class 1/Parent class 1/Child class 2
    Parent class 2 /GrandParent class 1/Parent class 2
    Child class 3 SuperUser /GrandParent class 1/Parent class 2/Child class 3
    would get tidied up to:

    DESCRIPTION ROLE PATH
    GrandParent class 1 /GrandParent class 1
    Parent class 1 /GrandParent class 1/Parent class 1
    Child class 1 Admin /GrandParent class 1/Parent class 1/Child class 1
    | Read-Only Admin /GrandParent class 1/Parent class 1/Child class 1
    | SuperUser /GrandParent class 1/Parent class 1/Child class 1
    Child class 2 Admin /GrandParent class 1/Parent class 1/Child class 2
    | SuperUser /GrandParent class 1/Parent class 1/Child class 2
    Parent class 2 /GrandParent class 1/Parent class 2
    Child class 3 SuperUser /GrandParent class 1/Parent class 2/Child class 3
    Any thoughts? I'm also going to indent description by level with lpad, but it doesn't work in the fiddle for some reason.
    Last edited by heavywater; 24-03-17, 22:37.

  • #2
    Since it's a small data set you're dealing with and it's PL/SQL (which supports imperative flow control), i'd just take the lazy route and wrap you're SQL inside a recursive loop.

    Comment


    • #3
      Not a bad suggestion... I gave up on that one and went for the even lazier route: manipulating the output in excel.

      Any thoughts on a good way to identify roles in child nodes that don't exist in all parent nodes?

      Comment

      Working...
      X