table pagination

[jgranduel]

Hi,

thanks for this project that I exploring for an SQLite quick frontend. So, is there a way to paginate large tables? As it's mentionned in the wishlist/roadmap (#69), I guess it's not...

[lovasoa]

Hi !

You can paginate tables manually using LIMIT and the steps component to render a list of pages.

Here is an example, let me know if it does what you want: https://replit.com/@pimaj62145/SQLPage-pagination?v=1

-- render the table
SELECT 'table' AS component;
SELECT * FROM todo
WHERE id >= COALESCE($start_id, 0) ORDER BY id LIMIT 5;

-- render the page numbers with links for pagination
SELECT 'steps' as component, true as counter;
SELECT format('?start_id=%s', id) AS link
FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS table_index FROM todo)
WHERE table_index % 5 = 1;

Paginating in the component itself on the frontend wouldn't be very useful, since that would require loading all the data all of the time anyway. Is that what you were looking for ?

[jgranduel]

Thanks, it works fine. I hadn't seen steps component yet. My table has >10e6 rows... A bit too advanced for my current sqlpage level though!

[lovasoa]

My table has >10e6 rows.

If you have a very large table, then maybe what you need is not pagination, but search, or faceted navigation ? What exactly is in the table and what do you want your users to do with it ?

A bit too advanced for my current sqlpage level though!

Is there something else I can help you with ?

[jgranduel]

Sorry for late answer. Well, I tried sqlpage with a local DB that contains file metadata. Say I have a column of extensions and I want to show distinct extensions, count files with this extension, calculate sum of lengths of a subcategory of files. Some columns could be of JSON type. Faceted research would be great, or an plain SQL editor indeed.

I compare your tool with what can be done with datasette.

Thanks!

[lovasoa]

All of that seems very possible with SQLPage, and you'll end up with something that looks better and if more versatile than datasette. Let me know if there's something you're having troubles implementing in SQLPage, I'll be happy to help.

[lukavia]

You can try this:

Put this in your big query:LIMIT IFNULL($page, 25) OFFSET IFNULL($offset, 0);

and then create some navigation buttons

SELECT 'button' as component,
  TRUE AS center;
SELECT '|<' as title, 
  '?offset=0&page=' || IFNULL($page, 25) as link,
  cast(IFNULL($offset, 0) as integer) <= 0 as disabled;
SELECT '<<' as title, 
  '?offset=' || (IFNULL($offset, 0) - IFNULL($page, 25)) || '&page=' || IFNULL($page, 25) as link,
  cast(IFNULL($offset, 0) as integer) <= 0 as disabled;
SELECT '>>' as title, 
  '?offset=' || (IFNULL($offset, 0) + IFNULL($page, 25)) || '&page=' || IFNULL($page, 25) as link
  ;

you can move or copy the navigation buttons section before the query, so they appear at the top

[ggaughan]

Hello,

Thanks to the excellent tutorial, I've knocked up an example of pagination that works with large tables. The name SQLPage insists on having good pagination I think.

My example is here: https://github.com/ggaughan/SQLpage_examples/tree/main/pagination_efficient

It needs SQLPage v0.18.0 or higher (the _sqlpage_embed is invaluable) and uses htmx. The very first run will pause while it builds a sample million row table (~160MiB).