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ę: 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().

1// Query Builder - Zaawansowane Poszukiwania w Skarbcu
2import { Injectable } from '@nestjs/common';
3import { InjectRepository } from '@nestjs/typeorm';
4import { Repository } from 'typeorm';
5
6console.log("Query Builder - potezne narzedzie wyszukiwania!");
7
8// ===========================================
9// 1. Podstawowy QueryBuilder
10// ===========================================
11
12@Injectable()
13export class LegionQueryService {
14  constructor(
15    @InjectRepository('Legionary')
16    private legionaryRepo: Repository<any>,
17  ) {}
18
19  // Znajdz doswiadczonych legionistow
20  async findExperienced(minExp: number) {
21    return this.legionaryRepo
22      .createQueryBuilder('legionary')
23      .where('legionary.experience >= :minExp', { minExp })
24      .orderBy('legionary.experience', 'DESC')
25      .getMany();
26  }
27
28  // Wyszukaj po imieniu
29  async searchByName(name: string) {
30    return this.legionaryRepo
31      .createQueryBuilder('legionary')
32      .where('legionary.name LIKE :name', { name: '%' + name + '%' })
33      .getMany();
34  }
35
36  // Filtrowanie z wieloma warunkami
37  async findByCriteria(rank?: string, minExp?: number, legion?: string) {
38    const qb = this.legionaryRepo.createQueryBuilder('l');
39
40    if (rank) {
41      qb.andWhere('l.rank = :rank', { rank });
42    }
43    if (minExp) {
44      qb.andWhere('l.experience >= :minExp', { minExp });
45    }
46    if (legion) {
47      qb.andWhere('l.legion = :legion', { legion });
48    }
49
50    return qb.orderBy('l.experience', 'DESC').getMany();
51  }
52}
53
54// ===========================================
55// 2. QueryBuilder z relacjami (JOIN)
56// ===========================================
57
58@Injectable()
59export class AdvancedQueryService {
60  constructor(
61    @InjectRepository('Legion')
62    private legionRepo: Repository<any>,
63  ) {}
64
65  // LEFT JOIN z relacja
66  async findLegionsWithCenturions() {
67    return this.legionRepo
68      .createQueryBuilder('legion')
69      .leftJoinAndSelect('legion.soldiers', 'soldier')
70      .where('soldier.rank = :rank', { rank: 'Centurio' })
71      .orderBy('legion.name', 'ASC')
72      .getMany();
73  }
74
75  // Agregacja - statystyki
76  async getLegionStats() {
77    return this.legionRepo
78      .createQueryBuilder('legion')
79      .select('legion.name', 'legionName')
80      .addSelect('COUNT(soldier.id)', 'soldierCount')
81      .addSelect('AVG(soldier.experience)', 'avgExperience')
82      .leftJoin('legion.soldiers', 'soldier')
83      .groupBy('legion.name')
84      .getRawMany();
85  }
86
87  // Podzapytanie (subquery)
88  async findEliteLegions() {
89    const subQuery = this.legionRepo
90      .createQueryBuilder('sub')
91      .select('AVG(sub.soldiers)', 'avg')
92      .getQuery();
93
94    return this.legionRepo
95      .createQueryBuilder('legion')
96      .where('legion.soldiers > (' + subQuery + ')')
97      .getMany();
98  }
99}
100
101console.log("\n=== PODSUMOWANIE QUERY BUILDER ===");
102console.log("createQueryBuilder('alias') - rozpocznij zapytanie");
103console.log(".where() / .andWhere() - warunki filtrowania");
104console.log(".orderBy() - sortowanie wynikow");
105console.log(".leftJoinAndSelect() - laczenie z relacjami");
106console.log(".select() / .addSelect() - wybor kolumn");
107console.log(".groupBy() - grupowanie (agregacja)");
108console.log(".getMany() / .getRawMany() - pobranie wynikow");
109