Paginate and PaginateInline: Returning data in pages

The Paginate and PaginateInline extensions lets recipes be paginated, searched and sorted.

class recipe.Paginate(*args, **kwargs)

Allows recipes to paginate results. Pagination also supports searching and sorting within paginated data.

Using and controlling pagination

Pagination returns pages of data using limit and offset.

Pagination is enabled by setting a nonzero page size, like this:

shelf = Shelf({
    'state': Dimension(Census.state),
    'gender': Dimension(Census.gender),
    'population': Metric(func.sum(Census.population)),
})
recipe = Recipe(shelf=shelf, extension_classes=[Paginate])            .dimensions('state')            .metrics('population')            .pagination_page_size(10)

Pagination may be disabled by setting .apply_pagination(False).

Searching

pagination_q allows a recipe to be searched for a string. The default search fields are all dimensions used in the recipe. Search keys can be customized with pagination_search_keys. Search may be disabled by setting .apply_pagination_filters(False) The value role will be targetted when searching dimensions.

Sorting

Pagination can override ordering applied to a recipe by setting .pagination_order_by(…) to a list of ordering keys. If keys are preceded by a “-”, ordering is descending, otherwise ordering is ascending.

An example using all features

Here’s an example that searches for keys that start with “t”, showing the fifth page of results:

shelf = Shelf({
    'state': Dimension(Census.state),
    'gender': Dimension(Census.gender),
    'age': Dimension(Census.age),
    'population': Metric(func.sum(Census.population)),
})
recipe = self.recipe()            .metrics("pop2000")            .dimensions("state", "sex", "age")            .pagination_page_size(10)            .pagination_page(5)            .pagination_q('t%')            .pagination_search_keys("state", "sex")

This will generate SQL like:

SELECT census.age AS age,
       census.sex AS sex,
       census.state AS state,
       sum(census.population) AS population
FROM census
WHERE lower(census.state) LIKE lower('t%')
  OR lower(census.sex) LIKE lower('t%')
GROUP BY census.age,
         census.sex,
         census.state
LIMIT 10
OFFSET 40

apply_pagination(value: bool)

Should this recipe be paginated.

Parameters

value (bool) – Enable or disable pagination for this recipe, default True

apply_pagination_filters(value: bool)

Should this recipe apply the paginations query filtering.

Should paginate_q be used to apply a search on paginate_search_keys or all dimensions used in the recipe.

Parameters

value (bool) – Enable or disable pagination filtering for this recipe, default True

pagination_order_by(*value: Union[list, tuple])

Sort this pagination by these keys. Pagination ordering is applied before any other order_bys defined in the recipe.

Parameters

value (list(str)) – A list of keys to order the paginated recipe by

pagination_page(value: int)

Fetch this page.

Parameters

value (integer) – A positive integer page number to fetch

pagination_q(value: str)

Search this recipe for this string. The search is an case insensitive like that ORs all dimensions in the recipe by default.

To search for a substring, use a percentage sign for wildcard, like ‘%searchval%’.

pagination_search_keys can be used to customize what keys are used for search.

Parameters

value (str) – A query string to search for this in this recipe. The query string is evaluated as a ilike on all dimensions in the recipe or pagination_search_keys if provided

pagination_search_keys(*value: Union[list, tuple])

When querying this recipe with a pagination_q, search these keys

pagination_search_keys do not have to be used in the recipe.

Parameters

value (list(str)) – A list of keys to search in the paginated recipe