Pagination broken for composite keys in version 9

[stmfcgcg]

Pagination does not work for composite keys in version 9.x (works in version 6.x)

Below are version 9 and version 6 generated code (issuer_id, parent_issuer_id, start_date and source_id are the composite keys). As you can see the criteria to add the nth page is different between the 2.

The difference in the 'where' criteria results in the application only iterating over a subset of the data in v9.x

version 9.x

SELECT "ih"."issuer_id"        AS "ih_issuer_id",
       "ih"."parent_issuer_id" AS "ih_parent_issuer_id",
       "ih"."level"            AS "ih_level",
       "ih"."start_date"       AS "ih_start_date",
       "ih"."end_date"         AS "ih_end_date",
       "ih"."source_id"        AS "ih_source_id"
FROM "ethos_app"."issuer_hierarchy_view" "ih"
WHERE 1 = 1
  AND "ih"."start_date" <= $1
  AND $2 <= "ih"."end_date"
  **AND (("ih"."issuer_id" < $3 OR "ih"."issuer_id" = $4) AND
       ("ih"."parent_issuer_id" < $5 OR "ih"."parent_issuer_id" = $6) AND
       ("ih"."start_date" < $7 OR "ih"."start_date" = $8) AND ("ih"."source_id" < $9 OR "ih"."source_id" = $10))**
ORDER BY "ih"."issuer_id" DESC, "ih"."parent_issuer_id" DESC, "ih"."start_date" DESC, "ih"."source_id" DESC
LIMIT 1001 -- PARAMETERS: ["2022-09-30","2022-09-30",22080794,22080794,22080794,22080794,"2020-05-29","2020-05-29","866561ab-0973-4544-9350-8a3a413f9fee","866561ab-0973-4544-9350-8a3a413f9fee"]

version 6.x

SELECT "ih"."issuer_id"        AS "ih_issuer_id",
       "ih"."parent_issuer_id" AS "ih_parent_issuer_id",
       "ih"."level"            AS "ih_level",
       "ih"."start_date"       AS "ih_start_date",
       "ih"."end_date"         AS "ih_end_date",
       "ih"."source_id"        AS "ih_source_id"
FROM "ethos_app"."issuer_hierarchy_view" "ih"
WHERE 1 = 1
  AND "ih"."start_date" <= $1
  AND $2 <= "ih"."end_date"
  **AND ("ih"."issuer_id" < $3 OR "ih"."issuer_id" = $4 AND "ih"."parent_issuer_id" < $5 OR
       "ih"."issuer_id" = $6 AND "ih"."parent_issuer_id" = $7 AND "ih"."start_date" < $8 OR
       "ih"."issuer_id" = $9 AND "ih"."parent_issuer_id" = $10 AND "ih"."start_date" = $11 AND "ih"."source_id" < $12)**
ORDER BY "ih"."issuer_id" DESC, "ih"."parent_issuer_id" DESC, "ih"."start_date" DESC, "ih"."source_id" DESC
LIMIT 1001 -- PARAMETERS: ["2022-09-30","2022-09-30",212311729,212311729,212311729,212311729,212311729,"2022-08-31",212311729,212311729,"2022-08-31","b55f9205-59c9-4cd8-8bfa-b9245b5858fe"]

[benjamin658]

Please upgrade to v0.10.0 to see if it works.

[stmfcgcg]

Version 0.10.0 has the same problem - in fact I think the generated query is the same as v9 From: Ben Who ***@***.***> Reply-To: benjamin658/typeorm-cursor-pagination ***@***.***> Date: Sunday, October 30, 2022 at 7:10 PM To: benjamin658/typeorm-cursor-pagination ***@***.***> Cc: Stuart Faircloth ***@***.***>, Author ***@***.***> Subject: Re: [benjamin658/typeorm-cursor-pagination] Pagination broken for composite keys in version 9 (Issue #65) Please upgrade to v0.10.0 to see if it works. — Reply to this email directly, view it on GitHub<#65 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AOAIFGCM6ZCVVZLDUBB5O6DWF4TBBANCNFSM6AAAAAARSDU4LE>. You are receiving this because you authored the thread.Message ID: ***@***.***> Your privacy and security are important to us. See our privacy policy (Canada https://www.capitalgroup.com/individual/ca/en/about/legal.html, Europe & Asia https://www.capitalgroup.com/content/sites/the-capital-group/entry-page/shared/privacy.html, United States https://www.capitalgroup.com/individual/privacy.html).

[stuartf123]

Here is a sample dataset and generated queries that shows the problem:

Col1 Col2
10 20
9 21
8 22
7 23
6 24

1st query – paging 2 rows at a time

Select Col1, Col2
Order by Col1 desc, Col2 desc
Limit 3

Results:
Col1 Col2
10 20
9 21
8 22 <- row used in the 2nd query

2nd query
Select Col1, Col2
Where (Col1 < 8 or Col1 = 8) and (Col2 < 22 or Col2 = 22)
Order by Col1 desc, Col2 desc
Limit 3

Results:
Col1 Col2
8 22

As you can see in the above results – there are rows missing

[stuartf123]

Your original algorithm would have generated

select col1, col2
where col1 < 8 or col1 = 8 and col2 < 22
limit 3

The above also has a flaw.
I think your algorithm should be:

select col1, col2
where col1 < 8 or (col1 = 8 and col2 < 22) or (col1 = 8 and col2 = 22)
oder by col1 desc, col2 desc

or in general for 3 columns:

select col1, col2, col3
where col1 < x or (col1 = x and col2 < y) or (col1 = x and col2 = y and col3 < z) or (col1 = x and col2 = y and col3 = z)

[stuartf123]

Here is an implementation of Paginator.buildCursorQuery that pages correctly for composite keys:

const operator = this.getOperator();

 

  // adds "(col1 < x) or (col1 = x and col2 < y)" to the criteria

  for (let outerIndex = 0; outerIndex < this.paginationKeys.length; ++outerIndex) {

    where.orWhere(new Brackets(innerQb => {

      for (let innerIndex = 0; innerIndex <= outerIndex; ++innerIndex) {

        const innerKey = this.paginationKeys[innerIndex]

 

        innerQb.andWhere(`${this.alias}.${innerKey} ${innerIndex === outerIndex ? operator : '='} :${innerKey}`, {

          [`${innerKey}`]: cursors[innerKey]

        })

      }

    }))

  }

 

  // adds "or (col1 = x and col2 = y)" to the criteria

  where.orWhere(new Brackets(finalQb=>{

    this.paginationKeys.forEach((finalKey: Extract<keyof T, string>) => {

      finalQb.andWhere(`${this.alias}.${finalKey} = :${finalKey}`, {

        [`${finalKey}`] : cursors[finalKey]

      })

    })

  }))

[benjamin658]

@ stuartf123 Could you send a PR with your implementation?

I really appreciate it.