Filtering
- Jeremy Bruce (Unlicensed)
- Ted Jordan (Unlicensed)
- Lance Meyrick (Unlicensed)
Filter options are added to the end of the Query string in the URL as key/value pairs. Strict matching between fieldname and value is supported (i.e. "owner=123"); as well as, a range of supported operations. To use the support operations the following paradigm is employed: the key consists of the fieldname on which to filter (i.e. "owner") plus an operation identifier denoting the desired operation (i.e. "_eq"). The filter option may then look like this: "owner_eq=123"
Available Filter Options
| Operation | Operation Identifier | Notes |
|---|---|---|
| Equal | [[Fieldname]]_eq | |
| Not Equal | [[Fieldname]]_nteq | |
| In | [[Fieldname]]_in | for example, /api/rest/actions?actionType_in=1,2,3,4 |
| Not In | [[Fieldname]]_ntin | for example, /api/rest/actions?actionType_ntin=1,2,3,4 |
| Greater Than | [[Fieldname]]_gt | |
| Less Than | [[Fieldname]]_lt | |
| Greater Than Equal To | [[Fieldname]]_gteq | |
| Less Than Equal To | [[Fieldname]]_lteq | |
| Like | [[Fieldname]]_like | for example /api/rest/actions?name_ilike=*test* |
| ILike | [[Fieldname]]_ilike | Case insensitive option |
| Not Like | [[Fieldname]]_ntlike | |
| Not ILike | [[Fieldname]]_ntilike |
Linked Resources
To filter on linked resources you can either filter on the primary key of the linked resource (ID), or by specifying a column.
For example if you want to filter time records on the "owner" linked resource you can do this as follows:
.../timerecords?owner_eq=78 or: ../timerecords?owner[id_eq]=78
or, by some other column
.../timerecords?owner[lastName_eq]=Jones
Sublink Resources
You can link resources to other links to drill down even further. For example if you want to find all the actions with a division name that contains the phrase "demonstration" you can write it as follows:
GET /api/rest/actions?division[participant][displayName_ilike]=demo*
The API will translate this into a SQL query something like the following (not that you need to know this, but it might help you understand the example)
SELECT * FROM action WHERE division in (SELECT id FROM division WHERE participant in (SELECT id FROM participant WHERE displayName ILIKE 'demonstration%'))
Filter Options By Field Type
Available filtering options differ from field type to field type. There are 5 possible field types: Integer, Char, Varchar, Numeric and Datetime.
| Field Type | Equal | Not Equal | In | Not In | Greater Than | Less Than | Greater Than Equal To | Less Than Equal To | Like | iLike | Not Like | Not iLike |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Integer |  | | | | | | | | | |||
| Char | | | | | ||||||||
| Varchar | | | | | | | | | ||||
| Numeric | | | | | | | | | ||||
| Datetime | | | | | | |
?Filter option
You can optionally filter using the "?filter" directive with a url-encoded SQL query string as the value.
Example:
?filter=primaryParticipants.displayName ilike '%Des%' OR primaryParticipants.displayName ilike '%Smith%'
Which should look like the following once URL-encoded
?filter=primaryParticipants.displayName+ilike+'%25Des%25'+OR+primaryParticipants.displayName+ilike+'%25Smith%25'
Don't forget to URL-encode the query string