Before & after cursor not behaving correctly

[acSpock]

I'm puzzled. How is this behavior supposed to work?

current version: 0.10.1

Here's my API response fetching a table with only 18 items in the table:

api/resource?limit=10

{
   data: [10 records...]
   beforeCursor: null,
   afterCursor: 'Y3JlYXRlZEF0OjE2Nzc5NjQ1NDg3NzksaWQ6ZDQ5YzA3NjgtNTBlYy00M2I3LWExNzctYjAxNmQ2YjE3YjRm'
}

so far so good. let's move forward:

api/resource?limit=10&afterCursor=Y3JlYXRlZEF0OjE2Nzc5NjQ1NDg3NzksaWQ6ZDQ5YzA3NjgtNTBlYy00M2I3LWExNzctYjAxNmQ2YjE3YjRm

{
   data: [8 records...]
   beforeCursor: 'Y3JlYXRlZEF0OjE2Nzc4MDgzODM1NjgsaWQ6NmYxNGRmZDEtOGQ1Zi00OTZlLTkzZmUtOTc3MDkzMDY5YzAw',
   afterCursor: null
}

still... so far so good. Let's go back

api/resource?limit=10&beforeCursor=Y3JlYXRlZEF0OjE2Nzc4MDgzODM1NjgsaWQ6NmYxNGRmZDEtOGQ1Zi00OTZlLTkzZmUtOTc3MDkzMDY5YzAw

{
   data: [10 records...]
    "afterCursor": "Y3JlYXRlZEF0OjE2Nzc4MDgzODM1NjgsaWQ6NmYxNGRmZDEtOGQ1Zi00OTZlLTkzZmUtOTc3MDkzMDY5YzAw",
    "beforeCursor": "Y3JlYXRlZEF0OjE2Nzg1NzY0MTM3NzcsaWQ6ZTdiZDhkMGEtODkyMi00MDM4LTlhZTEtMWMwYzMxYjhkNGJl",
}

mmmm that's a little strange. Data is right, but I'm seeing a beforeCursor again. There shouldn't be, because aren't we back at the beginning of the list?

Let's try going forward 1 more time.

api/resource?limit=10&afterCursor=Y3JlYXRlZEF0OjE2Nzc4MDgzODM1NjgsaWQ6NmYxNGRmZDEtOGQ1Zi00OTZlLTkzZmUtOTc3MDkzMDY5YzAw

{
   data: [7 records]
    "afterCursor": "",
    "beforeCursor": "Y3JlYXRlZEF0OjE2Nzc4MDY5NTY0NTMsaWQ6NjI2NWY2YzItOGQwYS00MGU4LWIwYmItYjAxNzIwODQwMWUy",
}

That's not right. 7 records.

And if I keep going back and forth, I'll go back to 10 records then if I go forward again, 6 records, then back to 10 records then forward to 5 until it goes down to 0.

Why?

Here's my implementation:

  async queryData(userId: string, beforeCursor: string | undefined, afterCursor: string | undefined, limit: number, order = 'DESC'): Promise<{ data: Response[], afterCursor: string | null, beforeCursor: string | null, totalCount: number }> {
    const totalCount = await this.entityManager.createQueryBuilder(TestEntity, 'test')
      .where('test.userId = :userId', { userId })
      .getCount();

    const queryBuilder = this.entityManager.getRepository(TestEntity).createQueryBuilder('test')
      .where('test.userId = :userId', { userId })
      .take(limit);

    const paginator = buildPaginator({
      entity: TestEntity,
      alias: 'test',
      paginationKeys: ['createdAt', 'id'],
      query: {
        order: 'DESC',
      },
    });

    if (afterCursor) {
      paginator.setAfterCursor(afterCursor);
    } else if (beforeCursor) {
      paginator.setBeforeCursor(beforeCursor);
    }

    paginator.setLimit(limit);

    const { data: testResults, cursor: cursorResult } = await paginator.paginate(queryBuilder);

    return {
      testResults,
      afterCursor: cursorResult.afterCursor,
      beforeCursor: cursorResult.beforeCursor,
      totalCount,
    };
  }

I'm experimenting with cursor pagination and fastify and i'm wondering why this behavior is happening. Any help would be appreciative.

[Anton-Burdin]

Hi, you you need to remove id from paginationKeys,
because id not sortable field or you can use ulid instead of uuid

[benjamin658]

Can you try to set the limit on buildPaginator instead of the query builder?

    const paginator = buildPaginator({
      entity: TestEntity,
      alias: 'test',
      paginationKeys: ['createdAt', 'id'],
      query: {
        order: 'DESC',
        limit <-- here
      },
    });

[siamahnaf]

I am seeing exact same issue.

Here is my code-

        const sections = await this.sectionRepository
            .createQueryBuilder("section")
            .leftJoinAndSelect("section.createdBy", "createdBy")
            .where("LOWER(section.name) LIKE :search", { search: `%${searchInput.search.toLowerCase()}%` })

        const paginator = buildPaginator({
            entity: Section,
            paginationKeys: ["created_at"],
            query: {
                limit: searchInput.limit,
                order: searchInput.orderBy ?? "DESC",
                beforeCursor: searchInput.beforeCursor || null
                afterCursor: searchInput.afterCursor || null
            }
        });

        const { data, cursor } = await paginator.paginate(sections);

@acSpock are you find any solutions for this?

[siamahnaf]

Hello anyone

[siamahnaf]

@benjamin658 , I try it with the limit, I am facing the same issue. Please review the code!

[benjamin658]

@benjamin658 , I try it with the limit, I am facing the same issue. Please review the code!

Please make sure that the pagination key "created_at" is table wide unique.

[kylenguyen-relay]

@benjamin658 Does pagination key need to be unique ?

[clothe09986]

@benjamin658 @kylenguyen-relay
I encountered the same issue and found that it was due to the difference in precision between JavaScript and the database(Postgres) for timestamps. After conversion, the precision discrepancy made equality impossible, which necessitated uniqueness. However, I believe it should be able to detect duplicate values since multiple pagination keys have already been added. I tried modifying the code to adjust the precision, and it works.

async buildCursorQuery(where, cursors) {
    const operator = this.getOperator();
    const params = {};
    let query = '';
    for (const key of this.paginationKeys) {
        params[key] = cursors[key];
        const isTimestamp = await this.isTimestampColumn(this.alias, key);
        if (isTimestamp) {
            where.orWhere(`${query}date_trunc('milliseconds', ${this.alias}.${key}) ${operator} :${key}`, params);
            query = `${query}date_trunc('milliseconds', ${this.alias}.${key}) = :${key} AND `;
        } else {
            where.orWhere(`${query}${this.alias}.${key} ${operator} :${key}`, params);
            query = `${query}${this.alias}.${key} = :${key} AND `;
        }
    }
}