How to implement pagination in NestJS with TypeORM

Question

Is there any way to get the total count and records with a single query, instead of running it as 2 separate queries?

If it's not possible, is there any way to reuse the where condition in both queries?

async findAll(query): Promise<Paginate> {   const take = query.take || 10   const skip = query.skip || 0   const keyword = query.keyword || ''    const builder = this.userRepository.createQueryBuilder("user")   const total = await builder.where("user.name like :name", { name: '%' + keyword + '%' }).getCount()   const data = await builder.where("user.name like :name", { name: '%' + keyword + '%' }).orderBy('name', 'DESC').skip(skip).take(take).getMany();    return {     data: data,     count: total   } }  {   count: 10,   data: [     {       id: 1,       name: 'David'     },     {       id: 2,       name: 'Alex'     }] } 

Answer 1

You can find some nice example in this project. In short typeorm has a really nice method specific to this usecase findAndCount.

async findAll(query): Promise<Paginate> {     const take = query.take || 10     const skip = query.skip || 0     const keyword = query.keyword || ''      const [result, total] = await this.userRepository.findAndCount(         {             where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },             take: take,             skip: skip         }     );      return {         data: result,         count: total     } } 

Repository API you can find here. More documentation about Repository class can be found here.

Answer 2

summing up...

This middleware checks if you have the take and skip parameters in the URL, if it does, it converts from string to number, if you don't use the default values. 10 for take and 0 for skip.

take is the number of results per page and skip, from where it should start reading records.

With that, I set up to intercept the "product / paged" route just for the GET method.

With this I can retrieve these values in the controller and pass to TypeORM or an SQL query.

@Injectable() export class PagerMiddleware implements NestMiddleware {   use(req: any, res: any, next: () => void) {     req.query.take = +req.query.take || 10;     req.query.skip = +req.query.skip || 0;     next();   } } 

and apply in module.

export class AdminFeatureApi implements NestModule {   configure(consumer: MiddlewareConsumer) {     consumer.apply(PagerMiddleware)     .forRoutes({ path: 'product/paged', method: RequestMethod.GET })   } } 

Controller

@Controller('product') export class TrainingDomainController {   constructor(private service: YourService) {}    @Get('paged')   get(@Query() { take, skip }) {     return this.service.findAll(take, skip);   } } 

and service

@Injectable() export class YourService {   constructor(     @InjectRepository(YourEntity)     private readonly repo: MongoRepository<YourEntity>   ) {}    async findAll(take: number = 10, skip: number = 0) {     const [data, total] = await this.repo.findAndCount({ take, skip });     return { data, total };   } } 

ok?