Comments (8)
Related - piccolo-orm/piccolo#47
from piccolo_api.
Hi Daniel
I’ve never encountered cursor pagination before, but it looks interesting to me. I played around with it a bit, and it seems to work if I didn’t miss the point. I changed a bit of code in PiccoloCRUD endpoints.py
.
- adding cursor model to
serializers.py
# cursor model
class Cursor(pydantic.BaseModel):
next_cursor: str
- adding cursor to
Param
classcursor: str = ""
- adding cursor to
_split_params
method
if key == "__cursor":
try:
cursor = str(value)
except ValueError:
logger.info(f"Unrecognised __cursor argument - {value}")
else:
response.cursor = cursor
continue
- adding cursor pagination and base64 encoding and decoding for next_cursor value
async def _get_all(self, params: t.Optional[t.Dict[str, t.Any]] = None) -> Response:
# rest of unchanged code
# Pagination
cursor = split_params.cursor
page_size = split_params.page_size or self.page_size
page = split_params.page
# raise error if both __page and __cursor in query parametars
if "__cursor" in params and "__page" in params:
return JSONResponse(
{
"error": "Can't have both __page and __cursor as query parametars",
},
status_code=403,
)
# Cursor pagination
# query where limit is equal to page_size plus one
query = query.limit(page_size + 1)
rows = await query.run()
# initial cursor
next_cursor = self.encode_cursor(str(rows[-1]["id"]))
if cursor:
# query parametar cursor
next_cursor = self.decode_cursor(cursor)
# querying by query parametar order
if order_by and order_by.ascending:
query = query.where(self.table.id >= int(next_cursor)).limit(
page_size + 1
)
else:
query = query.where(self.table.id <= int(next_cursor)).limit(
page_size + 1
)
rows = await query.run()
# reset cursor to new value from latest value from rows
# if no more further results provide empty string as next_cursor
next_cursor = (
""
if len(rows) <= page_size
else self.encode_cursor(str(rows[-1]["id"]))
)
# LimitOffset pagination
# if page_size greater than max_page_size return error
if page_size > self.max_page_size:
return JSONResponse(
{
"error": "The page size limit has been exceeded",
},
status_code=403,
)
query = query.limit(page_size)
if page > 1:
offset = page_size * (page - 1)
query = query.offset(offset).limit(page_size)
rows = await query.limit(page_size).run()
# We need to serialise it ourselves, in case there are datetime
# fields.
cursor_model = Cursor(next_cursor=next_cursor).json()
json = self.pydantic_model_plural(include_readable=include_readable)(
rows=rows
).json()
return (
CustomJSONResponse(f"{json[:-1]}, {cursor_model[1:]}")
if "__cursor" in params
else CustomJSONResponse(json)
)
###########################################################################
def encode_cursor(self, cursor):
cursor_bytes = cursor.encode("ascii")
base64_bytes = base64.b64encode(cursor_bytes)
return base64_bytes.decode("ascii")
def decode_cursor(self, cursor):
base64_bytes = cursor.encode("ascii")
cursor_bytes = base64.b64decode(base64_bytes)
return cursor_bytes.decode("ascii")
Sorry for the long comment, but what do you think about this? If it's OK I can do PR, if it's not, just forget it :). Cheers.
from piccolo_api.
@sinisaos Great job!
I haven't used cursor based pagination before, but I do know it's the recommended way as limit + offset isn't very performant in Postgres, and there are weird edge cases where you can miss rows, or get duplicate rows.
If you make a pull request, I'll have a play around. I'm interested to try it out! Thanks.
from piccolo_api.
@dantownsend Thank you. I'll do it later tonight.
from piccolo_api.
Any updates on the implementation here.:)
from piccolo_api.
@satishdash All progress is at #34, but there has been no activity lately. If you have any code or ideas on how to improve it, feel free to contribute and make pull request.
from piccolo_api.
@satishdash Yeah, it has stalled a bit.
Cursor based pagination is really complex - @sinisaos did a great job of getting it to work, but I still struggle to wrap my head around it entirely! Here is the implementation:
The problem is it adds a lot of logic to PiccoloCRUD
. What I'd like to do is somehow move this logic into a separate paginator class, like Django REST Framework does:
That way we can add the new functionality, whilst keeping PiccoloCRUD
maintainable. But I haven't been able to make much progress on it recently.
from piccolo_api.
@dantownsend This is done in separate package. You can close this issue.
from piccolo_api.
Related Issues (20)
- Batch version of the get endpoint HOT 6
- Drop Python 3.7 support
- Catch foreign key constraint errors in `PiccoloCRUD`
- `schema_extra` param not passed to `pydantic_model_{output | optional | plural}` methods HOT 1
- Piccolo Admin API docs are not rendering
- Better error display on PATCH request
- Alpha version for Pydantic 2.0 / Piccolo 1.0a1? HOT 2
- PiccoloCRUD `post_single` return id of the inserted row instead of the row HOT 3
- Add `ne` operator
- Python 3.12 support
- RateLimitingMiddleware tests failed HOT 5
- Stop multi-dimensional arrays from breaking
- Issue with updating and bulk updating `BaseUser` via admin panel HOT 15
- Updating middleware syntax HOT 6
- Make `default-src` configurable in `CSPMiddleware`
- Hide parameter in Validators to hide Piccolo Admin table link from sidebar if the validators fail. HOT 8
- Add `excluded_paths` to `SessionsAuthBackend`
- Replace deprecated `abstractproperty`
- Fix for Pydantic breaking changes HOT 2
- Hooks Outside PiccoloCrud HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from piccolo_api.