Understanding Nested Sets
Last modified on 2022/05/28 10:55
Contents
Service Manager tree tables are based on the nested set model used to manage parents and childs to an infinity levels. 
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
 |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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 | |