Multiple Retrieve
HTTP Method: PUT
Base Syntax:
http://deepserhost/api/rest/[entities]?[filters]&[parameters]
Example Syntax:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%&limit=2
Description: retrieves the entities data, given some filters or parameters.
Example Result:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%&limit=2
{
"totalRecords": 3,
"items": [
{
"entity_id": "1",
"parent_id": "0",
"sort_order": null,
"name": "ACME International",
"description": "ACME International - Main Company of the group",
"phone": "0288396",
"fax": "998266",
"address": "Madison Square Garden",
"city": "NY",
"state": "NY",
"country": "USA",
"zip_code": "00195",
"notes": null,
"logo": "company/image/a/c/acme_usa.png",
"primary_contact": "4\\euclid",
"mailbox_id": "0",
"status": "1",
"formtemplate_id": "56",
"updated_at": "2018-07-12 08:43:13",
"created_at": "2018-06-19 13:30:41",
"payment_status": null
},
{
"entity_id": "2",
"parent_id": "1",
"sort_order": null,
"name": "ACME France",
"description": "ACME France",
"phone": null,
"fax": null,
"address": "Roux de Strasse",
"city": "Paris",
"state": "Paris",
"country": "France",
"zip_code": "75000",
"notes": null,
"logo": "company/image/a/c/acme_fr_1.png",
"primary_contact": "4\\euclid",
"mailbox_id": "0",
"status": "1",
"formtemplate_id": "56",
"updated_at": "2018-07-12 08:43:56",
"created_at": "2018-06-19 13:33:57",
"payment_status": null
}
]
}
Where:
totalRecords is the number of items corresponding to the conditions of the query (note that is more than the limit we set).
items is a JSON array with all entities retrieved (eventually limited to the limit parameter).
FILTERS
When we deal with the multiple retrieve method, it is important to understand filters and parameters to get only a subset of data if we don’t need to retrieve all the entities of the system.
Think about filters as the where condition of a SQL Query, while parameters are clauses (limit, sort, etc.) for that query.
Deepser API filters are very simple to implement.
To define filters, just pass additional fields to the HTTP request query string.
The syntax to define filters is here described.
After the request path we define an array called filter.
This array has at least one element with key attribute to define the target field:
http://deepserhost/api/rest/companies?filter[1][attribute]=field
Where:
- filter is our array to set filters
- attribute is a constant key
- field is the name of the field we want to apply the filter to
Then, we must define which kind of filter we want to apply to that field, by populating in the query string the element of the filter array with key corresponding to the kind of filter. The value of that element is the value to look for:
http://deepserhost/api/rest/companies?filter[1][attribute]=field&filter[1][operator]=value
Where:
- filter is our array to set filters and the index 1 is the same of the attribute field we are referring to
- operator is a key with the query operator we want to apply. Operators are described below
- value is the value of the condition we want to apply to the filter
To filter a company by its name, taking all the companies with name starting with the string ‘ACME’, we should raise this GET request:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%
Obviously, to implement more filters on different fields we can add more elements to our filter array:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%&filter[2][attribute]=country&filter[2][eq]=USA
This will filter all companies with name starting with ACME and country equals to USA.
FILTER OPERATORS
When we deal with filters, it is important to understand filter operators to create the query we need.
Here is a list of all operators that can be used:
- eq – “equal to” – returns items with the specified attribute that is equal to the defined value
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%
- neq – “not equal to” – returns items with the specified attribute that is not equal to the defined value
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][neq]=ACME
- in – “equals any of” – returns items that are equal to the item(s) with the specified attribute(s)
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][in]=ACME
If we want to use multiple values for the in, we can use an additional array, so for example:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][in][0]=ACME&filter[1][in][1]=Contoso
- nin – “not equals any of” – returns items excluding the item with the specified attribute
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nin][1]=ACME&filter[1][nin][2]=International
- gt – “greater than” – returns items with the specified attribute that is greater than the defined value
http://deepserhost/api/rest/companies?filter[1][attribute]=parent_id&filter[1][gt]=0
- lt – “less than” – returns items with the specified attribute that is less than the defined value
http://deepdeskhost/api/rest/companies?filter[1][attribute]=parent_id&filter[1][lt]=1
- like – “like” – returns items with the specified attribute is like the defined value. Use SQL like syntax to define wildcards chars.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%
- nlike – “not like” – returns items with the specified attribute is not like the defined value. Use SQL like syntax to define wildcards chars.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nlike]=ACME%
- from, to – “from to” – specifies the range of attributes according to which items will be returned.
http://deepserhost/api/rest/companies?filter[1][attribute]=parent_id&filter[1][from]=2&filter[1][to]=3
You can write filters using AND, OR logical operators.
To do that follow the examples:
- AND: to define a condition in AND, simply define multiple filters, like:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][like]=ACME%&filter[2][attribute]=parent_id&filter[2][gt]=0
- OR: to define a condition in OR, use this syntax:
http://deepserhost/api/rest/companies?filter[1][attribute]=entity_id&filter[1][0][eq]=1&filter[1][1][eq]=3
So use multiple index arrays in the filters for the same field.
To use an OR condition on different fields, use the following syntax:
http://deepserhost/api/rest/companies?filter[1][attribute]=entity_id&filter[1][0][eq]=1&filter[1][1][eq]=3
PARAMETERS
When we deal with retrieve multiple method, it is important to understand parameters to create the query we need.
Here is a list of all parameters that can be used:
- page: specifies the page number which items will be returned.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nlike]=ACME%&page=2
- order: specifies the sort order of returned items.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nlike]=ACME%&order=name&dir=asc
- dir: specifies the order direction: ‘asc’ – returns items in the ascending order; ‘dsc’ – returns items in the descending order.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nlike]=ACME%&order=name&dir=asc
- limit: limits the number of returned items in the response. Note that by default, 10 items are returned in the response. The maximum number is 100 items.
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][nlike]=ACME%&limit=100
If the attribute value consists of several words separated by a whitespace, the ‘%20’ sign is used:
http://deepserhost/api/rest/companies?filter[1][attribute]=name&filter[1][eq]=ACME%20International%20LTS