search

piccolo-orm / piccolo

A fast, user friendly ORM and query builder which supports asyncio.
https://piccolo-orm.com/
MIT License
1.44k stars 91 forks source link

[Need suggestion] Is there a paginator support in piccolo like DRF cursor paginator? #47

Open dashsatish opened 3 years ago

dashsatish commented 3 years ago

Hi, I couldn't find any links in the docs that support pagination in piccolo. Is there any support for it which I can integrate with my FastAPI server endpoint? (Mostly looking on the lines of Django (DRF) like pagination support!) Even thought of writing my own paginator but before that wanted to check with you @dantownsend if there's any other suggestion on this.

dantownsend commented 3 years ago

@dashsatish Piccolo currently supports offset and limit query clauses.

This isn't an ideal way of doing pagination, because using offset for pagination is bad performance wise.

As you mention, cursor based pagination would be far better, and it's something I'd definitely like in Piccolo, but haven't thought through the API yet.

If you're happy to use offset and limit, and are just exposing a single table as an endpoint, you may be interested in FastAPIWrapper. You can pass it __page_size and __page parameters:

http://localhost/api/my-table?__page_size=10&__page=2
dashsatish commented 3 years ago

Hi, An update here: We did implement a keyset based pagination to avoid performance issues pertaining to table scans in limit-offset based pagination. @dantownsend I used your idea from https://github.com/piccolo-orm/piccolo_api/pull/34 that has the shopify example. (thanks 🙂 )

Digging more into cursor based pagination approach. I shall update here of any progress we make.

dantownsend commented 3 years ago

@dashsatish That's awesome - thanks for the update.