I have a simple tree table in which I reference types, sub-types (no level limit), brands and models for a simple inventory application.
data structure is as shown below: (nodeIsModel is a boolean field, used for calculations)
ID parentID label nodeIsModel 1 -1 Root 0 2 1 IT 0 3 2 Desktops 0 4 3 Hewlett-Packard 0 5 4 HP Z240 1 6 4 H97M-PLUS 1 7 2 Laptops 0 8 7 DELL 0 9 8 G3 3579 1
and so on.
This table (displayed in a DBTreeView under Delphi) gives me something like:
ID Label 1 Root 2 |_IT 3 |__Desktops 4 |___Hewlett-Packard 5 |____HP Z240 6 |____H97M-PLUS 7 |__Laptops 8 |___DELL 9 |____G3 3579
This table is the reference table in which the user will pick and assign to an equipment, a set of type, sub-types, brands and models.
Equipment is saved in another table (data_items) and linked to the previous table by the id of the selected model (lowest level of tree structure).
As an example, my desktop computer would be saved as:
itemID itemLabel typeID 1 RSI-HP-DESK-01 5
because it’s a HP Z240 workstation and it is referenced as ID 5 in the tree table.
Starting from this typeID, I have a CTE query that climbs up the tree to find all parents from child ID, this works no problem.
My question is as follows:
I would like, when displaying the DBTreeView, to had a column displaying:
- the item count for all models
- the sum of models for each brand (counting the models)
- the sum of models for each sub-types
- the sum of models for each type…
and so on…
As an example, if we had 2 HP Z240 workstation and 1 DELL G3 laptop in database, the TreeView would look like:
ID Label NB 1 Root 2 |_IT 3 3 |__Desktops 2 4 |___Hewlett-Packard 2 5 |____HP Z240 1 6 |____H97M-PLUS 1 7 |__Laptops 1 8 |___DELL 1 9 |____G3 3579 1
Based on the “nodeIsModel” boolean field in the tree table, I have tried queries, some with CTE others without, but I am getting confused on the JOIN and sub-queries, also getting the error message about:
aggregate queries not being allowed in CTE
Any advice would be very much appreciated.
As per request of Dale K, here is the recursive query giving me all the parents starting from a child ID :
This query works. I am looking for a new query that would join the tree table with the item table giving a count on models assigned to items at each level of the tree
WITH CTE AS ( SELECT ID, parentID, label, CAST (nodeLevel AS INTEGER) AS LEVEL FROM dico_TBM WHERE ID = :lookupID UNION ALL SELECT r.ID, t.parentID, t.label, LEVEL - 1 FROM dico_TBM t INNER JOIN CTE r ON t.ID = r.parentID ) SELECT DISTINCT r.ID, r.parentID, r.label, LEVEL FROM CTE r WHERE LEVEL > 0 ORDER BY LEVEL
(nodeLevel being an integer giving the level of the node in the tree I omitted to mention in my question, thinking it was of no interest, and :lookupID being the ID of the starting child node I pass as a parameter to my query)
Here’s one approach which may help you solve the problem. Below is my similar setup
And the following query gives us the count of resources on each level in the resource tree.
;with CTE1 AS ( SELECT r.id, r.label, r.parentID, count(*) AS ItemCount FROM resourcetree r JOIN items i ON (r.id = i.resourceID) GROUP BY r.id, r.label, r.parentID ), CTE2 AS ( SELECT r.id, r.label, r.parentID, SUM(cte1.itemCount) AS ItemCount, 0 AS sumFlag FROM resourceTree r JOIN CTE1 ON (r.id = cte1.parentID) GROUP BY r.id, r.label, r.parentID UNION ALL SELECT r.id, r.label, r.parentID, cte2.ItemCount AS ItemCount, 1 AS sumFlag FROM resourceTree r JOIN cte2 ON (r.id = cte2.parentID) ) SELECT r.id, r.label, COALESCE(c2_1.ItemCount, c2_0.ItemCount, cte1.ItemCount, 0) AS ItemCount FROM resourceTree r LEFT JOIN cte1 ON r.id = cte1.id LEFT JOIN cte2 c2_0 ON (r.id = c2_0.id AND c2_0.sumFlag = 0) LEFT JOIN (SELECT id, label, SUM(ItemCount) AS ItemCount FROM CTE2 WHERE sumFlag = 1 GROUP BY id, label) c2_1 ON r.id = c2_1.id
Below is the result I got. Hope it helps. Thanks.
PS: I didn’t have to use the ‘NodeIsModel’ flag