API Paging and filtering

The most of our “list” v1 API use the PagingAndFiltering structure to allow unified advanced filtering on data records.

This has been systematically implemented on the most recent APIs, and added to those for the “big” tables (e.g. WalletOperation, Invoice, Customer, …)

Concept

The PagingAndFiltering structure allows for deterministic and predictable syntax for paging and filtering.

See legacy API documentation to look for list APIs using the PagingAndFiltering structure.

Field names in are the names from the Functional data model.

Parameters

Parameter	Description
fullTextFilter	Full text search filter. Mutually exclusive with filters attribute. fullTextFilter has priority
filters	Search filters (key = Filter key, value = search pattern or value). Filter key can be: "$FILTER". Value is a filter name "type_class". Value is a full classname. Used to limit search results to a particular entity type in case of entity subclasses. Can be combined to condition "ne" to exclude those classes. SQL. Additional sql to apply. Value is either a sql query or an array consisting of sql query and one or more parameters to apply <condition> <fieldname1> <fieldname2> ... <fieldnameN>. Value is a value to apply in condition A union between different filter items is AND Condition is optional Number of fieldnames depend on condition used If no condition is specified an "equals ignoring case" operation is considered Following conditions are supported `fromRange` Ranged search - field value in between from - to values. Specifies "from" part value: e.g value<=field.value. Applies to date and number type fields. `toRange` Ranged search - field value in between from - to values. Specifies "to" part value: e.g field.value<=value list. Value is in field's list value. Applies to date and number type fields. `inList`/`not-inList` Field value is [not] in value (list). A comma separated string will be parsed into a list if values. A single value will be considered as a list value of one item `minmaxRange` The value is in between two field values. TWO field names must be provided. Applies to date and number type fields. `minmaxOptionalRange` Similar to minmaxRange. The value is in between two field values with either them being optional. TWO fieldnames must be specified. `overlapOptionalRange` The value range is overlapping two field values with either them being optional. TWO fieldnames must be specified. Value must be an array of two values. `likeCriterias` Multiple fieldnames can be specified. Any of the multiple field values match the value (OR criteria). In case value contains , a like criteria match will be used. In either case case insensitive matching is used. Applies to String type fields. `wildcardOr` Similar to likeCriterias. A wildcard match will always used. A will be appended to start and end of the value automatically if not present. Applies to String type fields. `ne` Not equal. Following special meaning values are supported: `IS_NULL` Field value is null `IS_NOT_NULL` Field value is not null To filter by a related entity's field you can either filter by related entity's field or by related entity itself specifying code as value. These two example will do the same in case when querying a customer account: `customer.code=aaa` `customer=aaa` To filter a list of related entities by a list of entity codes use "inList" on related entity field. e.g. for querying offer template by sellers: `inList sellers=code1,code2` Note Querying by related entity field directly will result in exception when entity with a specified code does not exists Examples: invoice number equals "1578AU": Filter key: `invoiceNumber` Filter value: `1578AU` invoice number is not "1578AU": Filter key: `ne invoiceNumber` Filter value: `1578AU` invoice number is null: Filter key: `invoiceNumber` Filter value: `IS_NULL` invoice number is not empty: Filter key: `invoiceNumber` Filter value: `IS_NOT_NULL` Invoice date is between 2017-05-01 and 2017-06-01: Filter key: `fromRange invoiceDate` Filter value: `2017-05-01` Filter key: `toRange invoiceDate` Filter value: `2017-06-01` Date is between creation and update dates: Filter key: `minmaxRange audit.created audit.updated` Filter value: `2017-05-25` invoice number is any of 158AU, 159KU or 189LL: Filter key: `inList invoiceNumber` Filter value: `158AU,159KU,189LL` any of param1, param2 or param3 fields contains "energy": Filter key: `wildcardOr param1 param2 param3` Filter value: `energy` any of param1, param2 or param3 fields start with "energy": Filter key: `likeCriterias param1 param2 param3` Filter value: `energy` any of param1, param2 or param3 fields is "energy": Filter key: `likeCriterias param1 param2 param3` Filter value: `energy` Note* Filters passed as string in Rest GET type method are in the following format: `filterKey1:filterValue1\|filterKey2:filterValue2`
fields	Data retrieval options/fieldnames separated by a comma. See specifics on each API
offset	Pagination: fetch from record number
limit	Pagination: fetch at most “limit” records
sortBy	Sorting: field to sort by A field from a main entity being searched
sortOrder	Sorting: sort order ASCENDING DESCENDING
loadReferenceDepth	Recursive depth for fetching nested entities
totalNumberOfRecords	Total number of records Filled on response only