Returning total record count with paginating search criteria

🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions.

1. Overview

The current pagination functionality does not provide an API-based way to retrieve the total number of items based on the search conditions used in the query. External systems are required to iterate through all resource pages until an empty response is encountered. This is inconvenient in development and complicates the ability to offer a user-friendly interface to end-users.

To enhance the user experience, it is proposed to implement the capability to create search conditions with a new pagination type that additionally returns information about the current page, the number of items per page, the total number of items, and the total number of pages.

2. Actors and user roles

  • Regulations developer

  • External systems

3. General provisions

  • The behavior and contract of existing search conditions remain unchanged.

  • Backward compatibility of search conditions configuration is maintained.

4. Functional scenarios

  • Setting search conditions

  • Generation of search conditions services

  • Synchronous API call of search conditions

  • Asynchronous API call of search conditions

  • API call of search conditions from external systems, with and without Trembita (secure exchange gateway name in Ukraine, UA-specific)

5. Current implementation

The current implementation allows passing offset and limit when calling the API of search conditions. This capability is enabled by default for all search conditions but can be disabled using the attribute tag createSearchCondition pagination=false.

The API response, regardless of whether the pagination option is enabled or not, contains only an array of elements based on which the search was performed. Information about the total number of items or pages is not included in the response. If offset and limit were passed, the response is formulated considering them.

6. Target design

6.1. Schema and liquibase tags extension module

In the schema of extended tags, the attribute type pagination changes to an enumeration, consisting of the following values: offset, none, page, and true and false, which are synonyms for offset and none for backward compatibility.

Schema of extended Liquibase tags
	<xsd:simpleType name="paginationType">
		<xsd:restriction base="xsd:string">
			<xsd:enumeration value="offset"/>
			<xsd:enumeration value="page"/>
			<xsd:enumeration value="none"/>
      <!--Following is for the backward compatibility-->
			<xsd:enumeration value="true"/> <!--Synonym for "offset"-->
			<xsd:enumeration value="false"/> <!--Synonym for "none"-->
		</xsd:restriction>
	</xsd:simpleType>
  ....
	<xsd:complexType name="selectSearchConditionType">
		....
		<xsd:attribute name="pagination" type="paginationType" use="optional" default="offset"/>
	</xsd:complexType>

In the Liquibase tags extension module, the ability to record the pagination attribute value for new pagination types is added to the ddm_liquibase_metadata metadata table when processing the createSearchCondition tag.

6.2. Service generator

The values of offset and none are equivalent to true and false. If the pagination attribute is set to offset or none for the search condition, the logic of generating services should be the same as it is for true and false, respectively.

If the pagination attribute is set to page for the search condition, a service should be generated that, in addition to the filters defined in the search condition, accepts:

pageSize

desired number of items per page. Default is 10

pageNo

desired page number. Default is 0.

The response should contain the following attributes:

content

an array of elements based on which the search was performed, limited by the page number and number of items per page

totalElements

the total number of elements based on the query

totalPages

the total number of pages based on the query

pageSize

number of items per page

pageNo

returned page number

For example
{
  "content": [
    {
      "col1": "ADMIN"
    },
    {
      "col1": "USER"
    }
  ],
  "totalElements": 1,
  "totalPages": 1,
  "pageNo": 0,
  "pageSize": 20,
}

To obtain the content, the service sends a query to the database, generated in the same way as in the current implementation with limit and offset. In this case, the values of limit and offset are not taken directly from the API query but are calculated from the received pageSize and pageNo. Where limit=pageSize and offset=pageSize*pageNo.

To obtain totalElements, the service sends an additional query to the database, generated according to the following template:

SELECT COUNT(*)
  FROM <search_condition_view>
 WHERE <filtering conditions>

totalPages equals ceil(totalElements/pageSize)

pageNo and pageSize are the values applied in the query - either from input parameters or by default.

Example OpenAPI specification

(Download)

Details

Additionally, an equivalent Kafka API for asynchronous interaction and SOAP for interaction with external systems should be generated if the corresponding options in the registry regulations for the search condition are enabled.

Spring has standard tools for implementing this type of pagination. Example implementation: Using jOOQ With Spring: Sorting and Pagination

6.3. System components and their roles within the solution design

This section lists the system components that are involved or require changes/creation within the implementation of the functional requirements according to the technical design of the solution.

Component Service name Purpose / Aim of changes

Service Generator

service-generation-utility

Generation of Java projects for services

Liquibase extended gags schema

liquibase-ext-schema

Schema validation

Liquibase tags extension module

liquibase-ddm-ext

Processing of extended tags during regulation deployment

7. Registry regulations modeling

7.1. Search conditions modeling

The registry regulations administrator is provided the ability to choose the pagination type page when modeling search conditions.

Registry regulations structure
Figure 1. Registry regulations structure
Configuration example
    <changeSet author="registry owner" id="create pageable SC factor_chemical_host_contains_name">
        <ext:createSearchCondition name="factor_chemical_host_contains_name_pageable"  pagination="page">
            <ext:table name="factor" alias="f">
                <ext:column name="factor_id"/>
                <ext:column name="name" sorting="asc" searchType="contains"/>
            </ext:table>
            <ext:where>
                <ext:condition tableAlias="f" columnName="factor_type" operator="eq" value="'Chemical: HOST'"/>
            </ext:where>
        </ext:createSearchCondition>
    </changeSet>

The options offset and none are also available to the registry regulations administrator in the pagination attribute.

offset — provides the same result as true in the current implementation and is the default option.

none — provides the same result as false in the current implementation.

The options true and false become deprecated and may be removed in the future. However, for existing registry regulations, they will continue to function the same way as in the current implementation.

7.2. Registry regulations validation

As part of the solution implementation, it is necessary to extend the XML schema of extended Liquibase tags against which validation takes place.

8. High-level development plan

8.1. Technical expertise

  • BE

8.2. Development plan

  • Extension of the liquibase extended tags schema.

  • Extension of the liquibase tags extension module.

  • Extension of the service generator.

  • Development of instructions for the regulation developer and reference examples.