Understanding Nested Sets


Product name - ev itsm.png tree structures are based on the nested set model. The tables organized as tree structures are :

  • Location
  • Department
  • Asset category (Equipment, License, Contract, CI)
  • All request Catalog (Incident/Service request/Problem/Change/Investment/Projects)
  • Supplier
  • Known errors and Knowledge

         Open url.png  see  http://en.wikipedia.org/wiki/Nested_set_model

LEVEL, LFT, RGT fields

Tree structures are built based on 3 fields:

  • LEVEL field: It contains the level within the tree structure:
    • LEVEL 1: the tree root
    • LEVEL 2: first level beyond the root. On the request and asset catalog tree, this is a reserved level identifying the request categories (Incidents, service requests, changes, problems, projects)
  • LFT and RGT fields: The values of these fields are calculated automatically and define the position within the tree.
    • All records below a specific record within the tree will have a greater LFT than the LFT of their parent and a smaller RGT than the RGT of their parent. 
    • A root record has the following values: LFT = 1; RGT = max value of the LFT and RGT fields in the base.

Example: Catalogs tree structure

         Nested tree.png

SD_CATALOG table

Subject name LEVEL LFT RGT
Root (« -») 1 1 24
Incidents 2 2 13
Subject I.1 3 3 8
Subject I.1.1 4 4 5
Subject I.1.2 4 6 7
Subject I.2 3 9 10
Subject I.3 3 11 12
Services 2 14 21
Subject S.1 3 15 16
Subject S.2 3 17 20
Subject S.2.1 4 18 19
Changes 2 22 23

Notes:

  • LFT is always smaller than RGT.
  • If the difference between RGT and LFT is one, the record is a leaf. There's no record below.

Procedures

How to get all the sublevels of a level

1. Execute the query below: it returns the values of the searched record, here the "Services" level.

SELECT *
FROM   sd_catalog
WHERE  title_fr LIKE '%service%'

2. Once the values of the LFT (14) and RGT (21) fields are returned, execute this second query that displays the list of sublevels.

SELECT *
FROM   sd_catalog
WHERE  ( lft > 14
        AND rgt < 21 )  

Result:

Subject name LEVEL LFT RGT
Subject S.1 3 15 16
Subject S.2 3 17 20
Subject S.2.1 4 18 19

How to get all the leaves

You can execute this query:
SELECT parent.*
FROM   sd_catalog parent
WHERE  NOT EXISTS (SELECT 1
                  FROM   sd_catalog enfant
                  WHERE  enfant.lft > parent.lft
                         AND enfant.rgt < parent.rgt)
Result:
Subject name LEVEL LFT RGT
Subject I.1.1 4 4 5
Subject I.1.2 4 6 7
Subject I.2 3 9 10
Subject I.3 3 11 12
Subject S.1 3 15 16
Subject S.2.1 4 18 19
Changes 2 22 23
Tags:
Last modified by Unknown User on 2017/03/29 10:30
Created by Administrator XWiki on 2013/07/01 10:34

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2018