Nestjs /TypeORM - How to implement custom search by column

Implementing custom search by column in Nestjs with TypeORM can be achieved through several steps. Below are the specific implementation methods along with a simple example:

Step 1: Create a Request Handling Function

First, create a function in your service that accepts search parameters and returns the corresponding data. For example, if you want to search in a user table, you can implement a function like the following:

typescript
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private userRepository: Repository<User>,
  ) {}

  async findUsers(searchOptions: any): Promise<User[]> {
    const queryBuilder = this.userRepository.createQueryBuilder('user');

    if (searchOptions.name) {
      queryBuilder.andWhere('user.name LIKE :name', { name: `%${searchOptions.name}%` });
    }
    if (searchOptions.email) {
      queryBuilder.andWhere('user.email LIKE :email', { email: `%${searchOptions.email}%` });
    }
    // Add additional search conditions as needed

    return queryBuilder.getMany();
  }
}

Step 2: Controller Layer Handling

Next, create an endpoint in the controller to handle user search requests. For example:

typescript
import { Controller, Get, Query } from '@nestjs/common';
import { UserService } from './user.service';
import { User } from './user.entity';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}

  @Get()
  findAll(@Query() query): Promise<User[]> {
    return this.userService.findUsers(query);
  }
}

This findAll method processes GET requests from the client, accepting search criteria via query parameters.

Example Usage:

Clients can send an HTTP GET request to /users?name=John&email=john@example.com to search for users named 'John' with the email 'john@example.com'. The request is handled by the findAll controller method, which passes the parameters to the findUsers service method. The service method constructs and executes the database query using these parameters.

Summary:

By following these steps, you can implement custom search by column in the Nestjs and TypeORM environment. Using TypeORM's QueryBuilder, you can flexibly construct complex SQL queries based on varying query parameters, enabling highly customized search functionality. This approach is ideal for scenarios requiring multi-field searches, enhancing code scalability and maintainability.