Query Builder - zaawansowane poszukiwania

Konsul Caesar.js staje przed Tobą z nowym zadaniem: "Cenzor pyta, którzy centurioni zebrali w tym miesiącu tributy o największej łącznej wartości. Chce listę posortowaną od najbogatszych." Otwierasz znane Ci repozytorium... i zatrzymujesz się. Metoda

find()

świetnie radzi sobie z prostymi pytaniami: "daj tributy warte ponad 1000 denarów", "znajdź legionistę po imieniu". Ale sumowanie, grupowanie po centurionie, warunki budowane dopiero w trakcie działania programu - tego

find()

nie wyrazi.

Do takich pytań TypeORM ma potężniejsze narzędzie: Query Builder. Pomyśl o nim jak o kartografie skarbca - zapytanie budujesz krok po kroku, metoda po metodzie, a on na końcu rysuje z tych kroków jedną precyzyjną mapę (zapytanie SQL) i wysyła ją do bazy.

Pierwsze zapytanie krok po kroku

Zacznijmy od pytania, które umiałbyś zadać także przez

find()

- tak najłatwiej zobaczyć, co Query Builder robi inaczej. Szukamy tributów wartych ponad 1000 denarów, od najcenniejszych:

1const tributes = await this.tributeRepository
2  .createQueryBuilder('tribute')
3  .where('tribute.value > :minValue', { minValue: 1000 })
4  .orderBy('tribute.value', 'DESC')
5  .getMany();

Prześledźmy łańcuch.

createQueryBuilder('tribute')

otwiera budowę zapytania i nadaje encji alias - krótki przydomek, którym posługujemy się w kolejnych metodach, pisząc

tribute.value

czy

tribute.name

.

where(...)

dodaje warunek, a

orderBy(...)

sortowanie. Dopiero

getMany()

skleja z tych klocków SQL i wysyła go do bazy - wcześniej do bazy nie idzie nic. Wynik to zwykła tablica encji

Tribute[]

, dokładnie taka sama, jaką dostałbyś z

find()

- zmienił się sposób budowania pytania, nie kształt odpowiedzi.

Parametry - tarcza przeciw wrogim rozkazom

W warunku

where

nie wpisaliśmy liczby 1000 wprost. Zamiast niej stoi

:minValue

- parametr, którego wartość podajemy obok, w obiekcie

{ minValue: 1000 }

. Po co to rozdzielenie? Wyobraź sobie, że wartość pochodzi od użytkownika. Wklejona bezpośrednio do tekstu zapytania, mogłaby przemycić wrogi rozkaz - jak posłaniec, który w treści listu ukrywa polecenie otwarcia bram. Ten atak nazywa się SQL injection:

1// ŹLE - dane użytkownika sklejone z tekstem zapytania
2.where(`tribute.name = '${userInput}'`)
3
4// DOBRZE - dane wędrują osobno, jako parametr
5.where('tribute.name = :name', { name: userInput })

Różnica jest zasadnicza: parametr nigdy nie jest sklejany z tekstem SQL. Baza dostaje go osobnym kanałem i traktuje wyłącznie jako dane - nawet jeśli ktoś wpisze w formularzu fragment złośliwego zapytania, pozostanie on tylko dziwnie wyglądającym tekstem. Dlatego przyjmij żelazną zasadę, @name: każda wartość w zapytaniu przechodzi przez parametr. Bez wyjątków.

Sklejanie warunków

Prawdziwe poszukiwania rzadko mają jeden warunek. Kolejne dokładasz metodą

andWhere

- warunek musi być spełniony razem z poprzednimi. Jest też

orWhere

- wystarczy, że spełniony będzie którykolwiek:

1const tributes = await this.tributeRepository
2  .createQueryBuilder('tribute')
3  .where('tribute.value >= :min', { min: 1000 })
4  .andWhere('tribute.type = :type', { type: 'gold' })
5  .getMany();

To zapytanie znajdzie tributy, które są złote i zarazem warte co najmniej 1000 denarów. A ponieważ zapytanie jest zwykłym obiektem budowanym metodami, warunki możemy dokładać także warunkowo - w

if

:

1let query = this.tributeRepository.createQueryBuilder('tribute');
2
3if (criteria.minValue) {
4  query = query.andWhere('tribute.value >= :min', { min: criteria.minValue });
5}
6if (criteria.type) {
7  query = query.andWhere('tribute.type = :type', { type: criteria.type });
8}
9
10const tributes = await query.orderBy('tribute.value', 'DESC').getMany();

I to jest moment, w którym Query Builder zostawia

find()

daleko w tyle: zapytanie rośnie w trakcie działania programu, warunek po warunku, zależnie od tego, o co pyta użytkownik - a do bazy i tak poleci jako jeden SQL, dopiero przy

getMany()

.

Dołączanie relacji

Tributy nie istnieją w próżni - każdy należy do legionisty, a legionista służy pod centurionem. Aby pobrać tributy razem z danymi właścicieli, dołączamy relację:

1const tributes = await this.tributeRepository
2  .createQueryBuilder('tribute')
3  .leftJoinAndSelect('tribute.legionariusze', 'legionista')
4  .where('legionista.isActive = :active', { active: true })
5  .getMany();

Nazwa

leftJoinAndSelect

to dwie decyzje zapisane w jednym słowie. LeftJoin: dołącz legionistę, ale tributu bez właściciela nie odrzucaj - jego pole będzie po prostu puste (bliźniacze

innerJoinAndSelect

odrzuciłoby taki tribut z wyniku). AndSelect: załaduj dane legionisty do encji wyniku. Drugi argument,

'legionista'

, to alias dołączonej encji - dzięki niemu mogliśmy w

where

filtrować po

legionista.isActive

. Istnieje też samo

leftJoin

, bez

AndSelect

: relacja posłuży wtedy tylko do filtrowania, a jej dane nie zostaną załadowane - wynik będzie lżejszy. I to polecam, gdy danych właściciela nie wyświetlasz.

Statystyki - kiedy wynik nie jest encją

Wróćmy do pytania cenzora: łączna wartość tributów według typu. Odpowiedzią nie jest lista tributów, tylko tabela podsumowań - kilka wierszy z licznikami i sumami. Do takich zapytań kolumny wybieramy sami i zamykamy budowę metodą

getRawMany()

zamiast

getMany()

:

1const stats = await this.tributeRepository
2  .createQueryBuilder('tribute')
3  .select('tribute.type', 'type')
4  .addSelect('COUNT(*)', 'count')
5  .addSelect('SUM(tribute.value)', 'totalValue')
6  .groupBy('tribute.type')
7  .orderBy('totalValue', 'DESC')
8  .getRawMany();

select

wybiera pierwszą kolumnę, każdy

addSelect

dokłada następną, a drugi argument nadaje jej nazwę w wyniku.

groupBy

zwija wiersze w grupy -

COUNT

i

SUM

liczą się wtedy osobno dla każdego typu tributu. Kluczowe jest zakończenie:

getMany()

zwraca encje

Tribute

, ale suma czy licznik encją nie są - dlatego wołamy

getRawMany()

, które zwraca surowe wiersze o polach nazwanych przez Ciebie:

{ type, count, totalValue }

.

Paginacja - skarbiec podawany porcjami

Skarbiec Imperium liczy tysiące tributów i nikt nie ogląda ich naraz. Wyniki podajemy stronami:

1const [tributes, total] = await this.tributeRepository
2  .createQueryBuilder('tribute')
3  .orderBy('tribute.value', 'DESC')
4  .skip((page - 1) * limit)
5  .take(limit)
6  .getManyAndCount();

skip

pomija wyniki należące do poprzednich stron,

take

pobiera jedną porcję.

getManyAndCount()

zwraca parę: porcję wyników i łączną liczbę wszystkich pasujących wierszy - z niej policzysz liczbę stron:

Math.ceil(total / limit)

. Zwróć uwagę, że

orderBy

nie jest tu ozdobą, a warunkiem poprawności: bez ustalonej kolejności baza mogłaby podawać wiersze za każdym razem inaczej i strony zaczęłyby się mieszać.

Podsumowanie

Spójrz, ile odpowiedzi potrafisz już wydobyć ze skarbca:

• createQueryBuilder('alias')

  otwiera budowę zapytania, a alias to przydomek encji używany w jego wnętrzu,
• warunki sklejasz metodami

  where

  ,

  andWhere

  i

  orWhere

  - także dynamicznie, w

  if

  ,
• każda wartość wędruje jako parametr

  :nazwa

  - to tarcza przeciw SQL injection,
• relacje dołączasz przez

  leftJoinAndSelect

  , a gdy służą tylko do filtrowania - lżejszym

  leftJoin

  ,
• statystyki budujesz z

  select

  ,

  addSelect

  i

  groupBy

  , odbierając je przez

  getRawMany()

  ,
• strony wyników wycinasz parą

  skip

  i

  take

  , a

  getManyAndCount()

  dorzuca łączny licznik.

W następnej lekcji poznasz transakcje - sposób, by kilka operacji na skarbcu wykonało się jak jedna: albo wszystkie, albo żadna. A na razie zapamiętaj: Query Builder to kartograf - Ty dokładasz klocki, a on rysuje z nich jedną mapę SQL dopiero wtedy, gdy zawołasz

getMany()

.