Understanding Nested Sets

Last modified on 2022/05/28 10:55

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  http://en.wikipedia.org/wiki/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