# Filtering, Sorting, Pagination

The returned response may contain over million rows in some cases. You can narrow down the large result set by employing a [filter](#filtering) in your request and/or using [pagination](#pagination) to define how many rows should be returned in the response.
Returned rows can be also [sorted by](#sorting) the specified attribute.

## Filtering

Set up the filter on the object's attributes. There are two types of filters available – [simple](#simple-filtering) and [advanced](#advanced-filtering).

### Simple Filtering

The following filter settings return only those objects where the `version` attribute is *8* and (the filters are ANDed)
the `currency` attribute is *USD*:


```json
{
   "textMatchStyle":"exact",
   "data":{
      "version":"8",
      "currency":"USD"
   }
}
```

The following filter returns only those objects where the `sku` attribute starts with *P10* (by the `textMatchStyle` equals to `startWith` filter setting):


```json
{
   "textMatchStyle":"startsWith",
   "data":{
      "sku":"P10"
   }
}
```

The following filter returns only objects where the `sku` attribute contains *product* (by the `textMatchStyle` equals to `substring` filter setting):


```json
{
   "textMatchStyle":"substring",
   "data":{
      "sku":"product"
   }
}
```

### Advanced Filtering

The advanced filter (specified by `"_constructor":"AdvancedCriteria"`) enables you to employ more complex filtering options.

The following filter example returns only workflows where `code` equals to `Q` **AND** (defined by `"operator":"and"`) `workflowStatus` equals to `Approved`.


```json
{
   "data":{
      "_constructor":"AdvancedCriteria",
      "operator":"and",
      "criteria":[
         {
            "fieldName":"code",
            "operator":"equals",
            "value":"Q"
         },
         {
            "fieldName":"workflowStatus",
            "operator":"equals",
            "value":"APPROVED"
         }
      ]
   }
}
```

As you can see above, you can define filter objects within the `criteria` object (array).

Each filter object consists of:

- `fieldName` : The name of the field you want to filter results by. Use the `custom` operator to apply a custom JPQL/HQL expression within the `fieldName` – see the example below.
- `operator` : The operator of the filter criteria. See [the list of available operators](#operators). See the [custom()](https://developer.pricefx.eu/pricefx-api/groovy/master/com/googlecode/genericdao/search/Filter.html#custom(java.lang.String)) method for more details regarding the `custom` filter.
- `value` : The value of the field you want to filter results by.
- `start` : The start date of the date range when using, for example, the `iBetween` operator. Use the ISO 8601 date format (YYYY-MM-DD).
- `end` : The end date of the date range when using, for example, the `iBetween` operator. Use the ISO 8601 date format (YYYY-MM-DD).


Example – when the filter below is applied, only records where *lastUpdateDate* is not equal to *attribute30* are returned:


```json
{
    "startRow": 0,
    "endRow": 300,
    "data": {
        "operator": "or",
        "criteria": [{
            "operator": "custom",
            "fieldName": "lastUpdateDate<>attribute30",
            "value": ""
        }]
    },
    "distinctResults": false,
    "valueFields": []
}
```

### Custom JPQL/HQL expression operators

The following operators can be used when a `custom` filter is applied:

- Comparison operators: `=`, `<>` (or `!=`), `<`, `>`, `<=`, `>=`. These are used to compare one expression with another. The result is true only if the comparison is valid.
- Logical operators: `AND`, `OR`, `NOT`. These are used to combine or invert conditions.
- Null comparison operators: `IS NULL`, `IS NOT NULL`. These are used to check whether a value is null or not.
- Arithmetic operators: `+`, `-`, `*`, `/`, mod. These are used to perform basic arithmetic operations.
- `LIKE` operator: Used for string matching. The '%' character can be used as a wildcard.
- `BETWEEN` operator: Used to filter the result within a range of values.
- `IN` operator: Used to check if a value is within a list of values.
- `Member Of`: Used to check if a specific entity instance is a contained in a collection.
- `EMPTY` operator: Used to check if a collection is empty or not.
- `SIZE` operator: Used to determine the number of elements in a collection.


### Operators

| Name | Operator |
|  --- | --- |
| equals (match case) | `equals` |
| equals | `iEquals` |
| not equal (match case) | `notEqual` |
| not equal | `iNotEqual` |
| less than or equal to | `lessOrEqual` |
| less than | `lessThan` |
| greater than or equal to | `greaterOrEqual` |
| greater than | `greaterThan` |
| is null | `isNull` |
| is not null | `notNull` |
| contains (match case) | `contains` |
| contains | `iContains` |
| contains pattern (match case) | `containsPattern` |
| contains pattern | `iContainsPattern` |
| does not contain (match case) | `notContains` |
| does not contain | `iNotContains` |
| starts with (match case) | `startsWith` |
| starts with | `iStartsWith` |
| does not start with (match case) | `notStartsWith` |
| does not start with | `iNotStartsWith` |
| ends with (match case) | `endsWith` |
| ends with | `iEndsWith` |
| does not end with (match case) | `notEndsWith` |
| does not end with | `iNotEndsWith` |
| between | `iBetween` |
| between (inclusive) | `iBetweenInclusive` |
| is one of | `inSet` |
| is not one of | `notInSet` |
| and | `and` |
| or | `or` |
| not | `not` |
| custom operator | `custom` |


## Sorting

You can set the sort order of the returned objects.
Use the `sortBy` parameter - an array of strings that specify one or more fields to sort on (in that order).

By default, the order is ascending, but if you need descending, you can prepend "-" (minus) character before the name of the field you want to sort by.
The following sorting setting sorts the returned result by the `label` field in descending order:


```json
{
   "sortBy":[
      "-label"
   ]
}
```

## Pagination

Commands of the type **fetch** enforce the maximum result size of 1 million rows. This is enforced for unbounded requests (`startRow` and `endRow` parameters not set) or when the delta between them exceeds that number.

- `startRow` is **inclusive** (the first row that will be returned).
- `endRow` is **exclusive** (the first row that will not be returned).


> **Please note**: It is generally advisable to fetch results in reasonably sized batches and support the paging mechanism of the protocol (`startRow` / `endRow` / `totalRows`).


Implement the pagination to return a defined number of items in the result and/or to return a specified result set. Pricefx REST API utilizes `startRow` and `EndRow` parameters to set the pagination on returned objects.
The following pagination setting returns 300 rows, starting with row 2700 and ending with row 2999:


```json
{
   "startRow":2700,
   "endRow":3000
}
```

### Batch Pagination Without Overlaps

The following example shows how to page through data in batches without missing or overlapping rows. Ranges are contiguous and non-overlapping because `endRow` is exclusive.

**Batch 1**


```json
{
   "startRow":2700,
   "endRow":3000
}
```

**Batch 2**


```json
{
   "startRow":3000,
   "endRow":3300
}
```

**Batch 3**


```json
{
   "startRow":3300,
   "endRow":3600
}
```

### Recommendation

- Use a **consistent sort order** (e.g. by all key fields) across all requests.
- Ensure the **data set does not change** between paged requests if you need strict consistency (no overlap, no gaps).