FilterTableAccess APIs
Query one or more tables
Overview
Allows you to query tables by providing the query in the Request body. Queries can be simple 'select' statements or more complex ones involving joins or calculated fields. The response will be a set of records that satisfy the query. A maximum of 1000 records can be returned. The results can also be ordered. The examples provided in this section all pertain to the Booking table.
To get a list of all tables that can be queried, see Get a list of tables that can be queried.
Entry point
POST /api/tableaccess/{table}/filter
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table you wish to query in {tablename} as a string:
| Name | Mandatory | Description | Default value | Example |
|---|---|---|---|---|
| Table | Yes | Table name | Booking |
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/Booking/filter
Request body
Specify fields (including calculated fields), tables (for joins), filters, sub-filters and the order (ascending or descending) in the Request body. There are various ways to create a query in the request body. Each part of the query for specifying fields, tables, filters and order has been explained separately for better understanding. Examples follow.
Fields
{
"distinct": false,
"useReadOnly": true,
"maxRecords": 0,
"fields": [
{
"fieldName": "string",
"fieldAlias": "string",
"tableName": "string",
"parameters": {
"additionalProp1": "string",
"additionalProp2": "string",
"additionalProp3": "string"
}
}
]
}
- Use "distinct": true to remove duplicates from retrieved records
- Use "useReadOnly": true to enable the API to read the data from Retain's read replica tables
- Set MaxRecords to a number less than 1000 to return the number of records. E.g. MaxRecords = 100 returns 100 records
- Specify the field name and/or field alias to return specific fields
You do not have to specify everything listed above. Field aliases are optional. Table names and parameters are only required for some calculated fields. Even field names are optional as you can specify {} to return all fields.
See examples:
Tables
Table names are required for joins.
"tables": [
{
"name": "string",
"alias": "string",
"joinType": "Inner",
"joinField": "string",
"joinToField": "string",
"joinToTable": "string"
}
]
- Enter the table name in name and/or table alias in alias
- Specify the join type which can be 'Inner' or 'Outer' in joinType
- Specify the name of the field that will be joined in joinField
- Specify the field you will join to in joinToField
- Specify the table name you will join to in joinToTable
See example:
Filters
"filter": {
"filterGroupOperator": "And",
"filterLines": [
{
"field": "string",
"operator": "Equals",
"isNot": false,
"value": "string",
"tableName": "string",
"parameters": {
"additionalProp1": "string",
"additionalProp2": "string",
"additionalProp3": "string"
}
}
],
"subFilters": [
"string"
]
}
- filterGroupOperator can be And/Or. This is mainly required when you use multiple filterLines objects
- The operator passed in filterLines can be: Equals , GreaterThan , GreaterThanOrEqual , LessThan , LessThanOrEqual , In , Contains , Range , Like , Assigned
- "IsNot": true translates to is Not Equal to and "IsNot": false translates to is Equal to
- SubFilters are used for nested 'Where' SQL statements using And/Or
See Examples:
- A simple filter
- A filter and a join
- Filter using calculated fields
- Calculated fields and a filter
- A simple subfilter
Order
"order": {
"orderFields": [
{
"order": "Ascending",
"field": "string",
"fieldAlias": "string",
"tableName": "string",
"parameters": {
"additionalProp1": "string",
"additionalProp2": "string",
"additionalProp3": "string"
}
}
]
}
- Specify the order as either Ascending or Descending in order
- Specify the field name and/or field alias on which to order. If you want to order on more than one field, specify it in seperate orderFields objects
See example:
Query tables efficiently
Overview
Allows you to query tables by passing the query in the Request body. Similar to Query Table but the results are returned in a tabular fashion leading to a smaller response size and therefore better efficiency. The query can be a simple select and can even include a join. Results can be ordered. The response is a set of records. A maximum of 1000 records can be returned. You can refer to examples provided in 'Query one or more table' above.
To get a list of all tables that can be queried, see Get a list of tables that can be queried.
Entry points
POST /api/tableaccess/{tablename}/filter/minimised
Authorisation
Use the bearer token obtained from GetBearerToken for authorisation.
Path Parameters
Enter the name of the table you wish to query in {tablename} as a string:
| Name | Mandatory | Description | Default value | Example |
|---|---|---|---|---|
| Tablename | Yes | Table name | Booking |
Resource URL
https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess
/Booking/filter/minimised
Request body
Refer to the Request body.
Example: A simple 'select' query
The following example selects fields 'booking_start' and 'booking_end' from the table 'Booking'. The query will translate as follows:
select booking_start, booking_end from booking
The Request Body will be as follows:
{
"fields": [
{
"fieldName": "booking_start"
},
{
"fieldName": "booking_end"
}
]
}
If successful, returns response code 200 and a set of records
else returns one of the following response codes:
- 400 - Bad request
- 401 - Unauthorised
- 403 - Forbidden
- 404 - Not found
Returns the following on success:
{
"columns": [
"booking_start",
"booking_end"
],
"rows": [
[
"2021-05-03T00:00:00+00:00",
"2021-07-03T00:00:00+00:00"
],
[
"2022-01-11T00:00:00+00:00",
"2022-01-13T23:59:59+00:00"
]
]
}
Examples
The following examples appear in increasing order of complexity. These examples apply to both query one or more tables and query table(s) efficiently. Reponses are provided for the first two examples only and these are applicable to query one or more tables.
A simple query
The following example selects fields 'booking_start' and 'booking_end' from the table 'Booking'. The query will translate as follows:
select booking_start, booking_end from booking
The Request Body will be as follows:
{
"fields": [
{
"fieldName": "booking_start"
},
{
"fieldName": "booking_end"
}
]
}
This will return the following response:
[
{
"booking_start": "2021-05-03T00:00:00+00:00",
"booking_end": "2021-07-03T00:00:00+00:00"
},
{
"booking_start": "2020-08-27T06:29:19.1766667+00:00",
"booking_end": "2020-08-28T06:29:19.1766667+00:00"
}
]
Retrieve all records from a table
To return all records from a table without specifying a query, enter {} in the request body and enter the name of the table as a parameter. The query will translate to:
select top 1000 * from booking
The Request body will be:
{}
A simple join
The following example translates to the following SQL statement:
select top 10 from booking
inner join resource
on booking_resource_guid = resource_guid
{
"maxRecords": 10,
"fields": [
{
"fieldName": "booking_guid"
},
{
"fieldName": "resource_description",
"tableName": "resource"
}
],
"tables": [
{
"name": "resource",
"joinType": "Inner",
"joinField": "resource_guid",
"joinToField": "booking_resource_guid"
}
]
}
Sort results in ascending order
The following example translates to the following SQL statement:
select * from booking order by booking_start asc
{
"order": {
"orderFields": [
{
"order": "Ascending",
"field": "booking_start"
}
]
}
}
A simple filter
The following examples translates into the following SQL statement:
select top 10 booking_guid from booking
where booking_resource_guid = 'f42a6a8d-1537-4a4b-bc40-1469fa62674b'
order by booking_notes desc
A maximum of 10 non-distinct records will be returned.
{
"distinct": false,
"maxRecords": 10,
"fields": [
{
"fieldName": "booking_guid",
"fieldAlias": "booking_guid"
}
],
"filter": {
"filterGroupOperator": "And",
"filterLines": [
{
"field": "booking_resource_guid",
"operator": "Equals",
"isNot": false,
"value": "f42a6a8d-1537-4a4b-bc40-1469fa62674b"
}
]
},
"order": {
"orderFields": [
{
"order": "Descending",
"field": "booking_notes"
}
]
}
}
A simple subfilter
The following examples translates into the following SQL statement:
select top 100
resource_description as name,
resource_localgrade_guid.localgrade_description as level,
resource_location_guid.location_description as location
from resource
where location = 'London'
and (level = 'senior' or resource_userstatus = 1)
order by level asc
{
"maxRecords": 100,
"fields": [
{
"fieldName": "resource_description",
"fieldAlias": "Name"
},
,
{
"fieldName": "resource_localgrade_guid.localgrade_description",
"fieldAlias": "level"
},
{
"fieldName": "resource_location_guid.location_description",
"fieldAlias": "location"
}
],
"filter": {
"filterGroupOperator": "And",
"filterLines": [
{
"field": "resource_location_guid.location_description",
"operator": "Equals",
"isNot": false,
"value": "London"
}
],
"subFilters": [
{
"filterGroupOperator": "Or",
"filterLines": [
{
"field": "resource_localgrade_guid.localgrade_description",
"operator": "Equals",
"isNot": false,
"value": "senior"
},
{
"field": "resource_userstatus",
"operator": "Equals",
"isNot": false,
"value": 1
}
]
}
]
},
"order": {
"orderFields": [
{
"order": "Ascending",
"field": "resource_localgrade_guid.localgrade_description"
}
]
}
}
Filter using calculated fields
The following example selects all resources who have more than 50% availability in March 2022. 'Availability' is a calculated fields which requires a date range as a parameter.
{
"fields": [
{
"fieldName": "resource_description"
},
{
"fieldName": "availability",
"parameters": {
"StartDate": "2022-03-01T00:00:00Z",
"EndDate": "2022-03-31T00:00:00Z"
}
}
],
"filter": {
"filterLines": [
{
"field": "availability",
"operator": "GreaterThan",
"value": 50,
"parameters": {
"StartDate": "2022-03-01T00:00:00Z",
"EndDate": "2022-03-31T00:00:00Z"
}
}
]
}
}
A filter and a join
The following example translates to the following SQL statement:
select resourcediary_resource_guid, resourcediary_diarygroup_guid
from resourcediary join resource
on resourcediary_resource_guid = resource_guid
and resourcediary_diarygroup_guid = 'AAC1D9CC-AB68-40F6-9DF3-12EEAC214C9D'
{
"fields":[
{
"fieldName":"resourcediary_resource_guid",
"fieldAlias":"resourcediary_resource_guid"
},
{
"fieldName":"resourcediary_diarygroup_guid",
"fieldAlias":"resourcediary_diarygroup_guid"
},
{
"fieldName":"resource_guid",
"fieldAlias":"resource_guid",
"tableName":"resource"
}
],
"filter":{
"filterGroupOperator":"And",
"filterLines":[
{
"field":"resourcediary_diarygroup_guid",
"operator":"Equals",
"isNot": false,
"value":"AAC1D9CC-AB68-40F6-9DF3-12EEAC214C9D"
}
]
},
"tables":[
{
"name":"resource",
"joinType":"Inner",
"joinField":"resource_guid",
"joinToField":"resourcediary_resource_guid"
}
]
}
Calculated fields and a filter
'Availability', 'resource_booking_conflict_dates' and 'Diary Group' are calculated fields. The first two take in a date range as parameters. The following example translates to the following SQL statement:
select
[availability query],
[conflict dates query],
[diary history query]
from booking
where resource_guid in (
"714fe826-2022-448b-a84e-755fc4354d9c",
"599f443c-968f-43e1-b518-dbf5b2ef8385")
{
"fields": [
{
"fieldName": "availability",
"parameters": {
"startDate": "2022-03-08T00:00:00.000Z",
"endDate": "2022-04-04T00:00:00.000Z"
}
},
{
"fieldName": "resource_booking_conflict_dates",
"parameters": {
"startDate": "2022-03-08T00:00:00.000Z",
"endDate": "2022-04-04T00:00:00.000Z"
}
},
{
"fieldName": "Diary Group"
},
{
"fieldName": "resource_guid"
}
],
"filter": {
"filterGroupOperator": "And",
"filterLines": [
{
"field": "resource_guid",
"operator": "in",
"value": [
"714fe826-2022-448b-a84e-755fc4354d9c",
"599f443c-968f-43e1-b518-dbf5b2ef8385"
]
}
]
}
}