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.