Pagination in DataSources allows handling large datasets efficiently by showing only a portion of records at a time. To implement pagination, the @limit and @offset parameters are used, which are automatically provided by Pragmatic.



Implementation by DataSource Type

SQL Case

The following example shows how to use the @limit and @offset parameters in an SQL query to implement pagination:

-- 1. Select the necessary fields
SELECT 
    id, 
    title, 
    date
FROM 
    events 
WHERE 
    -- 2. Apply conditional filters
    -- If no title is provided, show all records
    (@parameter.title IS NULL OR title LIKE CONCAT('%', @parameter.title, '%')) AND
    -- If no date is provided, show all records
    (@parameter.date IS NULL OR date <= @parameter.date)
-- 3. Apply pagination
LIMIT @limit  -- Number of records per page
OFFSET @offset -- Records to skip

In this example:

  • @limit controls how many records will be returned per page
  • @offset determines from which record to start, allowing navigation between pages
  • The filters @parameter.title and @parameter.date are examples of filters that are configured from a Prompt or List field



MongoDB Case

For a MongoDB type datasource, pagination is implemented as follows:

[
  // 1. Filtering stage (match)
  {
    "$match": {
      "$expr": {
        "$and": [
          // 2. Title filter
          {
            "$or": [
              { "$eq": [ @parameter.title, null ] },  // If no title, don't filter
              { "$eq": [ "$title", @parameter.title ] }  // If title exists, filter by equality
            ]
          },
          // 3. Date filter
          {
            "$or": [
              { "$eq": [ @parameter.date, null ] },  // If no date, don't filter
              { "$lte": [ "$date", { "$dateFromString": { "dateString": @parameter.date } } ] }  // If date exists, filter by less than or equal
            ]
          }
        ]
      }
    }
  },
  // 4. Pagination stage
  { "$skip": @offset },  // Skip records according to offset
  { "$limit": @limit }   // Limit number of records per page
]

In this example:

  • @limit is used in the $limit stage to control the number of records per page
  • @offset is used in the $skip stage to determine from which record to start
  • The filters @parameter.title and @parameter.date are examples of filters that are configured from a Prompt or List field



JavaScript Function Case

For a JavaScript function type datasource, pagination is implemented as follows:

// 1. Get the necessary pagination parameters
const search = @parameter.search
const recordsPerPage = @limit
const recordsToSkip = @offset

// 2. Make the API request
return axios.get('https://api.example.com/products', {
    params: {
        search: search,
        limit: recordsPerPage,
        skip: recordsToSkip
    }
})
.then((response) => {
    // 3. Process the response
    const data = response.data || {}
    
    // 4. Transform the data to the required format
    const results = (data.products).map(product => ({
        id: product.id,
        name: product.name,
        price: product.price
    }))

    // 5. Return the object with the required structure for pagination
    return {
        results: results,
        totalRecords: data.total
    }
})
.catch((error) => {
    console.error('Error getting products:', error)
})

In this example:

  • @limit and @offset are used as pagination parameters in the API call
  • It is mandatory to return the axios promise for Pragmatic to process it correctly
  • Inside the axios then, an object must be returned with the following structure:
    • results: Array of objects with the obtained data
    • totalRecords: Total number of records (must be a positive integer)
  • If this return structure is not met, pagination will not work correctly