search

gcpug / dogrun

Apache License 2.0
0 stars 0 forks source link

Are you planning to implement Cursor? #21

Closed sinmetal closed 5 years ago

sinmetal commented 5 years ago

WHAT

I want a Cursor when executing a query

WHY

Cursor makes implementation easier for Pagenation.

sinmetal commented 5 years ago

How to implement the current Pagination

Set the value of the last row of the query to WHERE and throw the query

First Query

SELECT * FROM Order1M@{FORCE_INDEX=Order1MCreatedAtDesc} ORDER BY CreatedAt DESC, OrderId LIMIT 10

Last Row 
OrderId = “b28e0a2d-f131-497b-8eb3-71d5cdeddcf0”
CreatedAt = “2018-08-26T16:15:09.436784119Z”

Next Page Query

SELECT * 
FROM Order1M@{FORCE_INDEX=Order1MCreatedAtDesc} 
WHERE 
      (CreatedAt < "2018-08-26T16:15:09.436784119Z") 
OR (CreatedAt = "2018-08-26T16:15:09.436784119Z" AND OrderId > "b28e0a2d-f131-497b-8eb3-71d5cdeddcf0")
ORDER BY CreatedAt DESC, OrderId LIMIT 10
sinmetal commented 5 years ago

SpannerでCursorを実装するのは難しい。 TimestampベースでPaginationするなら、上記のやり方がベター