Confusing sort interface

[cbertelli]

Introduction

Any table which has more rows than the page size can deliver wrong results if sorted by the column headers in the table component. This is significant if you have a table lots of rows that is paginated by the pagination component.

To Reproduce

The issue is very simple, just select a table with more tuples then your pagination (say a pagination of 100 for a total records of 500), sort it by the table header and then advance to the following page.

SELECT 'table' AS component, true AS sort,
    '/atti/edit.sql?id_soc={id}' AS edit_url,
    '/atti/delete.sql?id_soc={id}' AS delete_url;

SELECT
    t.id_atto::text AS _sqlpage_id,
    COALESCE(t.notaio::text, '—') AS "Notaio",
    COALESCE(t.filza::text, '—') AS "Filza",
    COALESCE(t.documento_n::text, '—') AS "Documento n.",
    to_char(t.data_stipula, 'DD/MM/YYYY') AS "Data Stipula",
    COALESCE(t.tipo_atto::text, '—') AS "Tipo Atto",
    COALESCE(t.regesto::text, '—') AS "Regesto"
FROM atti AS t
ORDER BY t.id_atto
LIMIT 100 OFFSET COALESCE(CAST(NULLIF($offset, '') AS INTEGER), 0);

SELECT 'pagination' AS component;
SELECT
    (g.i / 100 + 1) AS contents,
    sqlpage.link(sqlpage.path(), jsonb_strip_nulls(jsonb_build_object('offset', g.i, 'notaio', NULLIF($notaio, ''), 'filza', NULLIF($filza, ''), 'documento_n', NULLIF($documento_n, ''), 'sort', COALESCE(NULLIF($sort, ''), 'id_atto'), 'dir', NULLIF($dir, '')))) AS link,
    (g.i = COALESCE(CAST(NULLIF($offset, '') AS INTEGER), 0)) AS active
FROM generate_series(0, GREATEST((SELECT count(*) FROM atti AS t WHERE (NULLIF($notaio, '') IS NULL OR t.notaio::text ILIKE '%' || $notaio || '%') AND (NULLIF($filza, '') IS NULL OR t.filza::text ILIKE '%' || $filza || '%') AND (NULLIF($documento_n, '') IS NULL OR t.documento_n::text ILIKE '%' || $documento_n || '%')) - 1, 0), 100) AS g(i);

Actual behavior

The order is valid only for the current page (and selection!). If I understand well, there is no way to forward it towards the database.
There is an open discussion "Table filtering, sorting, pagination." by jeneizs-74 here:
#778
It has no answer since Jan 15, 2025.

This may lead to errors and Is anyway confusing for the user who may think sorting shows the proper records, while he will get only the result of sorting the page records.

Expected behavior

I think sorting should happen on the entire table. At least there should be some way of getting sorting column(s) and direction so to force a new select on the backend.

[lovasoa]

Hello! this is indeed an limitation of the current SQLPage, we don't support server side sorting in the table component. I'm working on a potential solution. In the meantime, you have to define your own custom table component with your own sorting strategy. Just allowing server side on all columns would not be enough: if the dataset is large enough to paginate, sorting on non indexed columns should probably be disabled.

[cbertelli]

Thank you for your immediate reply (and action!).
Untile the merging il ready, is there a way to detect the "sorting action" on tables. Maybe complex scenarios could be handled by custom actions by the user. This could be less impactful on the code.
The pull solution seems anyway elegant and effective.

[lovasoa]

The only way to get it to work for now is too write (or more likely have an AI agent write) a custom table component for your use case.