[openstreetmap/openstreetmap-website] Add Sorting with Turbo Pagination and Extend to Sortable Columns (Issue #5259)

[Emin Kocan]

You bring up a valid point about the limitations of cursor-based pagination with sorting. Cursor pagination works efficiently when sorting by a unique or sequential field (like `id`, `created_at`, or `updated_at`), but it breaks down when we try to sort by non-unique columns (e.g., `name`, `status`).

To address this, there are a couple of potential solutions:

1. **Combined Sorting**: We could continue using cursor-based pagination but with a fallback to a secondary unique column like `id`. This way, sorting by `name` or `status` would still function but would maintain stable pagination by also ordering by `id` to ensure uniqueness:
   
   ```sql
   ORDER BY name ASC, id ASC
   ```

   This approach would retain the benefits of cursor-based pagination while allowing some flexibility with sorting.

2. **Offset-based Pagination**: If we want to fully support sorting by arbitrary columns, we could switch to offset-based pagination when sorting by non-unique columns. This is less efficient on larger datasets but avoids the problem of ambiguous cursor positions.

3. **Hybrid Approach**: Implement cursor-based pagination for sortable fields that are unique (like `id` or timestamps), and fallback to offset-based pagination for others.

Before proceeding, I’d like to get some thoughts on which approach fits best with our performance requirements and use cases. If we'd prefer sticking with cursor-based pagination, I can explore implementing the combined sorting method, as it strikes a balance between efficiency and flexibility.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/5259#issuecomment-2411376926
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/issues/5259/2411376926 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20241014/b80a05fc/attachment.htm>