Skip to content

Instantly share code, notes, and snippets.

@i-e-b

i-e-b/column paging.md

Last active January 27, 2023 10:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save i-e-b/9f4d9a536377d3c73fa6d6fb1ca27146 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save i-e-b/9f4d9a536377d3c73fa6d6fb1ca27146 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. i-e-b revised this gist Jan 27, 2023. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions column paging.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,11 @@ To get page 1 when ordering by `AssetId`
    SELECT * FROM AssetTable WHERE AssetId_page = 1 ORDER BY AssetId_data;
    ```

    To get "2nd" page when sorting by location in reverse order
    ```
    SELECT * FROM AssetTable WHERE Location_page = (MAX(Location_page)-1) ORDER BY Location_data DESC;
    ```

    ## Example

    Page size of two to make the example short. Should really be 10 or so.
  2. i-e-b created this gist Jan 27, 2023.
    39 changes: 39 additions & 0 deletions column paging.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,39 @@
    ## Idea

    For cached queries, each column has a matching 'page-column', that gives the page is should be on *if sorted by that column*

    That way, we can have a single 'paged' data table, but still correctly sort and page by any column.

    e.g. (using sample data below)

    To get page 3 when ordering by `Type`
    ```
    SELECT * FROM AssetTable WHERE Type_page = 3 ORDER BY Type_data;
    ```

    To get page 1 when ordering by `AssetId`
    ```
    SELECT * FROM AssetTable WHERE AssetId_page = 1 ORDER BY AssetId_data;
    ```

    ## Example

    Page size of two to make the example short. Should really be 10 or so.

    Asset Table

    ![image](https://user-images.githubusercontent.com/576220/215063385-529de5b0-1081-493a-86ce-ca2f1e6b6bc3.png)

    ```
    AssetId_data AssetId_page Name_data Name_page Location_data Location_page Type_data Type_page AcquiredDate_data AcquiredDate_page
    1 1 Chair 2 London 2 OfficeFurniture 3 2021-01-05 2
    2 1 Cabinet 1 London 2 OfficeFurniture 3 2022-06-04 5
    3 2 Desk 4 London 3 OfficeFurniture 4 2020-08-11 1
    4 2 Computer 3 London 3 IT 2 2021-07-05 3
    5 3 Chair 2 Paris 4 OfficeFurniture 5 2022-05-04 4
    6 3 Cabinet 1 Paris 4 OfficeFurniture 4 2021-05-05 3
    7 4 Desk 4 Paris 5 OfficeFurniture 5 2020-06-08 1
    8 4 Computer 3 Paris 5 IT 2 2021-04-04 2
    9 5 Server 5 Berlin 1 IT 1 2022-01-05 4
    10 5 Server 5 Berlin 1 IT 1 2023-01-11 5
    ```