Query Builder - advanced searching

Treasury masters! Consul Caesar.js has a new challenge for you - you'll learn the Query Builder, the most powerful tool for searching tributes in the entire Roman world. It's like a magical magnifying glass that lets you find exactly what you're looking for!

What is Query Builder?

Query Builder in TypeORM is like an experienced cartographer who can draw an exact map leading to every tribute. Instead of writing raw SQL queries, we use TypeScript methods that are safer and more readable.

Basic Query Builder

1// Instead of this:
2const tributes = await connection.query(
3 'SELECT * FROM tributes WHERE value > $1 ORDER BY value DESC',
4 [1000]
5);
6
7// We use this:
8const tributes = await tributeRepository
9 .createQueryBuilder('tribute')
10 .where('tribute.value > :minValue', { minValue: 1000 })
11 .orderBy('tribute.value', 'DESC')
12 .getMany();

Basic Query Builder operations

Simple queries

1// tribute.service.ts
2import { Injectable } from '@nestjs/common';
3import { InjectRepository } from '@nestjs/typeorm';
4import { Repository } from 'typeorm';
5import { Tribute } from './tribute.entity';
6
7@Injectable()
8export class TributeQueryService {
9 constructor(
10 @InjectRepository(Tribute)
11 private tributeRepository: Repository<Tribute>,
12 ) {}
13
14 // Find tributes by value
15 async findByValueRange(minValue: number, maxValue: number): Promise<Tribute[]> {
16 return this.tributeRepository
17 .createQueryBuilder('tribute')
18 .where('tribute.value >= :minValue', { minValue })
19 .andWhere('tribute.value <= :maxValue', { maxValue })
20 .orderBy('tribute.value', 'DESC')
21 .getMany();
22 }
23
24 // Text search
25 async searchByName(searchTerm: string): Promise<Tribute[]> {
26 return this.tributeRepository
27 .createQueryBuilder('tribute')
28 .where('tribute.name ILIKE :search', { search: `%${searchTerm}%` })
29 .orWhere('tribute.description ILIKE :search', { search: `%${searchTerm}%` })
30 .orderBy('tribute.collectedAt', 'DESC')
31 .getMany();
32 }
33
34 // Find recently discovered tributes
35 async findRecentlyDiscovered(daysAgo: number): Promise<Tribute[]> {
36 const cutoffDate = new Date();
37 cutoffDate.setDate(cutoffDate.getDate() - daysAgo);
38
39 return this.tributeRepository
40 .createQueryBuilder('tribute')
41 .where('tribute.collectedAt >= :cutoffDate', { cutoffDate })
42 .orderBy('tribute.collectedAt', 'DESC')
43 .getMany();
44 }
45}

Joining relations

1// Find tributes with owner information
2async findTributesWithOwners(): Promise<any[]> {
3 return this.tributeRepository
4 .createQueryBuilder('tribute')
5 .leftJoinAndSelect('tribute.legionaries', 'legionaries')
6 .leftJoinAndSelect('legionaries.centurion', 'centurion')
7 .where('legionaries.isActive = :isActive', { isActive: true })
8 .orderBy('tribute.value', 'DESC')
9 .getMany();
10}
11
12// Find legionaries with their tributes
13async findLegionarysWithTributes(): Promise<any[]> {
14 return this.legionariesRepository
15 .createQueryBuilder('legionaries')
16 .leftJoinAndSelect('legionaries.tributes', 'tribute')
17 .leftJoinAndSelect('legionaries.centurion', 'centurion')
18 .where('legionaries.tributeCount > :minTributes', { minTributes: 5 })
19 .orderBy('legionaries.totalTributeValue', 'DESC')
20 .getMany();
21}
22
23// Inner join - only legionaries with centurions
24async findLegionarysWithCenturions(): Promise<any[]> {
25 return this.legionariesRepository
26 .createQueryBuilder('legionaries')
27 .innerJoinAndSelect('legionaries.centurion', 'centurion')
28 .innerJoinAndSelect('legionaries.tributes', 'tribute')
29 .orderBy('centurion.name', 'ASC')
30 .addOrderBy('legionaries.name', 'ASC')
31 .getMany();
32}

Aggregations and statistics

1// Tribute statistics by type
2async getTributeStatsByType(): Promise<any[]> {
3 return this.tributeRepository
4 .createQueryBuilder('tribute')
5 .select('tribute.type', 'type')
6 .addSelect('COUNT(*)', 'count')
7 .addSelect('AVG(tribute.value)', 'avgValue')
8 .addSelect('SUM(tribute.value)', 'totalValue')
9 .addSelect('MIN(tribute.value)', 'minValue')
10 .addSelect('MAX(tribute.value)', 'maxValue')
11 .groupBy('tribute.type')
12 .orderBy('totalValue', 'DESC')
13 .getRawMany();
14}
15
16// Centurion ranking by legion tribute value
17async getCenturionRanking(): Promise<any[]> {
18 return this.centurionRepository
19 .createQueryBuilder('centurion')
20 .leftJoin('centurion.legion', 'legionaries')
21 .leftJoin('legionaries.tributes', 'tribute')
22 .select('centurion.id', 'centurionId')
23 .addSelect('centurion.name', 'centurionName')
24 .addSelect('centurion.cohortName', 'cohortName')
25 .addSelect('COUNT(DISTINCT legionaries.id)', 'legionSize')
26 .addSelect('COUNT(tribute.id)', 'totalTributes')
27 .addSelect('COALESCE(SUM(tribute.value), 0)', 'totalValue')
28 .groupBy('centurion.id')
29 .having('COUNT(DISTINCT legionaries.id) > :minLegion', { minLegion: 2 })
30 .orderBy('totalValue', 'DESC')
31 .getRawMany();
32}
33
34// Monthly discovery statistics
35async getMonthlyDiscoveryStats(): Promise<any[]> {
36 return this.tributeRepository
37 .createQueryBuilder('tribute')
38 .select('EXTRACT(YEAR FROM tribute.collectedAt)', 'year')
39 .addSelect('EXTRACT(MONTH FROM tribute.collectedAt)', 'month')
40 .addSelect('COUNT(*)', 'discoveries')
41 .addSelect('SUM(tribute.value)', 'totalValue')
42 .addSelect('AVG(tribute.value)', 'avgValue')
43 .where('tribute.collectedAt >= :startDate', {
44 startDate: new Date('2023-01-01')
45 })
46 .groupBy('EXTRACT(YEAR FROM tribute.collectedAt)')
47 .addGroupBy('EXTRACT(MONTH FROM tribute.collectedAt)')
48 .orderBy('year', 'DESC')
49 .addOrderBy('month', 'DESC')
50 .getRawMany();
51}

Advanced WHERE conditions

1// Complex logical conditions
2async findTributesByComplexCriteria(criteria: {
3 minValue?: number;
4 types?: string[];
5 locations?: string[];
6 discoveredAfter?: Date;
7 ownerRanks?: string[];
8 excludeCursed?: boolean;
9}): Promise<Tribute[]> {
10 let query = this.tributeRepository
11 .createQueryBuilder('tribute')
12 .leftJoinAndSelect('tribute.legionaries', 'legionaries')
13 .leftJoinAndSelect('legionaries.centurion', 'centurion');
14
15 // Dynamically adding conditions
16 if (criteria.minValue) {
17 query = query.andWhere('tribute.value >= :minValue', {
18 minValue: criteria.minValue
19 });
20 }
21
22 if (criteria.types && criteria.types.length > 0) {
23 query = query.andWhere('tribute.type IN (:...types)', {
24 types: criteria.types
25 });
26 }
27
28 if (criteria.locations && criteria.locations.length > 0) {
29 query = query.andWhere('tribute.location IN (:...locations)', {
30 locations: criteria.locations
31 });
32 }
33
34 if (criteria.discoveredAfter) {
35 query = query.andWhere('tribute.collectedAt >= :discoveredAfter', {
36 discoveredAfter: criteria.discoveredAfter
37 });
38 }
39
40 if (criteria.ownerRanks && criteria.ownerRanks.length > 0) {
41 query = query.andWhere('legionaries.rank IN (:...ranks)', {
42 ranks: criteria.ownerRanks
43 });
44 }
45
46 if (criteria.excludeCursed) {
47 query = query.andWhere('tribute.isCursed = :isCursed', {
48 isCursed: false
49 });
50 }
51
52 return query
53 .orderBy('tribute.value', 'DESC')
54 .getMany();
55}
56
57// Conditions with sub-queries
58async findLegionarysWithMostValuableTribute(): Promise<any[]> {
59 return this.legionariesRepository
60 .createQueryBuilder('legionaries')
61 .leftJoinAndSelect('legionaries.tributes', 'tribute')
62 .where(qb => {
63 const subQuery = qb.subQuery()
64 .select('MAX(t.value)')
65 .from(Tribute, 't')
66 .where('t.legionariesId = legionaries.id')
67 .getQuery();
68 return `tribute.value = (${subQuery})`

1 })
2 .orderBy('tribute.value', 'DESC')
3 .getMany();
4}

Pagination and limiting

1// Pagination with Query Builder
2async findTributesWithPagination(
3 page: number = 1,
4 limit: number = 10,
5 sortBy: string = 'value',
6 sortOrder: 'ASC' | 'DESC' = 'DESC'
7) {
8 const queryBuilder = this.tributeRepository
9 .createQueryBuilder('tribute')
10 .leftJoinAndSelect('tribute.legionaries', 'legionaries');
11
12 // Dynamic sorting
13 if (sortBy === 'collectedAt' || sortBy === 'value' || sortBy === 'name') {
14 queryBuilder.orderBy(`tribute.${sortBy}`, sortOrder);
15 } else if (sortBy === 'ownerName') {
16 queryBuilder.orderBy('legionaries.name', sortOrder);
17 }
18
19 const [tributes, total] = await queryBuilder
20 .skip((page - 1) * limit)
21 .take(limit)
22 .getManyAndCount();
23
24 return {
25 data: tributes,
26 pagination: {
27 page,
28 limit,
29 total,
30 totalPages: Math.ceil(total / limit),
31 hasNext: page * limit < total,
32 hasPrev: page > 1
33 }
34 };
35}
36
37// Top N results
38async getTopTributesByValue(limit: number = 10): Promise<Tribute[]> {
39 return this.tributeRepository
40 .createQueryBuilder('tribute')
41 .leftJoinAndSelect('tribute.legionaries', 'legionaries')
42 .orderBy('tribute.value', 'DESC')
43 .limit(limit)
44 .getMany();
45}

Raw queries and SQL functions

1// Using SQL functions
2async getTributeDistanceFromPort(portLat: number, portLng: number): Promise<any[]> {
3 return this.tributeRepository
4 .createQueryBuilder('tribute')
5 .select('tribute.*')
6 .addSelect(
7 `SQRT(POW(tribute.latitude - :portLat, 2) + POW(tribute.longitude - :portLng, 2))`,
8 'distance'
9 )
10 .where('tribute.latitude IS NOT NULL')
11 .andWhere('tribute.longitude IS NOT NULL')
12 .orderBy('distance', 'ASC')
13 .setParameters({ portLat, portLng })
14 .getRawMany();
15}
16
17// Advanced text functions
18async searchTributesFullText(searchTerm: string): Promise<Tribute[]> {
19 return this.tributeRepository
20 .createQueryBuilder('tribute')
21 .where(
22 `to_tsvector('english', tribute.name || ' ' || COALESCE(tribute.description, ''))
23 @@ plainto_tsquery('english', :searchTerm)`,
24 { searchTerm }
25 )
26 .orderBy(
27 `ts_rank(to_tsvector('english', tribute.name || ' ' || COALESCE(tribute.description, '')),
28 plainto_tsquery('english', :searchTerm))`,
29 'DESC'
30 )
31 .setParameter('searchTerm', searchTerm)
32 .getMany();
33}
34
35// Date functions
36async getTributesByDateRange(
37 startDate: Date,
38 endDate: Date,
39 groupBy: 'day' | 'week' | 'month' = 'day'
40): Promise<any[]> {
41 const dateFormat = {
42 day: 'YYYY-MM-DD',
43 week: 'YYYY-"W"WW',
44 month: 'YYYY-MM'
45 };
46
47 return this.tributeRepository
48 .createQueryBuilder('tribute')
49 .select(`TO_CHAR(tribute.collectedAt, '${dateFormat[groupBy]}')`, 'period')
50 .addSelect('COUNT(*)', 'discoveries')
51 .addSelect('SUM(tribute.value)', 'totalValue')
52 .where('tribute.collectedAt BETWEEN :startDate AND :endDate', {
53 startDate,
54 endDate
55 })
56 .groupBy('period')
57 .orderBy('period', 'ASC')
58 .getRawMany();
59}

Performance optimization

1// Indexes in queries
2async findTributesOptimized(type: string, minValue: number): Promise<Tribute[]> {
3 return this.tributeRepository
4 .createQueryBuilder('tribute')
5 .useIndex('IDX_tribute_type_value') // Use a specific index
6 .where('tribute.type = :type', { type })
7 .andWhere('tribute.value >= :minValue', { minValue })
8 .orderBy('tribute.value', 'DESC')
9 .getMany();
10}
11
12// Batch processing for large datasets
13async processTributesBatch(
14 batchSize: number = 1000,
15 processor: (tributes: Tribute[]) => Promise<void>
16): Promise<void> {
17 let offset = 0;
18 let hasMore = true;
19
20 while (hasMore) {
21 const batch = await this.tributeRepository
22 .createQueryBuilder('tribute')
23 .orderBy('tribute.id', 'ASC')
24 .skip(offset)
25 .take(batchSize)
26 .getMany();
27
28 if (batch.length === 0) {
29 hasMore = false;
30 } else {
31 await processor(batch);
32 offset += batchSize;
33 hasMore = batch.length === batchSize;
34 }
35 }
36}
37
38// Query with EXPLAIN for performance analysis
39async explainQuery(type: string): Promise<any> {
40 const query = this.tributeRepository
41 .createQueryBuilder('tribute')
42 .where('tribute.type = :type', { type })
43 .orderBy('tribute.value', 'DESC');
44
45 // In dev environment you can analyze the query plan
46 const sql = query.getSql();
47 console.log('Generated SQL:', sql);
48
49 return query.getMany();
50}

Transactions with Query Builder

1// Transactional operations
2async transferTribute(
3 tributeId: number,
4 fromLegionaryId: number,
5 toLegionaryId: number
6): Promise<void> {
7 await this.tributeRepository.manager.transaction(async manager => {
8 // Update tribute owner
9 await manager
10 .createQueryBuilder()
11 .update(Tribute)
12 .set({ legionaries: { id: toLegionaryId } })
13 .where('id = :tributeId', { tributeId })
14 .andWhere('legionaries.id = :fromLegionaryId', { fromLegionaryId })
15 .execute();
16
17 // Update legionary counters
18 await manager
19 .createQueryBuilder()
20 .update(Legionary)
21 .set({
22 tributeCount: () => 'tribute_count - 1',
23 totalTributeValue: () => `total_tribute_value - (
24 SELECT value FROM tributes WHERE id = ${tributeId}
25 )`
26 })
27 .where('id = :fromLegionaryId', { fromLegionaryId })
28 .execute();
29
30 await manager
31 .createQueryBuilder()
32 .update(Legionary)
33 .set({
34 tributeCount: () => 'tribute_count + 1',
35 totalTributeValue: () => `total_tribute_value + (
36 SELECT value FROM tributes WHERE id = ${tributeId}
37 )`
38 })
39 .where('id = :toLegionaryId', { toLegionaryId })
40 .execute();
41 });
42}

Testing Query Builder

1// tribute-query.service.spec.ts
2import { Test, TestingModule } from '@nestjs/testing';
3import { getRepositoryToken } from '@nestjs/typeorm';
4import { TributeQueryService } from './tribute-query.service';
5import { Tribute } from './tribute.entity';
6
7describe('TributeQueryService', () => {
8 let service: TributeQueryService;
9 let mockRepository: any;
10
11 const mockQueryBuilder = {
12 where: jest.fn().mockReturnThis(),
13 andWhere: jest.fn().mockReturnThis(),
14 orWhere: jest.fn().mockReturnThis(),
15 orderBy: jest.fn().mockReturnThis(),
16 getMany: jest.fn(),
17 getRawMany: jest.fn(),
18 leftJoinAndSelect: jest.fn().mockReturnThis()
19 };
20
21 beforeEach(async () => {
22 mockRepository = {
23 createQueryBuilder: jest.fn(() => mockQueryBuilder)
24 };
25
26 const module: TestingModule = await Test.createTestingModule({
27 providers: [
28 TributeQueryService,
29 {
30 provide: getRepositoryToken(Tribute),
31 useValue: mockRepository
32 }
33 ]
34 }).compile();
35
36 service = module.get<TributeQueryService>(TributeQueryService);
37 });
38
39 describe('findByValueRange', () => {
40 it('should find tributes in value range', async () => {
41 const mockTributes = [
42 { id: 1, name: 'Golden Coin', value: 1500 }
43 ];
44
45 mockQueryBuilder.getMany.mockResolvedValue(mockTributes);
46
47 const result = await service.findByValueRange(1000, 2000);
48
49 expect(mockRepository.createQueryBuilder).toHaveBeenCalledWith('tribute');
50 expect(mockQueryBuilder.where).toHaveBeenCalledWith(
51 'tribute.value >= :minValue',
52 { minValue: 1000 }
53 );
54 expect(mockQueryBuilder.andWhere).toHaveBeenCalledWith(
55 'tribute.value <= :maxValue',
56 { maxValue: 2000 }
57 );
58 expect(result).toEqual(mockTributes);
59 });
60 });
61});

Query Builder best practices

1. Use aliases

1// GOOD
2const tributes = await repository
3 .createQueryBuilder('tribute')
4 .leftJoinAndSelect('tribute.legionaries', 'legionaries')
5 .where('tribute.value > :value', { value: 1000 })
6 .getMany();
7
8// BAD
9const tributes = await repository
10 .createQueryBuilder()
11 .where('value > 1000')
12 .getMany();

2. Parameterize queries

1// GOOD
2.where('tribute.name = :name', { name: userInput })
3
4// BAD (vulnerable to SQL injection)
5.where(`tribute.name = '${userInput}'`)

3. Optimize joins

1// Load only needed relations
2.leftJoinAndSelect('tribute.legionaries', 'legionaries')
3.leftJoin('legionaries.centurion', 'centurion') // Without Select if we don't need the data
4.addSelect('centurion.name') // Add only needed fields

Summary

Now you're a true search master! You can:

✓ Create complex queries with Query Builder ✓ Use aggregations and statistics for data analysis ✓ Optimize query performance ✓ Implement pagination and sorting ✓ Test Query Builder in unit tests

Query Builder is your best guide through the vast territory of data - it lets you find exactly what you're looking for!