Understanding Nested Sets

Last modified on 2023/08/03 12:23

Service Manager tree tables are based on the nested set model used to manage parents and childs to an infinity levels.

     Open url.png See Nested set model.

Characteristics of Service Manager tree tables

List of tree tables

  • Asset categories (Equipment, Licenses, Contracts, CIs)
  • Catalogs : Incidents / Service requests / Problems / Changes / Investments / Projects
  • Departments
  • Known errors and Knowledge
  • Suppliers
  • Locations

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: Sublevels of the tree root (Incidents, Service requests, Changes, Problems, Projects).
    • LEVEL N: If you do down one level in the tree structure, the LEVEL field will be incremented by 1.
  • LFT and RGT fields: The values of these two fields are calculated automatically and define the position within the tree.
    • All subjects below a specific branch 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 tree structure.

Example

The catalogs tree structure is managed from the SD_CATALOG table

  • 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.
Tree representation LEVEL, LFT, RGT fields
Nested tree.png
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

Procedures

How to get all the sublevels of a level

1. Retrieve the values of the LFT et RGT fields that correspond to the Services subject. To do this, execute a query that returns all entries of the SD_CATALOG table containing the service character string.

SELECT sd_catalog_id, "40000".get_path_sd_catalog_fr(sd_catalog_id) as title_fr, lft, rgt, level
FROM   sd_catalog
WHERE  title_fr LIKE '%service%'
ORDER BY 2 ASC

2. Retrieve the values of the LFT et RGT fields (14 and 21). To do this, execute a query that returns all subjects of the Services subject.

SELECT sd_catalog_id, "40000".get_path_sd_catalog_fr(sd_catalog_id) as title_fr, lft, rgt, level
FROM   sd_catalog
WHERE  ( lft > 14  AND rgt < 21 )
ORDER BY 2 ASC

Result:

Nom du Subject 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 the query below.

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:

Nom du Subject 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
Changements 2 22 23
Tags:
Powered by XWiki © EasyVista 2022