nestjs-typeorm-paginate: Wrong totalCount when using joins in QB

In this commit https://github.com/nestjsx/nestjs-typeorm-paginate/commit/890ee164e15a1495782917b8443f0cd4cca0b7c0#diff-c58e144dadf00250381ed55e6ce83245cda76aca84131ad494fd4911932f656f. a custom total count query was introduced instead of TypeORMs qb.getCount();

this query being SELECT COUNT(*) FROM (…) returns number of all rows returned by query which would be like (m*n* k…) depending on how many rows were joined

the original implementation of TypeORMs getCount does SELECT COUNT(DISTINCT("table"."id")) AS "cnt" FROM (..) which returns correct number of entities even when using JOINs

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 9
Comments: 36 (4 by maintainers)

Most upvoted comments

@ayeen, thank you for your advice.

I know about this option and used it. But in my case the result was not completely correct. When I set paginationType: PaginationTypeEnum.TAKE_AND_SKIP, I’ve got the correct value (count) of items, but meta.totalItems was incorrect. For example, items is an array of 5 elements (which is true), but meta.totalItems is 10.

My solution with this problem, was return the function metaTransformer and modifying the return of totalItems and totalPages.

async listAll({
    page,
    limit,
  }: IListAllProps): Promise<Pagination<PeopleM>> {
    const peoples = this.peopleEntityRepository
      .createQueryBuilder('p')
      .leftJoinAndSelect('p.lead_step', 'lead')
      .leftJoinAndSelect('p.schedules', 's')
      .select(['p', 'lead', 's.id', 's.started_at', 's.finished_at']);

    // Here I get total items of table peoples
    const totalItems = await peoples.getCount();
    
    return await paginate<People>(peoples, {
      limit,
      page,
      paginationType: PaginationTypeEnum.TAKE_AND_SKIP,
      metaTransformer: ({ currentPage, itemCount, itemsPerPage }) => {

     // Calculating the total of pages
        const totalPages = Math.round(totalItems / itemsPerPage);
        return {
          currentPage,
          itemCount,
          itemsPerPage,

         // Returning in this two row
          totalItems,
          totalPages: totalPages === 0 ? 1 : totalPages,
        };
      },
    });
  }

I know it’s not right this way, but was that I found at moment.

WalleksMR on May 25, 2022

@ayeen, thank you for your advice.

I know about this option and used it. But in my case the result was not completely correct. When I set paginationType: PaginationTypeEnum.TAKE_AND_SKIP, I’ve got the correct value (count) of items, but meta.totalItems was incorrect. For example, items is an array of 5 elements (which is true), but meta.totalItems is 10.

NikolayYakovenko on Jan 24, 2022

Yea we need a more advanced query which I was trying to implement for others which doesn’t have the distinct func so adding that will solve your problem and enable more advanced queries for others!

bashleigh on Oct 20, 2021

@ayeen, thank you for your advice. I know about this option and used it. But in my case the result was not completely correct. When I set paginationType: PaginationTypeEnum.TAKE_AND_SKIP, I’ve got the correct value (count) of items, but meta.totalItems was incorrect. For example, items is an array of 5 elements (which is true), but meta.totalItems is 10.

My solution with this problem, was return the function metaTransformer and modifying the return of totalItems and totalPages.
async listAll({
    page,
    limit,
  }: IListAllProps): Promise<Pagination<PeopleM>> {
    const peoples = this.peopleEntityRepository
      .createQueryBuilder('p')
      .leftJoinAndSelect('p.lead_step', 'lead')
      .leftJoinAndSelect('p.schedules', 's')
      .select(['p', 'lead', 's.id', 's.started_at', 's.finished_at']);

    // Here I get total items of table peoples
    const totalItems = await peoples.getCount();
    
    return await paginate<People>(peoples, {
      limit,
      page,
      paginationType: PaginationTypeEnum.TAKE_AND_SKIP,
      metaTransformer: ({ currentPage, itemCount, itemsPerPage }) => {

     // Calculating the total of pages
        const totalPages = Math.round(totalItems / itemsPerPage);
        return {
          currentPage,
          itemCount,
          itemsPerPage,

         // Returning in this two row
          totalItems,
          totalPages: totalPages === 0 ? 1 : totalPages,
        };
      },
    });
  }
I know it’s not right this way, but was that I found at moment.

Math.round should be replaced by Math.ceil.

Anisi on Jul 26, 2022

@rudolfmedula @NikolayYakovenko use the below code snippet, I hope this will resolve your problem, this helped me with the same issue I had faced in my application options.paginationType = PaginationTypeEnum.TAKE_AND_SKIP

ayeen on Jan 24, 2022

@bashleigh I’ve added the failing test as promised. Hope it’s helpful.

https://github.com/nestjsx/nestjs-typeorm-paginate/compare/master...rudolfmedula:issues/627

rudolfmedula on Oct 20, 2021