typeorm-cursor-pagination

typeorm-cursor-pagination copied to clipboard

example:

const queryBuilder = this.dataSource .createQueryBuilder(Notification, 'notification') .select('notification.id', 'id') .addSelect('message.title', 'title') .leftJoin('notification.receiver', 'user') .leftJoin('notification.message', 'message') .where('user.did =:did', { did: userDID }); const paginator = buildPaginator({ entity: Notification, paginationKeys: ['id'], query: pagingQuery, }); const { data, cursor } = await paginator.paginateRaw(queryBuilder); return { data, cursor };

and,,, typeorm querybuilder.take method is not working if with join query https://github.com/typeorm/typeorm/issues/4742

Sorry, I don’t get it, could you please explain it more?

Sorry, I don’t get it, could you please explain it more?

ok, In typeorm, to getting raw result use getRawMany method. (https://typeorm.io/select-query-builder#getting-raw-results) In this package, Paginater.paginate method call getMany in typeorm to find entities. To not only support entities but also raw result, add Paginater.paginateRaw and call getRawMany.

But if it returns raw results, then the library cannot generate cursors.

yes, so how about add paginateRaw that return generate cursor like paginate in your package like this.

public async paginateRaw(builder: SelectQueryBuilder<Entity>): Promise<PagingRawResult> {
        const raws = await this.appendPagingQuery(builder).getRawMany();
        const hasMore = raws.length > this.limit;

        if (hasMore) {
            raws.splice(raws.length - 1, 1);
        }

        if (raws.length === 0) {
            return this.toPagingResult(raws);
        }

        if (!this.hasAfterCursor() && this.hasBeforeCursor()) {
            raws.reverse();
        }

        if (this.hasBeforeCursor() || hasMore) {
            this.nextAfterCursor = this.encode(raws[raws.length - 1]);
        }

        if (this.hasAfterCursor() || (hasMore && this.hasBeforeCursor())) {
            this.nextBeforeCursor = this.encode(raws[0]);
        }

        return this.toPagingRawResult(raws);
    } 
   
private toPagingRawResult(raws: any[]): PagingRawResult {
        return {
            data: raws,
            cursor: this.getCursor(),
        };
    }    

this paginateRaw method logic equal paginate in your package except below code.

        const raws = await this.appendPagingQuery(builder).getRawMany();

Would you mind sending a PR for this feature with proper test cases?

I've had this need as well so I had to implement myself, and I can tell it's not easy to do with typeorm, the mapping from raw results to entity results for cursor creation is tedious. About JOIN queries, that just requires adding code to handle these cases as the number of results in page will be inconsistent when used with limit and offset.

For solving the join query, I notice Typeorm provides this Joining and mapping functionality. So in your case where you want to load the message: .leftJoin('notification.message', 'message') can be written to .leftJoinAndMapOne('notification.message', 'notification.message', 'message')

In this case the Message entity will be loaded in the PagingResulst

NotificationEntity {
  id: '123',
  ...
  message: MessageEntity {
    id: '123'
    ...
  }
}