search

Uysim / pagy-cursor

Cursor pagination with pagy for Ruby On Rails
MIT License
126 stars 23 forks source link

Fix oredering with after and before option #57

Closed h-michael closed 1 year ago

h-michael commented 1 year ago

Solve: https://github.com/Uysim/pagy-cursor/issues/38

This will fix the following case

Compare query

before this change ```sql SELECT "posts".* FROM "posts" WHERE "posts"."title" = "post91 was updated" LIMIT 1; SELECT 1 AS one FROM "posts" WHERE ( "posts"."created_at" < (SELECT "posts"."created_at" FROM "posts" WHERE "posts"."id" = 'dd46c346-5368-4a09-a7a8-0d7d25ec2938') OR "posts"."created_at" = (SELECT "posts"."created_at" FROM "posts" WHERE "posts"."id" = 'dd46c346-5368-4a09-a7a8-0d7d25ec2938') AND "posts"."id" < 'dd46c346-5368-4a09-a7a8-0d7d25ec2938' ) LIMIT 1; SELECT "posts".* FROM "posts" WHERE ( "posts"."created_at" < (SELECT "posts"."created_at" FROM "posts" WHERE "posts"."id" = 'dd46c346-5368-4a09-a7a8-0d7d25ec2938') OR "posts"."created_at" = (SELECT "posts"."created_at" FROM "posts" WHERE "posts"."id" = 'dd46c346-5368-4a09-a7a8-0d7d25ec2938') AND "posts"."id" < 'dd46c346-5368-4a09-a7a8-0d7d25ec2938' ) ORDER BY "posts"."updated_at" DESC, "posts"."created_at" DESC, "posts"."id" DESC LIMIT 20; ```
this change ```sql SELECT "posts".* FROM "posts" WHERE "posts"."title" = "post91 was updated" LIMIT 1; SELECT 1 AS one FROM "posts" WHERE ( "posts"."updated_at" < (SELECT "posts"."updated_at" FROM "posts" WHERE "posts"."id" = '7c9ac4db-7017-4660-9c4a-0486b50032b2') OR "posts"."updated_at" = (SELECT "posts"."updated_at" FROM "posts" WHERE "posts"."id" = '7c9ac4db-7017-4660-9c4a-0486b50032b2') AND "posts"."id" < '7c9ac4db-7017-4660-9c4a-0486b50032b2' ) LIMIT 1; SELECT "posts".* FROM "posts" WHERE ( "posts"."updated_at" < (SELECT "posts"."updated_at" FROM "posts" WHERE "posts"."id" = '7c9ac4db-7017-4660-9c4a-0486b50032b2') OR "posts"."updated_at" = (SELECT "posts"."updated_at" FROM "posts" WHERE "posts"."id" = '7c9ac4db-7017-4660-9c4a-0486b50032b2') AND "posts"."id" < '7c9ac4db-7017-4660-9c4a-0486b50032b2' ) ORDER BY "posts"."updated_at" DESC, "posts"."created_at" DESC, "posts"."id" DESC LIMIT 20; ```
Uysim commented 1 year ago

Big thanks @h-michael. I'll find time to to release this to the latest version and credit you in our README