Implementing Query Operations
Query Options Implementation
The $select option can be used to limit the number of fields that are returned by an entity set. It is supported by the framework. When also limiting the retrieval of data in the data provider class to those fields that are actually requested, the performance can be enhanced. Client side paging as well as server side has to be implemented by the developer. The performance of client side paging can be enhanced if using the softstate based query result cache (SQRC).
When retrieving hierarchal data using $expand, the performance can be optimized if a custom-developed GET_EXPANDED_ENTITY or GET_EXPANDED_ENTITYSET is used (that is, a self expand) instead of relying on the default functionality supported by the framework.
OData Query Options
|
Operation |
OData query option |
Supported by the framework |
Needs to be implemented for the service |
Enhancement possible |
|
Filtering |
$filter |
N/A |
● |
N/A |
|
Projecting |
$select |
● |
N/A |
yes |
|
Paging |
$top |
N/A |
● |
SQRC |
|
N/A |
$skip |
N/A |
● |
SQRC |
|
N/A |
$inlinecount |
N/A |
● |
N/A |
|
Sorting |
$orderby |
N/A |
● |
N/A |
|
Inlining |
$expand |
● |
N/A |
Self expand |
|
Count |
$count |
● |
N/A |
N/A |
We will start with the query options $filter and $select.
$filter and $select
With $filter, you can apply several options to filter a result set. A filter can, in principle, be applied on all properties of an entity set (these should be marked as sap:filterable).
For the business partner you could for example filter on the name or the business partner ID. You could also perform a wild card search.
In our example, we want to limit the result set for a consumer application by limiting the number of rows and columns that are retrieved. Our queries are as follows:
- Get all business partners whose company name starts with the letter ‘S’
- Get all sales orders with a total sum larger than €10,000
- Only retrieve the business partner ID and the name of a business partner
Query Options: $filter & $select
The $filter statement can be compared with the where clause in the usual SQL syntax. The $select statement is basically the same as the SELECT keyword in normal SQL.
The result of a GET HTTP method with the request URI: …/BusinessPartners?$filter=startswith(CompanyName,'S‘)&$select=BusinessPartnerID,CompanyName is as follows:
{
"BusinessPartnerID": "0100000000",
"CompanyName": "SAP„
},
{
"BusinessPartnerID": "0100000041",
"CompanyName": "South American IT Company“
},
{
"BusinessPartnerID": "0100000042",
"CompanyName": "Siwusha„
},
{
"BusinessPartnerID": "0100000044",
"CompanyName": "Sorali„
SQL Statement Comparable to $filter and $select Options
The following code shows a SQL statement comparable to $filter and $select query options:
SELECT BusinessPartnerID CompanyName