3 ms·

Simon, I have a wish list item for Datasette, pagination of ad-hock queries. I know thats a really difficult thing to implement as it would require parsing and

Simon, I have a wish list item for Datasette, pagination of ad-hock queries. I know thats a really difficult thing to implement as it would require parsing and altering the sql query, but with large datasets it would be so useful! (Love Datasette!)

simonw 4y ago
I want this too! Datasette avoids offset/limit pagination because it performs poorly on huge queries - and I don't want random visitors (and crawlers) hurting performance of a public instance by crawling through offset/limit of thousands of pages. That's why table pages implement keyset pagination instead - so you can do https://congress-legislators.datasettes.com/legislators/legislators?_next=A000106 https://congress-legislators.datasettes.com/legislators/legi... and get back records following the one with A000106, which is a fast query because the ID column has an index on it. Supporting this with arbitrary queries is harder. One idea I had is to allow the user to specify which column and sort order should be used for keyset pagination - so you could construct a URL like this: /?sql=select+*+from+legislators+order+by+id&_pagination_column=id If a pagination column has been specified, Datasette would use the same trick it uses on regular table pages and add next links that way. Would that work for you? The other, probably easier option is a setting that enables offset/limit pagination of arbitrary SQL queries - turned off by default, but easy to turn on for users who are running Datasette on a private server. If that takes several seconds people can at least opt into it.
- samwillis 4y ago
  Either would work, but as you said the latter is easer to implement and would have done the job for what I was working on. If I had had more time I would have looking into trying to do it myself, but there is never enough time...

3 ms·

Simon, I have a wish list item for Datasette, pagination of ad-hock queries. I know thats a really difficult thing to implement as it would require parsing and

by samwillis 4y ago

simonw 4y ago
I want this too! Datasette avoids offset/limit pagination because it performs poorly on huge queries - and I don't want random visitors (and crawlers) hurting performance of a public instance by crawling through offset/limit of thousands of pages. That's why table pages implement keyset pagination instead - so you can do https://congress-legislators.datasettes.com/legislators/legislators?_next=A000106 https://congress-legislators.datasettes.com/legislators/legi... and get back records following the one with A000106, which is a fast query because the ID column has an index on it. Supporting this with arbitrary queries is harder. One idea I had is to allow the user to specify which column and sort order should be used for keyset pagination - so you could construct a URL like this: /?sql=select+*+from+legislators+order+by+id&_pagination_column=id If a pagination column has been specified, Datasette would use the same trick it uses on regular table pages and add next links that way. Would that work for you? The other, probably easier option is a setting that enables offset/limit pagination of arbitrary SQL queries - turned off by default, but easy to turn on for users who are running Datasette on a private server. If that takes several seconds people can at least opt into it.
- samwillis 4y ago
  Either would work, but as you said the latter is easer to implement and would have done the job for what I was working on. If I had had more time I would have looking into trying to do it myself, but there is never enough time...