Understanding Nested Sets
Last modified on 2023/08/03 12:23
Contents
Service Manager tree tables are based on the nested set model used to manage parents and childs to an infinity levels.
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
 |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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
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
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)
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 | |