Manage Collections
A collection resource represents a list of items, such as accounts, customers, or opportunities. You can sort, filter, and paginate your collection resources to make large result sets easier to use and consume. For instance, you might only want to select a subset of information from a customer collection resource, instead of the entire data set. You can manage data returned from a collection resource using the following actions:
Querying
To query a REST resource,
you need to know how to talk to your web server to get back the response
you want. You can use the finder
parameter supported by a resource to query records, or you can use
the q
parameter to query
and filter a collection resource.
Using the finder
Query Parameter
Use the finder
parameter when you want to find a record based on
specified criteria. The finder
parameter is a predefined query associated with a resource. Use
the supported finder names to return a record based on the criteria
specified in the finder variables. For example, use the finder MyOpportunitiesFinder
, and the
finder variable name
, to
get an opportunity with a specific name, such as "Big Data Analytics
Servers," which we've used in the following cURL command example:
curl -u username:password \
-X GET https://servername.fa.us2.oraclecloud.com/crmRestApi/resources/11.13.18.05/opportunities?finder=MyOpportunitiesFinder;Name=Big Data Analytics Servers\
-H 'content-type: application/vnd.oracle.adf.resourceitem+json'
Note:
To learn more about the supported finder names and finder variables for any resource, see theRequest
> Query Parameter
> finder
section for the resource's
Get All method located in the Tasks section of this guide.
Using the q
Query Parameter in REST Framework
Version 2 and Later
If you've
defined the REST framework as Version 2, the q
query parameter uses the expanded expression
syntax that supports RowMatch expressions
for filtering collection resources. In Version 2 and later, requests
that use query-by-example syntax that Version 1 supports return an
error.
For
example, the query expression separated by a semicolon, q=deptno>=10;loc!=NY
, returns
an error in Version 2 and later. The RowMatch expression, q= deptno>=10 or loc!=NY
,
is supported in Version 2 and later. The following are examples of
advanced query syntax supported in Version 2 and later:
-
To test whether a field's value is in a list of possibilities, use the
in
operator:DecisionLevelCode in ('DIRECTOR','VP')
-
For relational comparisons, use
between
andnot between:
-
WinProb between 80 and 100
-
WinProb not between 80 and 100
-
-
To create complex query expressions, use
and
andor
, along with the matching set of parentheses:-
(DescriptionText is not null) or ((Revenue <= 300000) and (WinProb between 80 and 100))
-
If you're using REST Framework Version 1, you can use query-by-example syntax in requests. However, note that query-by-example syntax doesn't work in Version 2 and later. To learn more about versions, see Setting the REST Framework Version.
The following
table lists REST data types and the valid operators that you can use
in query parameter strings. Note that the operators BETWEEN
, NOT BETWEEN
, IN
, NOT IN
, and the wildcard character %
are available only in REST framework
Version 2 and later.
REST Data Type | Supported Operators for q Parameter
|
---|---|
string |
|
boolean |
|
number |
|
integer |
|
Paginating
Most resource collections need some kind of pagination. Without it, a simple search could return millions of records, bringing your network to a crawl. So, instead of receiving all the records of a collection resource, you can limit the number of records that are displayed on a page in the REST client response. To do so, set the following parameters in the request payload:
-
limit
- to specify the paging size -
offset
- to specify the starting point from which the resources are returned -
totalResults
- set to true to include the total number of search records that match the query
Set the following fields in the response payload:
-
hasMore
- set to true when there more records to be returned from the collection; set to false when the last set of records are retrieved from the collection -
totalResults
- set to the total number of search records
For example, the client runs a GET
command on an opportunities resource.
The server stores 100 opportunities and the current request returns
only 25. To indicate that there are more records to retrieve, the
server sets the read-only hasMore
field to true.
Example Response Body
{
"items": [
.
.
.
],
"count": 25,
"hasMore": true,
"limit": 25,
"offset": 0,
"links": [
{
.
.
.
}
]
}
If you set the totalResults
parameter to true
in the request, the response includes the totalResults
field. After retrieving
all the records as indicated by the totalResults
value, the server sets hasMore
to false.
{
"items": [
.
.
.
],
"totalResults": 100,
"count": 25,
"hasMore": true,
"limit": 25,
"offset": 0,
"links": [
{
.
.
.
}
]
}
When you specify the limit
and offset
parameters, the paginated
result isn't ordered. To ensure consistent results, use the orderBy
parameter to order the
results. If you update the collection resource between paging requests,
the records displayed in each page may vary.
Example Requests | Records Returned |
---|---|
GET /opportunities?offset=10&limit=20 |
11 through 30 |
GET /emps?q=FirstNameLIKE ZHRX*&limit=10 |
1 through 10 |
GET /emps?q=FirstNameLIKE ZHRX*&offset=25 |
26 through 50 |
GET /Departments?totalResults=true&limit=20 |
1 through 20
Response includes the record count that match the query. |
Sorting
Sorting is another feature
that makes it easier to work with data-heavy resources. You can sort
items returned from a collection resource by using the orderBy
query parameter. To get
predictable paging results, sort the results on only unique attributes
such as OptyId
or PartyNumber
. For example, to get
to the first page of opportunities, use the command:
GET /opportunities?orderBy=OptyId
Assuming that in the response, the last
opportunity returned has OptyId=1000
, to get to the next page, structure the GET request like this:
GET /opportunities?orderBy=OptyId&q=OptyId>1000
To set the sorting sequence, use asc
for ascending order and desc
for descending order. The
default sequencing order is asc
. Items returned in the response payload are sorted in a case-sensitive
order.
For example, to sort items
according to documentId
in descending order, use the command:
?orderBy=documentId:desc
If you include multiple fields in
the query parameter, the order in which you specify the fields determines
the sorting order. For example, to sort items in ascending order of title
, and then sort according
to dateAdded
in descending
order, use the command:
?orderBy=title,dateAdded:desc
You now know how to manage your collection resources.