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, @name: 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()

.