63

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'
    }]
}
Audwin Oyong
  • 2,247
  • 3
  • 15
  • 32
HanJeaHwan
  • 956
  • 1
  • 8
  • 13

6 Answers6

141

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.

HanJeaHwan
  • 956
  • 1
  • 8
  • 13
Ivan Vasiljevic
  • 5,478
  • 2
  • 30
  • 35
  • 1
    Great example but please add a note that the Like('%' + keyword + '%') will work only on SQL database and not on mongodb. – Kashkashio Jul 22 '19 at 14:15
  • I can't find findAndCount on my model... only find – Artem Sep 23 '20 at 22:20
  • If you want to use the ClassSerializerInterceptor, I made some changes to it so it accepts a return value of type `[Array, number]`. Kind of useful in this context. So you can simply return `getManyAndCount()` and the data will be serialized anyway. Check it out: https://gist.github.com/ericjeker/08f719aae3b730c820b62136efec9708 – Eric Jeker Jan 13 '21 at 06:53
  • I am confused. In the docs it says now `findAndCount - Finds entities that match given find options. Also counts all entities that match given conditions, but ignores pagination settings (skip and take options).` Did they change this? – boy Mar 31 '21 at 10:20
  • OK i figured it out, only the count ignores pagination settings to give total count. Also to everyone trying to use this, in the linked example you definitely want to set skip to be `options.page * options.limit`. Its commented in the linked code and won't give proper result otherwise – boy Mar 31 '21 at 11:21
17

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.

Folders

@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?

Gui Seek
  • 421
  • 4
  • 5
  • 5
    please describe your answer – Mr. Pyramid Nov 01 '19 at 13:05
  • 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. – Gui Seek Apr 29 '20 at 13:56
  • 5
    You probably don't need middleware and just perform standard DTOs it's much cleaner. – Bryan Bojorque Oct 24 '20 at 03:00
  • This is a good solution if you want to standardize it throughout the entire app using decorators. Well done – DOZBORNE Feb 10 '23 at 20:44
17

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,
      }
    }
    
kadiro
  • 956
  • 1
  • 10
  • 16
  • This may be too late but we should `parseInt(page)` in `paginateResponse` before dealing with `nextPage` and `prevPage` if we are not parsing it anywhere else. – Sathya Jul 02 '23 at 19:18
4

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

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

2

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
});
Cassio Seffrin
  • 7,293
  • 1
  • 54
  • 54
-3

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> {

}
mega6382
  • 9,211
  • 17
  • 48
  • 69
sloan-dog
  • 85
  • 5