Το πρόβλημα

Πριν από μερικές μέρες μια συνάδελφος ήρθε με το εξής πρόβλημα, ήθελε να δείξει κάποια δεδομένα σε ένα treeview control σε ένα web page. Στην ουσία ήταν μια ιεραρχία που από την δομή του πίνακα έβγαινε αρκετά εύκολα με ένα order by clause. Όμως δεν ήταν τόσο απλά τα πράγματα, ήθελε να υπάρχει ταξινόμηση ανά επίπεδο ιεραρχίας το οποίο ορίζονταν από ένα άλλο πεδίο.

Για να εξηγήσουμε καλύτερα την πρόκληση αυτή ας έρθουμε να δούμε το πώς ήταν τα δεδομένα της

Ο πίνακας της ήταν ο εξής

CREATE TABLE T (A INT, B INT, C INT, D INT, P INT)

Το πεδίο Α ορίζει το 1^ο level της ιεραρχίας, το Β το 2^ο, το C το 3^ο, το D το 4^ο και το P είναι αυτό που ορίζει το σειρά του αντίστοιχου στοιχείου στο level σε σχέση με τα άλλα στοιχεία στο ίδιο level.

Ας έρθουμε να γεμίσουμε τον πίνακα αυτό με μερικά δεδομένα

-- 1st Level
insert into T values (1,null,null,null,2), (2,null,null,null,1), (3,null,null,null,3)

--2nd Level
insert into T
select y.A,x.A,x.B,null,x.P from T x cross join T y

--3rd Level
insert into T
select distinct y.A,x.A,x.B,null,x.P from (select * from T where B is not null) x
cross join (select * from T where B is not null) y

--4th Level
insert into T
select distinct y.A,x.A,x.B,x.C,x.P from (select * from T where C is not null) x
cross join (select * from T where C is not null) y

Εάν εκτελέσουμε το παρακάτω query θα έχουμε άμεσα την ιεραρχία μας αλλά χωρίς τον ορισμό της σειράς του στοιχείου ανά level.

select * from T order by A,B,C,D

To αποτέλεσμα μας θα είναι το παρακάτω

Εάν κάποιος δοκιμάσει να να λύσει το πρόβλημα αυτό απλά βάζοντας και το P στο order by δεν θα πάρει το επιθυμητό αποτέλεσμα καθώς θέλω διαφορετικό ordering ανά level.

Η λύση

Για να λυθεί η άσκηση αυτή ιδανικό και εύκολο στην υλοποίηση pattern είναι αυτό των Common Table Expressions (CTE).

Ας δούμε την λύση πρώτα και ας την εξηγήσουμε αμέσως μετά

WITH R (A,B,C,D,P,O)
AS 
(
-- 1st level
SELECT A,B,C,D,P,RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5) 
                FROM T WHERE B IS NULL 
-- 2nd level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
    FROM T WHERE B IS NOT NULL AND C IS NULL 
-- 3rd Level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)+
                 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                 +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5) 
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NULL 
--4th level
UNION ALL
SELECT A,B,C,D,P,RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B =T.B AND X.C=T.C AND D IS NULL) AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(C AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
                +RIGHT('00000'+CAST(D AS NVARCHAR(5)),5)
                  
    FROM T WHERE B IS NOT NULL AND C IS NOT NULL AND D IS NOT NULL 
)
SELECT * FROM R ORDER BY O

Η εξήγηση της λύσης

Στην περίπτωση μας ξέρουμε τον αριθμό των επιπέδων που έχουμε, καθώς επίσης ξέρουμε και το κριτήριο με το οποίο σε κάθε επίπεδο θέλουμε να τα ταξινομήσουμε το οποίο δεν είναι άλλο από το πεδίο P.

Για να το πετύχουμε αυτό θα πρέπει να έχουμε παράξουμε μια τιμή (Ο στο CTE) που με βάση αυτή θα γίνεται η τελική ταξινόμηση. Η τιμή αυτή θα πρέπει να έχει την δομή ταξινόμηση-level για κάθε γραμμή δεδομένων για το αντίστοιχο level, αλλά και για τους πατέρες αυτού.

Αρχίζοντας από το 1^ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NULL) όπου παράγουμε την τιμή για το Ο πεδίο προσθέτοντας την θέση που ορίζετε από το P και το level που ορίζεται από το πεδίο A ως εξής

RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)+RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)

Ο λόγος που χρησιμοποιούμε την μορφή αυτή είναι γιατί πρέπει να εξασφαλίσουμε την ορθή ταξινόμιση καθώς αν δεν χρησιμοποιούμε την RIGHT(‘00000’+CAST(…),5) θα βρεθούμε αντιμέτωποι με το 15 να έρχετε πριν το 2 αν υποθέσουμε ότι οι τιμές σε δύο διαφορετικά records ήταν αυτές.

Συνεχίζουμε με το 2^ο level (το εξασφαλίζουμε αυτό με το WHERE B IS NOT NULL AND C IS NULL) παράγουμε την τιμή τιμή για το Ο πεδίο όπως παραπάνω αλλά επειδή είναι το δεύτερο επίπεδο θα πρέπει να προσθέσουμε την αντίστοιχη τιμή του πατέρα του πριν από αυτή την οποία και διαβάζουμε με ένα correlated subquery  όπως παρακάτω

  RIGHT('00000'+CAST((SELECT P FROM T X WHERE X.A =T.A AND X.B IS NULL) AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(A AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(P AS NVARCHAR(5)),5)
 +RIGHT('00000'+CAST(B AS NVARCHAR(5)),5)

Πράττουμε το ίδιο και για τα επόμενα επίπεδα και στο τέλος λέμε στο CTE να τα κάνει order by O. (SELECT * FROM R ORDER BY O)

Το τελικό αποτέλεσμα

Αυτό θα έχει σαν αποτέλεσμα να ικανοποιήσουμε την ανάγκη μας στο 100% όπως φαίνεται παρακάτω μετά από την εκτέλεση της παραπάνω λύσης.

Reusability

Το παραπάνω query μπορεί εύκολα να γίνει ένα view το οποίο θα μπορεί να καλεστεί από οπουδήποτε και να παρέχει άμεσα το επιθυμητό αποτέλεσμα χωρίς να χρειαστεί να κάνω κάτι έξτρα στην εφαρμογή που θα το καλέσει. (ξέρετε τώρα αγαπητοί μου συνάδελφοι developers, λίστες επί λιστών, loops μέσα σε loops και άλλα τέτοια ωραία πράγματα τα οποία κάνουμε επειδή μας αρέσει το OOP (object oriented programming, η επεξήγηση αυτή για τους αγαπητούς admins),…). Απλά θέλει μια μικρή αλλαγή στο τελικό SELECT καθώς αυτό έχω ORDER BΥ και όπως είναι γνωστό για να παίξει αυτό σε view θέλει στο SELECT να βάλω την TOP. Για την περίπτωση αυτή το SELECT θα είναι το παρακάτω

SELECT TOP 9999999999 * FROM R ORDER BY O

antonch