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?

Answer 3

i prefer using page instead of skip directly

• endpoint example : /users?page=4&take=3

  async findAll(query): Promise<Paginate> {
      const take = query.take || 10
      const page=query.page || 1;
      const skip= (page-1) * take ;
      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
      }
  }
  

  2/. better way (handle the response) :

   async findAll(query): Promise<Paginate> {
       const take= query.take || 10
       const page=query.page || 1;
       const skip= (page-1) * take ;
       const keyword = query.keyword || ''
  
       const data = await this.userRepository.findAndCount(
           {
               where: { name: Like('%' + keyword + '%') }, order: { name: "DESC" },
               take: take,
               skip: skip
           }
       );
       return paginateResponse(data ,page,take)
  
   }
  

  export function paginateResponse(data,page,limit) {
    const [result, total]=data;
    const lastPage=Math.ceil(total/limit);
    const nextPage=page+1 >lastPage ? null :page+1;
    const prevPage=page-1 < 1 ? null :page-1;
    return {
      statusCode: 'success',
      data: [...result],
      count: total,
      currentPage: page,
      nextPage: nextPage,
      prevPage: prevPage,
      lastPage: lastPage,
    }
  }
  

Answer 4

You can also take a look at this package for NestJS and TypeORM:

https://github.com/nestjsx/nestjs-typeorm-paginate

Answer 5

There are 2 options for that: 1st createQueryBuilder and 2nd with findAndCount

const userRepository = dataSource.getRepository(User);
const _take = query.take || 10;
const _skip = query.skip || 0;

With createQueryBuilder

const qb = await dataSource
    .getRepository(User)
    .createQueryBuilder("user")
    .orderBy("user.id", "DESC")
    .take(_take)
    .skip(_skip);

    const users = await qb.getMany();
    const total = await qb.getCount();

With findAndCount. With this approach you could get total of results in just one call.

const [users, total]  = await userRepository.findAndCount({
        order: {
            id: 'DESC'
        }
        skip: _skip,
        take: _take
});

Answer 6

If you need to paginate through MANY records, i.e several iterations, (perhaps during a migration or mass update).

async getPaginatedResults(query: any, transactionManager?: EntityManager): Promise<any> {

}