Wąska lista z zapasowym pełnym zbiorem w jednym zapytaniu T-SQL

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

W SQL Server lista zależna od wyboru z zapasowym pełnym zbiorem pokazuje wąski zestaw pozycji dla wybranego produktu, a gdy produkt nie ma użytecznych przypisań, cały słownik. Pusta lista zatrzymałaby użytkownika przy polu, którego nie da się wypełnić. Cały wzorzec mieści się w jednym zapytaniu.

Zapytanie wybiera przez EXISTS aktywne wady przypisane do produktu, a gdy produkt nie ma aktywnego przypisania do aktywnej wady, zwraca przez NOT EXISTS wszystkie aktywne wady. Przypisanie do wady wycofanej nie liczy się jako przypisanie. Kolejność ustala ORDER BY po kolumnach kolejnosc i kod.

Dlaczego lista zależna od wyboru nie może być pusta

Lista zależna od wyboru jest wygodna, dopóki dla wyboru istnieją przypisania. Pracownik wybiera produkt, a pole rodzaju wady pokazuje tylko wady przypisane do tego produktu. Gdy przypisań nie ma, lista pusta zatrzymuje zgłoszenie na polu, którego nie można wypełnić.

Tę samą ideę od strony użytkownika formularza opisuje strona o rodzaju wady zależnym od produktu. Tutaj pokazujemy jej odpowiednik w bazie. Mapowanie jest danymi: dopisanie wiersza do tabeli przypisań zmienia listę bez zmiany kodu, a podobne podejście do koloru terminu opisuje strona o terminie reklamacji w kolorach oraz artykuł o kolorze z liczby dni w widoku.

Pierwsze pytanie brzmi, co ma zobaczyć użytkownik, który niczego jeszcze nie wybrał. Drugie dotyczy tego, co zrobić, gdy przypisania istnieją, ale wskazują pozycje, których nie wolno już wybierać. Oba przypadki wymagają odpowiedzi w samym zapytaniu, a nie w kodzie formularza.

Dwie tabele i jedna reguła

Przykład ma dwie tabele. Tabela wada ma kod jako klucz główny, a poza nim opis oraz dwie kolumny sterujące: kolejność i znacznik aktywności. Tabela produkt_wada ma klucz główny złożony z identyfikatora produktu i kodu wady oraz własny znacznik aktywności, a kod wady jest kluczem obcym do tabeli wad.

CREATE TABLE dbo.wada (
    kod varchar(10) NOT NULL PRIMARY KEY, opis nvarchar(60) NOT NULL,
    kolejnosc int NOT NULL, aktywne bit NOT NULL);
CREATE TABLE dbo.produkt_wada (
    produkt_id int NOT NULL,
    wada_kod varchar(10) NOT NULL REFERENCES dbo.wada (kod),
    aktywne bit NOT NULL,
    PRIMARY KEY (produkt_id, wada_kod));
INSERT dbo.wada VALUES
 ('W01', N'Wada powierzchni', 10, 1), ('W02', N'Wada działania', 20, 1),
 ('W03', N'Brak elementu w dostawie', 30, 1), ('W04', N'Uszkodzenie w transporcie', 40, 1),
 ('W05', N'Inna wada', 50, 1), ('W06', N'Wada wycofana', 60, 0);
INSERT dbo.produkt_wada VALUES
 (1, 'W01', 1), (1, 'W02', 1), (1, 'W03', 0),
 (3, 'W06', 1);

Przykład ilustracyjny, dane wymyślone. Produkt 1 ma dwa aktywne przypisania i jedno wyłączone, produkt 3 ma jedyne przypisanie do wady wycofanej.

Klucz obcy z kolumny wada_kod do tabeli wad nie pozwala zapisać przypisania do nieistniejącej wady: próba wpisu z nieistniejącym kodem kończy się błędem 547. Klucz główny złożony obsługuje obie części zapytania, więc osobny indeks nie był w teście potrzebny.

W danych produkt 1 ma przypisania W01 i W02 aktywne, a W03 z wyłączoną parą. Produkt 2 nie ma żadnych przypisań. Produkt 3 ma jedno przypisanie, i to do wady W06, którą słownik uznaje za wycofaną. W słowniku jest sześć wad, z czego pięć aktywnych.

Jedno zapytanie z EXISTS i NOT EXISTS

Reguła brzmi tak: lista pokazuje aktywne wady przypisane do produktu, a gdy produkt nie ma żadnego użytecznego przypisania, pokazuje wszystkie aktywne wady. Obie gałęzie są w jednym zapytaniu, w warunku WHERE w.aktywne = 1 AND (EXISTS (...) OR NOT EXISTS (...)).

DECLARE @produkt int = 1;
SELECT w.kod, w.opis
FROM dbo.wada AS w
WHERE w.aktywne = 1
  AND (   EXISTS (SELECT 1 FROM dbo.produkt_wada AS pw
                  WHERE pw.produkt_id = @produkt AND pw.wada_kod = w.kod AND pw.aktywne = 1)
       OR NOT EXISTS (SELECT 1 FROM dbo.produkt_wada AS pw
                      JOIN dbo.wada AS w2 ON w2.kod = pw.wada_kod AND w2.aktywne = 1
                      WHERE pw.produkt_id = @produkt AND pw.aktywne = 1))
ORDER BY w.kolejnosc, w.kod;

Przykład ilustracyjny, dane wymyślone. Jedno zapytanie zwraca wąską listę albo pełny zbiór zapasowy.

Predykat EXISTS występuje w zapytaniu dwa razy. Pierwszy EXISTS przepuszcza wady przypisane do produktu przez aktywną parę. NOT EXISTS łączy się z tabelą wad i sprawdza aktywność wady, więc jest prawdziwy tylko wtedy, gdy produkt nie ma żadnego aktywnego przypisania do aktywnej wady.

Kolejność wyniku ustala ORDER BY po kolumnach kolejnosc i kod. Pole kolejnosc ustawia administrator, a kod rozstrzyga remis. Bez ORDER BY kolejność wyniku nie jest gwarantowana, o czym przypomina artykuł o TOP bez ORDER BY w WMS.

Zapytanie zwraca kod i opis. Jako wartość pola wyboru powinien służyć kod, czyli stały klucz, a opis jako etykieta, bo opis bywa dłuższy niż kolumna docelowa i można go zmienić w słowniku. Jest to zalecenie projektowe wzorca, a nie wynik testu.

To samo zapytanie można umieścić w funkcji tablicowej z parametrem. Sprawdziliśmy, że wersja inline zwraca te same wyniki dla produktów 1 i 3. W funkcji ORDER BY stawia się przy wywołaniu, a kolumna kolejnosc wychodzi z funkcji. Alternatywą jest źródło listy, które przyjmuje pojedyncze zapytanie z parametrem, a konstrukcja IF EXISTS z ELSE wymagałaby dwóch zapytań.

CREATE FUNCTION dbo.wady_dla_produktu (@produkt int)
RETURNS TABLE
AS
RETURN
SELECT w.kod, w.opis, w.kolejnosc
FROM dbo.wada AS w
WHERE w.aktywne = 1
  AND (   EXISTS (SELECT 1 FROM dbo.produkt_wada AS pw
                  WHERE pw.produkt_id = @produkt AND pw.wada_kod = w.kod AND pw.aktywne = 1)
       OR NOT EXISTS (SELECT 1 FROM dbo.produkt_wada AS pw
                      JOIN dbo.wada AS w2 ON w2.kod = pw.wada_kod AND w2.aktywne = 1
                      WHERE pw.produkt_id = @produkt AND pw.aktywne = 1));
GO
SELECT kod, opis FROM dbo.wady_dla_produktu(1) ORDER BY kolejnosc, kod;

Przykład ilustracyjny. Wariant jako funkcja tablicowa inline, zgodnie z dokumentacją CREATE FUNCTION.

Wada wycofana w jedynym przypisaniu

Produkt 3 ma jedno przypisanie, do wady wycofanej W06. Gdyby zapytanie uznało je za przypisanie, wąska lista dla tego produktu byłaby pusta, bo EXISTS nie zwróci wady, która ma aktywne = 0. Dlatego NOT EXISTS zawiera złączenie z tabelą wad i warunek w2.aktywne = 1: przypisanie do wady wycofanej nie liczy się jako przypisanie, a produkt dostaje pełny zbiór aktywnych wad.

Przypisanie do pozycji wycofanej nie jest przypisaniem, więc zapytanie musi je pominąć także w warunku, który decyduje o zapasowym pełnym zbiorze.

W wariancie uproszczonym NOT EXISTS nie ma złączenia z tabelą wad. W teście dla produktu 3 zwrócił on zero wierszy: produkt ma przypisanie, ale do wady, której EXISTS nie zwróci, więc użytkownik dostaje pustą listę. Ten wariant opisujemy tylko prozą, bo jest błędem do uniknięcia, a nie wzorcem.

Para wyłączona, czyli z aktywne = 0, musi być pomijana zarówno w EXISTS, jak i w NOT EXISTS. W przeciwnym razie wyłączenie ostatniej pary zostawia pustą listę. Produkt, którego wszystkie pary są wyłączone, dostaje w teście pełną listę, tak jak produkt bez przypisań.

Takie produkty wykrywa zapytanie kontrolne. Zwraca produkty, których wszystkie aktywne przypisania wskazują wady wycofane, czyli te, dla których wąska lista po cichu staje się pełną. W danych testowych zwraca jeden produkt: 3 z jednym aktywnym przypisaniem.

SELECT pw.produkt_id, COUNT(*) AS aktywne_przypisania
FROM dbo.produkt_wada AS pw
JOIN dbo.wada AS w ON w.kod = pw.wada_kod
WHERE pw.aktywne = 1
GROUP BY pw.produkt_id
HAVING SUM(CASE WHEN w.aktywne = 1 THEN 1 ELSE 0 END) = 0;

Przykład ilustracyjny. Zapytanie kontrolne wskazuje produkty, dla których przypisania niczego nie zawężają. Wynik w danych przykładowych to wiersz 3 | 1.

Test na czterech przypadkach i plan zapytania

Test obejmuje cztery wartości parametru: numery produktów od 1 do 3 oraz NULL. Piąty wiersz tabeli to wariant uproszczony dla produktu 3, opisany wyżej prozą. Produkt 1 daje dwa wiersze, W01 oraz W02, a wyłączona para nie wpuszcza W03. Produkt 2 bez przypisań dostaje pięć aktywnych wad w kolejności od W01 do W05, a wada wycofana W06 nie występuje.

Pięć wierszy: cztery wartości parametru i wariant uproszczony dla produktu 3.

Gdy parametr ma wartość NULL, czyli użytkownik jeszcze nie wybrał produktu, wynik to pełna lista pięciu wad. Porównanie produkt_id = NULL nigdy nie jest prawdą, więc NOT EXISTS jest prawdziwe. Jeśli przed wyborem lista ma być pusta, trzeba dopisać AND @produkt IS NOT NULL. To wynika z logiki zapytania i nie było osobnym testem.

Plan sprawdziliśmy na większych wygenerowanych danych testowych dla produktu z przypisaniami. Zawierał wyłącznie Clustered Index Seek na kluczu głównym tabeli przypisań, po identyfikatorze produktu, a dla gałęzi EXISTS także po kodzie wady, bez skanu tej tabeli. Osobny indeks nie był potrzebny. Liczby odczytów zależą od danych i wersji, więc ich nie podajemy; nie obiecujemy też czasu wykonania. Plany opisuje dokumentacja o planach wykonania.

Granice wzorca

Przykład uruchomiliśmy na silniku SQL Server 2022 w jednorazowej bazie z poziomem zgodności 130. Kod nie używa funkcji nowszych niż SQL Server 2016, ale nie sprawdzaliśmy go na silniku 2016. Poziom zgodności opisuje dokumentacja o ALTER DATABASE.

Wzorzec zakłada, że znacznik aktywności znaczy to samo w obu tabelach. Gdy znaczenie kolumn zmienia się w dostosowaniu, odczyt po nazwach kolumn może dać fałsz, o czym pisze artykuł o kolumnach standardowych z nowym znaczeniem. Inny sposób na brak danych, opisany w artykule o odczycie stanu przez API, zwraca zamiast pustej tablicy komunikat ERR. Filtr oparty na EXISTS ma także funkcja tożsamości sesji, ale służy innemu celowi.

Przykład: pracownik wybiera w formularzu PROD-0001 i widzi na liście dwa rodzaje wady, Wadę powierzchni i Wadę działania. Dla PROD-0002, który nie ma jeszcze przypisań, lista pokazuje wszystkie pięć aktywnych rodzajów, więc zgłoszenie nie zatrzymuje się na pustym polu.

Administrator wycofuje rodzaj Wada wycofana, który był jedynym przypisaniem produktu PROD-0003. Lista tego produktu pokazuje wtedy pełny słownik, a zapytanie kontrolne wskazuje PROD-0003 do uzupełnienia przypisań. Przypisania dopisuje się wierszem w tabeli, bez zmiany kodu.

Cztery pytania przed budową listy zależnej

Przed budową własnej listy zależnej odpowiedz na cztery pytania.

• Podpowiedź albo ograniczenie: czy lista zależna ma być zawsze podpowiedzią, czy w pewnych przypadkach twardym ograniczeniem wyboru.
• Utrzymanie: kto utrzymuje przypisania i jak ma się dowiedzieć, że przypisania danego produktu przestały cokolwiek zawężać.
• Przed wyborem: co ma zobaczyć użytkownik, gdy jeszcze niczego nie wybrał: listę pustą czy pełną.
• Pozycje wycofane: czy mają znikać z list wyboru, ale zostawać w historycznych dokumentach.