Implementing pagination

Use case

We want to display a table of data with hundreds of rows. To make the table easier to digest and to improve the performance of the query, we'll use pagination. With the recipe below, we'll get the orders list sorted by the order number. Every page will have 5 orders.

Data modeling

We have the following data model:

cubes:
  - name: Orders
    sql_table: orders
 
    measures:
      - name: count
        type: count
 
    dimensions:
      - name: number
        sql: number
        type: number
 
      - name: created_at
        sql: created_at
        type: time

Query

To select orders that belong to a particular page, we can use the limit and offset query properties. First, let's get the number of all orders that we have.

{
  "measures": ["orders.count"]
}

Then, let's retrieve first batch (page) of five orders:

{
  "dimensions": ["orders.number"],
  "order": {
    "orders.number": "asc"
  },
  "limit": 5
}

Now, let's retrieve the second batch (page) of five orders:

{
  "dimensions": ["orders.number"],
  "order": {
    "orders.number": "asc"
  },
  "limit": 5,
  "offset": 5
}

Result

We have received five orders per query and can use them as we want.

// Orders count:
 
[
  {
    "orders.count": "10000",
  },
];

// The first five orders:
 
[
  {
    "orders.number": 1,
  },
  {
    "orders.number": 2,
  },
  {
    "orders.number": 3,
  },
  {
    "orders.number": 4,
  },
  {
    "orders.number": 5,
  },
];

// The next five orders:
 
[
  {
    "orders.number": 6,
  },
  {
    "orders.number": 7,
  },
  {
    "orders.number": 8,
  },
  {
    "orders.number": 9,
  },
  {
    "orders.number": 10,
  },
];

Source code

Please feel free to check out the full source code (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.