Query Builder - advanced searches

Consul Caesar.js stands before you with a new assignment: "The censor asks which centurions collected the most valuable tributes this month. He wants the list sorted from the richest down." You open the repository you know well... and stop. The find() method handles simple questions beautifully: "give me tributes worth over 1000 denarii", "find a legionary by name". But summing, grouping by centurion, conditions assembled only while the program runs - none of that can find() express.

For questions like these, TypeORM has a more powerful tool: the Query Builder. Think of it as the treasury's cartographer - you build the query step by step, method by method, and at the end it draws one precise map (the SQL query) out of those steps and sends it to the database.

Your first query, step by step

Let's start with a question you could also ask through find() - that's the easiest way to see what the Query Builder does differently. We are looking for tributes worth more than 1000 denarii, most valuable first:

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

Let's walk the chain. createQueryBuilder('tribute') opens the build and gives the entity an alias - a short nickname we use in every later method, writing tribute.value or tribute.name. where(...) adds a condition, orderBy(...) adds sorting. Only getMany() glues those blocks into SQL and sends it to the database - before that, nothing reaches the database at all. The result is a plain array of Tribute[] entities, exactly what find() would have given you - the way of building the question changed, the shape of the answer did not.

Parameters - a shield against hostile orders

In the where condition we did not write the number 1000 directly. In its place stands :minValue - a parameter, whose value we pass alongside, in the object { minValue: 1000 }. Why the separation? Imagine the value comes from a user. Pasted straight into the query text, it could smuggle in a hostile order - like a messenger hiding a command to open the gates inside a letter. That attack is called SQL injection:

1// BAD - user data glued into the query text
2.where(`tribute.name = '${userInput}'`)
3
4// GOOD - data travels separately, as a parameter
5.where('tribute.name = :name', { name: userInput })

The difference is fundamental: a parameter is never glued into the SQL text. The database receives it through a separate channel and treats it purely as data - even if someone types a fragment of a malicious query into a form, it stays nothing more than odd-looking text. So adopt an iron rule: every value in a query goes through a parameter. No exceptions.

Chaining conditions

Real searches rarely have a single condition. You add the next one with andWhere - it must hold together with the previous ones. There is also orWhere - it is enough that any one of them holds:

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

This query finds tributes that are golden and worth at least 1000 denarii. And because the query is an ordinary object built with methods, we can also add conditions conditionally - inside an 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();

And this is the moment the Query Builder leaves find() far behind: the query grows while the program runs, condition by condition, depending on what the user asks for - and it still travels to the database as a single SQL statement, only at getMany().

Joining relations

Tributes do not exist in a vacuum - each belongs to a legionary, and every legionary serves under a centurion. To fetch tributes together with their owners' data, we join the relation:

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

The name leftJoinAndSelect is two decisions written as one word. LeftJoin: attach the legionary, but do not discard a tribute that has no owner - its field will simply be empty (the twin innerJoinAndSelect would drop such a tribute from the result). AndSelect: also load the legionary's data into the result entities. The second argument, 'legionary', is the alias of the joined entity - it is what let us filter by legionary.isActive in the where. There is also plain leftJoin, without AndSelect: the relation then serves only for filtering and its data is not loaded - the result is lighter. That is what I recommend whenever you do not display the owner's data.

Statistics - when the result is not an entity

Back to the censor's question: the total value of tributes by type. The answer is not a list of tributes but a table of summaries - a few rows with counts and sums. For queries like this we pick the columns ourselves and close the build with getRawMany() instead of 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 picks the first column, every addSelect adds another, and the second argument names it in the result. groupBy folds rows into groups - COUNT and SUM are then computed separately for every tribute type. The ending is the crucial part: getMany() returns Tribute entities, but a sum or a count is not an entity - so we call getRawMany(), which returns raw rows with the fields you named yourself: { type, count, totalValue }.

Pagination - the treasury served in portions

The Empire's treasury holds thousands of tributes and nobody views them all at once. We serve results in pages:

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 skips the results belonging to previous pages, take fetches one portion. getManyAndCount() returns a pair: the portion of results and the total number of all matching rows - from it you compute the page count: Math.ceil(total / limit). Note that orderBy is not decoration here but a correctness requirement: without a fixed order the database could serve rows differently every time, and pages would start to overlap.

Summary

Look how many answers you can already draw out of the treasury:

• createQueryBuilder('alias') opens the build, and the alias is the entity's nickname used inside it,
• you chain conditions with where, andWhere and orWhere - dynamically too, inside an if,
• every value travels as a :name parameter - your shield against SQL injection,
• you join relations with leftJoinAndSelect, or the lighter leftJoin when they only filter,
• you build statistics with select, addSelect and groupBy, collecting them with getRawMany(),
• you cut result pages with the skip and take pair, and getManyAndCount() adds the total count.

In the next lesson you will meet transactions - a way to make several treasury operations execute as one: all of them, or none. For now remember: the Query Builder is a cartographer - you lay down the blocks, and it draws them into a single SQL map only when you call getMany().

1// Query Builder - Advanced Searches in the Treasury
2import { Injectable } from '@nestjs/common';
3import { InjectRepository } from '@nestjs/typeorm';
4import { Repository } from 'typeorm';
5
6console.log("Query Builder - a powerful search tool!");
7
8// ===========================================
9// 1. Basic QueryBuilder
10// ===========================================
11
12@Injectable()
13export class LegionQueryService {
14  constructor(
15    @InjectRepository('Legionary')
16    private legionaryRepo: Repository<any>,
17  ) {}
18
19  // Find experienced legionaries
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  // Search by name
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  // Filtering with multiple conditions
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 with a relation
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  // Aggregation - statistics
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  // 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=== QUERY BUILDER SUMMARY ===");
102console.log("createQueryBuilder('alias') - start a query");
103console.log(".where() / .andWhere() - filtering conditions");
104console.log(".orderBy() - sorting results");
105console.log(".leftJoinAndSelect() - joining with relations");
106console.log(".select() / .addSelect() - selecting columns");
107console.log(".groupBy() - grouping (aggregation)");
108console.log(".getMany() / .getRawMany() - fetching results");
109