Listy oparte na zapytaniu SQL

Lista może pobierać dane z zapytania SQL zamiast z formularzy. W tym artykule sprawdzisz, co zapytanie musi zwracać, aby działały kolumny, filtry, sortowanie i panel filtrów.

Pozostałe ustawienia listy opisuje Tworzenie list, a przygotowanie funkcji w bazie danych — Tworzenie funkcji SQL.

1. Przygotuj zapytanie

W zakładce Ogólne wybierz źródło danych Baza danych SQL i wpisz zapytanie w polu Wyrażenia SQL:

SELECT * FROM _fn_get_invoices(@UserId, @LanguageId)

Wymaganie	Szczegóły
Jedno zapytanie SELECT	System osadza je jako podzapytanie i sam dodaje filtry, sortowanie i stronicowanie.
Bez ORDER BY	Listy z ORDER BY w wyrażeniu nie da się zapisać. Kolejność wierszy ustaw w sortowaniu listy.
Kolumna id	Wymagana, typu integer. System traktuje ją jako identyfikator dokumentu: po niej otwiera dokument z wiersza i wykonuje akcje na zaznaczonych wierszach. Zapytanie powinno więc być oparte na dokumentach.
Dane przez funkcję	Zapytanie jest wykonywane na połączeniu tylko do odczytu, bez bezpośredniego dostępu do tabel. Dane udostępnij funkcją SQL.

W zapytaniu możesz użyć dwóch parametrów:

Parametr	Wartość
@UserId	Identyfikator zalogowanego użytkownika.
@LanguageId	Język użytkownika: 1 — domyślny, 2 — polski, 3 — angielski.

2. Zapisz listę i ustaw kolumny

Kolumny listy powstają z kolumn zwracanych przez zapytanie. Zapisz listę, a potem w zakładce Kolumny nadaj kolumnom nazwy i typy danych.

• Każda nowa kolumna zapytania staje się kolumną listy typu Tekst. Kolumny id i document_id są pomijane.
• Kod kolumny to nazwa kolumny w wyniku zapytania. Lista odczytuje po nim wartości, więc musi pozostać taki sam.
• Po zmianie zapytania zapisz listę ponownie. System doda nowe kolumny i usunie te, których zapytanie już nie zwraca. Zmiana nazwy kolumny w zapytaniu oznacza więc nową kolumnę listy.

Dopasuj Typ danych do wartości zwracanej przez zapytanie:

Typ danych	Kolumna zapytania
Tekst	text
Liczba całkowita	integer
Liczba zmiennoprzecinkowa	numeric
Data	date
Data i czas	timestamptz
Pole wyboru	JSON opisany w kolejnym kroku

3. Zwróć wartości pól wyboru jako JSON

Kolumna typu Pole wyboru zwraca tablicę wybranych wartości. Każda wartość ma identyfikator id i etykietę value:

[{"id": 10, "value": "Pilne"}, {"id": 11, "value": "Eksport"}]

Element	Znaczenie
id	Liczba całkowita identyfikująca wartość. Po niej działają warunki filtrów (Równe, W, Nie W), filtr w nagłówku kolumny i panel filtrów.
value	Etykieta widoczna w komórce i w filtrach. Po niej działają wyszukiwarka, wiersz wyszukiwania pod nagłówkami i sortowanie.

Kolumna może mieć typ jsonb, json albo text z zapisanym JSON-em. Lista przyjmuje też uproszczone postaci:

Zapytanie zwraca	Lista widzi
Pojedynczy obiekt {"id": 10, "value": "Pilne"}	Jedną wartość.
NULL, pusty tekst, JSON null albo pustą tablicę []	Brak wartości — w filtrach pozycja Puste.
Wartość bez id	Nic — taka wartość jest pomijana.

Komórka może zawierać kilka wartości. Filtry i liczniki panelu filtrów uwzględniają każdą z nich, więc wiersz z wartościami „Pilne” i „Eksport” jest liczony przy obu. Sortowanie i wykresy korzystają z pierwszej wartości.

Przykład budowania takich kolumn w funkcji:

-- jedna wartość albo brak wartości
CASE WHEN c.id IS NOT NULL
    THEN jsonb_build_array(jsonb_build_object('id', c.id, 'value', c.name))
END AS contractor,

-- kilka wartości; brak wierszy daje NULL, czyli brak wartości
(SELECT jsonb_agg(jsonb_build_object('id', v.value_id, 'value', v.value_name_pl))
 FROM document_choice_values v
 WHERE v.document_id = d.id AND v.form_attribute_id = 123) AS tags

4. Ogranicz dane w funkcji

Filtr listy, wyszukiwarka, filtry nagłówków i wybór w panelu filtrów działają na wyniku zapytania, więc nie ograniczają pracy funkcji, która zwraca dane. Zapytanie jest przy tym wykonywane osobno dla wierszy, liczby wierszy, wartości filtra w nagłówku i każdej kategorii panelu filtrów.

Zakres danych ogranicz więc w samej funkcji, np. po @UserId, i sprawdź czas jej wykonania na danych zbliżonych do produkcyjnych.

5. Dodaj panel filtrów

Tryb Filtry formularza nie jest dostępny dla list SQL, dlatego w zakładce Panel filtrów wybierasz Wyłączony albo Własna konfiguracja. W trybie Własna konfiguracja dodaj Kategorie filtrów:

Typ kategorii	Co pokazuje panel
Filtr na podstawie danych z pola	Wartości wskazanej kolumny z liczbą wierszy, np. „KSeF (32)”. Wiersze bez wartości trafiają do pozycji „Puste (12)”. Kolumny dat tworzą drzewo: rok, miesiąc, dzień.
Kreator filtrów	Opcje z własnymi warunkami, np. „Powyżej 1000”. Każda opcja ma swój licznik.

Liczniki działają tak:

• Licznik podaje liczbę wierszy z daną wartością wśród wierszy, które spełniają filtry listy i wybór w pozostałych kategoriach.
• Wybór w kategorii nie zmienia jej własnych liczników, więc nadal widać, ile wierszy ma każda z pozostałych wartości.
• Liczone są wiersze zestawienia. Jeśli zapytanie zwraca kilka wierszy z tym samym id, każdy jest liczony osobno.

Dalej: Tworzenie filtrów na liście lub Tworzenie funkcji SQL.