Pagination in TypeORM/NestJS

Question

I have to introduce pagination in findAll() method. I really dont know how to do it. I tried but it is giving so many errors. I used findAndCount() method given by typeorm for that, But I am not sure how it will work.

As of now below method returning all the record. I need to return at a time 10 records. Please suggest what modification I need to do.

async findAll(queryCertificateDto: QueryCertificateDto,page=1):  Promise<PaginatedResult> {
    
    let { country, sponser } = queryCertificateDto;

    const query = this.certificateRepository.createQueryBuilder('certificate');

    if (sponser) {
        sponser = sponser.toUpperCase();
        query.andWhere('Upper(certificate.sponser)=:sponser', { sponser });
    }

    if (country) {
        country = country.toUpperCase();
        query.andWhere('Upper(certificate.country)=:country', { country });
    }      
    const  certificates = query.getMany();     
    return certificates;
}

this is PaginatedResult file.

 export class PaginatedResult {
        data: any[];
        meta: {
          total: number;
          page: number;
          last_page: number;
        };
      }
  

I tried changing code of findAll() but where clause is giving error. I am not sure how to handle query.getMany() in pagination.

 const take = query.take || 10
        const skip = query.skip || 0
       
      
        const [result, total] = await this.certificateRepository.findAndCount(
            {
                where: query.getMany(),   //this is giving error
                take:take,
                skip:skip
            }
        );
        return result;

I need to introduce pagination in this method. Any help will be really helpful.

Answer 1

You don't need the .getMany() with your where in the last code, the result is an array of the data you need.

From your first code, you can do this:

async findAll(queryCertificateDto: QueryCertificateDto,page=1): Promise<PaginatedResult> {
    // let's say limit and offset are passed here too
    let { country, sponser, limit, offset } = queryCertificateDto;

    const query = this.certificateRepository.createQueryBuilder('certificate');

    if (sponser) {
        sponser = sponser.toUpperCase();
        query.andWhere('certificate.sponser = :sponser', { sponser });
    }

    if (country) {
        country = country.toUpperCase();
        query.andWhere('certificate.country = :country', { country });
    }

    // limit and take mean the same thing, while skip and offset mean the same thing
    const certificates = await query
        .orderBy("certificate.id", "ASC")
        .limit(limit || 10)
        .offset(offset || 0)
        .getMany();

    // if you want to count just replace the `.getMany()` with `.getManyandCount()`;

    return certificates;
}```