TypeORM: search in relationships (ft. NestJS)

🤔 Why?

Because I didn’t want to perform a full search once I brought all the records. Since SQL is really optimized for queries. For this case, I wanted to filter some records based on their relationship attributes.

I’ll use a simple test case.

💡 First, we need two TypeORM entities

For the most simple of cases, let’s suppose we have two classes. A Pet and an Owner. A Pet will belong to only one Owner. Both entities live in the same folder.

1
// ./entities/pet.ts
2
import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
3
import { Owner } from './owner';
4

5
@Entity()
6
export class Pet {
7
  @PrimaryGeneratedColumn()
8
  id: number;
9

10
  @Column({ name: 'owner_id' })
11
  ownerId: number;
12

13
  @ManyToOne(() => Owner)
14
  @JoinColumn({ name: 'owner_id' })
15
  owner: Owner;
16
}

And

1
// ./entities/owner.ts
2
import { Column, Entity, JoinColumn, OneToMany, PrimaryGeneratedColumn } from 'typeorm';
3
import { Pet } from './pet';
4

5
@Entity()
6
export class Owner {
7
  @PrimaryGeneratedColumn()
8
  id: number;
9

10
  @Column({ name: 'first_name' })
11
  firstName: string;
12

13
  @Column({ name: 'last_name' })
14
  lastName: string;
15

16
  @OneToMany(() => Pet, () => Owner)
17
  @JoinColumn({ name: 'owner_id' })
18
  pets: Pet[];
19
}

🔨 Creating the method for the controller

We will use NestJS. A simple NestJS controller seems like the following example.

1
@Controller('pets')
2
export default class AppController {
3
  @Get()
4
  async search() {
5
    // TODO: Implement search
6
  }
7
}

As you see here. The URL for the search will be /pets/search

🚛 Using Repositories

This is the method Typescript friendly. I prefer this since I’ll have type validation if I update any field in the future.

1
// ./app.ts
2
import { Controller, Get, Query } from '@nestjs/common';
3
import { Pet } from './entities/pet';
4
import { FindManyOptions, FindOptionsWhere, Like, Repository } from 'typeorm';
5
import { InjectRepository } from '@nestjs/typeorm';
6

7
@Controller('pets')
8
export default class AppController {
9
  constructor(@InjectRepository(Pet) private petRepository: Repository<Pet>) {}
10

11
  @Get('/repository')
12
  async searchUsingRepository(@Query('search') search?: string) {
13
    // By default we load the relationship in the default options
14
    const options: FindManyOptions<Pet> = { relations: { owner: true } };
15

16
    if (search?.length) {
17
      // if search request query param was sent. Create an array of words
18
      const searchFormattedText = search.trim().split(' ');
19
      const where: FindOptionsWhere<Pet>[] = [];
20

21
      // Create OR WHERE queries by each word into first and last name
22
      for (const word of searchFormattedText) {
23
        where.push({ owner: { firstName: Like(`%${word}%`) } });
24
        where.push({ owner: { lastName: Like(`%${word}%`) } });
25
      }
26

27
      // Adding those where to options
28
      options.where = where;
29
    }
30

31
    const pets = await this.petRepository.find(options);
32

33
    return { pets };
34
  }
35
}

It will produce the following query

1
SELECT `Pet`.`id`                    AS `Pet_id`,
2
       `Pet`.`owner_id`              AS `Pet_owner_id`,
3
       `Pet__Pet_owner`.`id`         AS `Pet__Pet_owner_id`,
4
       `Pet__Pet_owner`.`first_name` AS `Pet__Pet_owner_first_name`,
5
       `Pet__Pet_owner`.`last_name`  AS `Pet__Pet_owner_last_name`
6
FROM `pet` `Pet`
7
         INNER JOIN `owner` `Pet__Pet_owner` ON `Pet__Pet_owner`.`id` = `Pet`.`owner_id`
8
WHERE ((`Pet__Pet_owner`.`first_name` LIKE ?) OR (`Pet__Pet_owner`.`last_name` LIKE ?))

🚛 Using Query Builder from repositories

This method is more close to SQL. Useful if you’re more comfortable with it.

1
// ./app.ts
2
import { Controller, Get, Query } from '@nestjs/common';
3
import { Pet } from './entities/pet';
4
import { Brackets, SelectQueryBuilder, Like, Repository } from 'typeorm';
5
import { InjectRepository } from '@nestjs/typeorm';
6

7
@Controller('pets')
8
export default class AppController {
9
  constructor(@InjectRepository(Pet) private petRepository: Repository<Pet>) {}
10

11
  @Get('/querybuilder')
12
  async searchUsingQueryBuilder(@Query('search') search?: string) {
13
    // This is how we load relationships here
14
    let query = this.petRepository.createQueryBuilder('p').innerJoinAndSelect('p.owner', 'o');
15

16
    if (search?.length) {
17
      // By default we load relationship in the default options
18
      const searchFormattedText = search.trim().split(' ');
19

20
      // With query builder we specify the AND or OR queries
21
      // Brackets is a way to group queries within a ( )
22
      query = query.andWhere(
23
        new Brackets((queryPart: SelectQueryBuilder<Pet>) => {
24
          for (const word of searchFormattedText) {
25
            queryPart.orWhere('o.first_name like :firstName', {
26
              firstName: `%${word}%`,
27
            });
28
            queryPart.orWhere('o.last_name like :lastName', {
29
              lastName: `%${word}%`,
30
            });
31
          }
32
        }),
33
      );
34
    }
35

36
    const pets = await query.getMany();
37

38
    return { pets };
39
  }
40
}

The query for the function above will result as

1
SELECT `p`.`id`         AS `p_id`,
2
       `p`.`owner_id`   AS `p_owner_id`,
3
       `o`.`id`         AS `o_id`,
4
       `o`.`first_name` AS `o_first_name`,
5
       `o`.`last_name`  AS `o_last_name`
6
FROM `pet` `p`
7
         INNER JOIN `owner` `o` ON `o`.`id` = `p`.`owner_id`
8
WHERE (`o`.`first_name` like ? OR `o`.`last_name` like ?)

⭐ Final result

This is the final controller. I’ll add a docker-compose file if you want to try it by yourself. Also, there is a /fake method to create records if you want to test the search.

Remember, this is ONLY the main controller. Check my FULL demo on Github here

1
// ./app.ts
2
import { Controller, Get, Post, Query } from '@nestjs/common';
3
import { Pet } from './entities/pet';
4
import {
5
  Brackets,
6
  FindManyOptions,
7
  FindOptionsWhere,
8
  InsertResult,
9
  Like,
10
  Repository,
11
  SelectQueryBuilder,
12
} from 'typeorm';
13
import { InjectRepository } from '@nestjs/typeorm';
14
import { Owner } from './entities/owner';
15

16
@Controller('pets')
17
export class AppController {
18
  constructor(
19
    @InjectRepository(Pet) private petRepository: Repository<Pet>,
20
    @InjectRepository(Owner) private ownerRepository: Repository<Owner>,
21
  ) {}
22

23
  @Get('/repository')
24
  async searchUsingRepository(@Query('search') search?: string) {
25
    const options: FindManyOptions<Pet> = { relations: { owner: true } };
26

27
    if (search?.length) {
28
      const searchFormattedText = search.trim().split(' ');
29
      const where: FindOptionsWhere<Pet>[] = [];
30

31
      for (const word of searchFormattedText) {
32
        where.push({ owner: { firstName: Like(`%${word}%`) } });
33
        where.push({ owner: { lastName: Like(`%${word}%`) } });
34
      }
35

36
      options.where = where;
37
    }
38

39
    const pets = await this.petRepository.find(options);
40

41
    return { pets };
42
  }
43

44
  @Get('/querybuilder')
45
  async searchUsingQueryBuilder(@Query('search') search?: string) {
46
    let query = this.petRepository.createQueryBuilder('p').innerJoinAndSelect('p.owner', 'o');
47

48
    if (search?.length) {
49
      const searchFormattedText = search.trim().split(' ');
50

51
      query = query.andWhere(
52
        new Brackets((queryPart: SelectQueryBuilder<Pet>) => {
53
          for (const word of searchFormattedText) {
54
            queryPart.orWhere('o.first_name like :firstName', {
55
              firstName: `%${word}%`,
56
            });
57
            queryPart.orWhere('o.last_name like :lastName', {
58
              lastName: `%${word}%`,
59
            });
60
          }
61
        }),
62
      );
63
    }
64

65
    const pets = await query.getMany();
66

67
    return { pets };
68
  }
69

70
  @Post('/fakes')
71
  async fakes() {
72
    const ownerPromises: Promise<InsertResult>[] = [];
73
    let ownerCount = await this.ownerRepository.count();
74

75
    for (let i = ownerCount + 1; i <= ownerCount + 100; i++) {
76
      const owner = new Owner();
77
      owner.firstName = `first_${i} name_${i}`;
78
      owner.lastName = `last_${i} name_${i}`;
79
      ownerPromises.push(this.ownerRepository.insert(owner));
80
    }
81

82
    await Promise.all(ownerPromises);
83

84
    ownerCount = await this.ownerRepository.count();
85

86
    const petPromises: Promise<InsertResult>[] = [];
87
    let petCount = await this.petRepository.count();
88

89
    for (let j = petCount + 1; j <= petCount + 100; j++) {
90
      const pet = new Pet();
91
      pet.ownerId = this.#randomInteger(1, ownerCount);
92
      petPromises.push(this.petRepository.insert(pet));
93
    }
94

95
    await Promise.all(petPromises);
96

97
    petCount = await this.petRepository.count();
98

99
    return { ownerCount, petCount };
100
  }
101

102
  #randomInteger(min: number, max: number) {
103
    min = Math.ceil(min);
104
    max = Math.floor(max);
105
    return Math.floor(Math.random() * (max - min) + min);
106
  }
107
}

Corrections? Suggestions? Click here to edit this page on Github

My posts are not AI generated, they might be only AI corrected. The first draft is always my creation

In other languages

TypeORM: Búsqueda relacional (NestJS incluido)

TypeORM: effectuer une recherche sur les relations d’un modèle (NestJS inclus)

Author

Written by Helmer Davila