Trouble with pagination of `QueryBuilder` results with `join`

[Anisi]

Hello, This is my code:

const referrals = this.referralRepository
      .createQueryBuilder('referrals')
      .leftJoinAndSelect('referrals.stages', 'stages')
      .leftJoinAndSelect('referrals.patient', 'patient')
      .leftJoinAndSelect('referrals.receiver', 'receiver')
      .leftJoinAndSelect('referrals.sender', 'sender')
      .orderBy('stages.created_at', 'DESC')
      .orWhere('receiver.id = :id AND stages.scheduleTimes IS NOT NULL', {
        id: person.id,
      })
      .orWhere('sender.id = :id', { id: person.id });

The problem is when I return the results directly everything is ok and it returns 11 records including multiple related stages for each record:

return referrals.getMany();

Bu when I use pagination, it returns records each one with only one stage which includes all joined records including 27 records:

return paginate<Referral>(referrals, options);

What is wrong with the paginate?

[gaiyaobed]

I am currently facing similar problem

any fix?? @Anisi

[Anisi]

I am currently facing similar problem

any fix?? @Anisi

Not yet. I added a date field to my primary entity and sorted records based on that to fix the problem.

[julianklumpers]

I'm also encountering this issue.

this.userRepository.createQueryBuilder('u')
  .leftJoinAndSelect('u.enterprises', 'enterprises')
  .where(
    new Brackets((qb) =>
      Object.entries(query).map(([key, val]) =>
        qb.orWhere(`u.externalUser ->>'${key}' ILIKE :val`, { val: `%${val}%` }),
      ),
    ),
  )
  .getMany();

My user repository has a total of 4 users in the table, and the enterprise relation has a total of 12 relations. The returned pagination is wrong because it counts the total enterprise relations as totalItems

pagination output:

  meta: {
    totalItems: 12,
    itemCount: 4,
    itemsPerPage: 10,
    totalPages: 2,
    currentPage: 1
  }

EDIT:

I'm using this patch now, which solves it for me, use it at your own risk.

diff --git a/node_modules/nestjs-typeorm-paginate/dist/paginate.js b/node_modules/nestjs-typeorm-paginate/dist/paginate.js
index 3d28819..943ced5 100644
--- a/node_modules/nestjs-typeorm-paginate/dist/paginate.js
+++ b/node_modules/nestjs-typeorm-paginate/dist/paginate.js
@@ -134,15 +134,13 @@ function paginateQueryBuilder(queryBuilder, options) {
     return __awaiter(this, void 0, void 0, function* () {
         const [page, limit, route, paginationType, countQueries, cacheOption] = resolveOptions(options);
         const promises = [
-            (interfaces_1.PaginationTypeEnum.LIMIT_AND_OFFSET === paginationType
-                ? queryBuilder.limit(limit).offset((page - 1) * limit)
-                : queryBuilder.take(limit).skip((page - 1) * limit))
-                .cache(cacheOption)
-                .getMany(),
+            queryBuilder.take(limit).skip((page - 1) * limit)
+            .cache(cacheOption)
+            .getMany(),
             undefined,
         ];
         if (countQueries) {
-            promises[1] = countQuery(queryBuilder, cacheOption);
+            promises[1] = queryBuilder.getCount();
         }
         const [items, total] = yield Promise.all(promises);
         return (0, create_pagination_1.createPaginationObject)({

[SebaBoler]

If you use any conditions like sort by related table you will have issues with return. Its workaround and like in graphql I have used @ResolveField for return any related table and there I have setup with order

[psam44]

@Anisi or @bashleigh , please consider closing this issue as a duplicate of 627.

[Anisi]

After reviewing the dialogue and contributions on this issue, it's clear that our community has been actively engaged in seeking a resolution to the pagination problem when using QueryBuilder with join operations. The shared experiences and workarounds have been invaluable. However, this issue may overlap with the discussions and potential solutions outlined in issue #627. To improve our efforts and avoid duplication, we should redirect our attention to issue #627 for further updates and collaborative problem-solving. Thus, I am closing this issue. Thank you all for your contributions!