All OData query options can be implemented in the methods of the data provider class except $format, which is always handled by the SAP Gateway framework.
The $select option to limit the number of fields that are returned by an entity set 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.
Paging must be implemented by the developer. Beside that, soft state based query result cache (SQRC) enhances the performance for suitable scenarios through caching.
When retrieving hierarchical 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.
Filtering and Projecting
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’
- Only retrieve the business partner ID and the name of a business partner
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).
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 request with the URI: …/BusinessPartners?format=json&$filter=startswith(CompanyName,'S‘)&$select=BusinessPartnerID,CompanyName is as follows:
12345678910111213141516{
"BusinessPartnerID": "0100000000",
"CompanyName": "SAP"
},
{
"BusinessPartnerID": "0100000041",
"CompanyName": "South American IT Company"
},
{
"BusinessPartnerID": "0100000042",
"CompanyName": "Siwusha"
},
{
"BusinessPartnerID": "0100000043",
"CompanyName": "Sorali"
}The following code shows an SQL statement comparable to $filter and $select query options:
123SELECT BusinessPartnerID CompanyName
FROM BusinessPartners
WHERE CompanyName LIKE 'S%'The benefits of using $select are as follows:
- Less data is transferred
- Depending on the data structure in the BES, less data might have to be consumed. For example, if the data is stored in a view comprising multiple database tables.
The benefits of using $filter are as follows:
- Less data is transferred
- Less data is retrieved from the BES
This code implements the support to filter products by their category if the query option ?$filter=Category eq '<your category>' has been added to the request URI.
Again, the io_tech_request_context parameter provides the OData request header. The get_filter() method provides an object, which allows to get the filter as a select option or range table by calling the method get_filter_select_options(). The suitable type to save the return parameter is /iwbep/t_mgw_select_option.
Hint
The/iwbep/cl_mgw_data_util class provides methods to ease filtering, ordering, and paging.Most filters originating from input fields can be transformed in select options. But OData also allows arithmetic operations or date functions in a filter-statement, which cannot be transformed. In such a case, the developer must parse the filter-statement provided as string by the get_filter_string() method.
The following block provides the example source code in a copy-friendly way:
1234567891011121314151617181920212223242526272829303132333435363738394041424344METHOD businesspartners_get_entityset.
DATA: lt_headerdata TYPE TABLE OF bapi_epm_bp_header,
lt_return TYPE TABLE OF bapiret2.
DATA: lt_filters TYPE /iwbep/t_mgw_select_option,
lt_so_company TYPE TABLE OF bapi_epm_company_name_range.
* Get filter
lt_filters = io_tech_request_context->get_filter( )
->get_filter_select_options( ).
IF line_exists( lt_filters[ property = 'COMPANYNAME' ] ).
lt_so_company = CORRESPONDING #( lt_filters
[ property = 'COMPANYNAME' ]-select_options ).
ENDIF.
* Get data
CALL FUNCTION 'BAPI_EPM_BP_GET_LIST'
TABLES
bpheaderdata = lt_headerdata
selparamcompanyname = lt_so_company
return = lt_return.
IF lt_return IS NOT INITIAL.
"Message Container
mo_context->get_message_container( )
->add_messages_from_bapi( lt_return ).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = mo_context->get_message_container( ).
ENDIF.
* Map properties from the back-end to output response structure
et_entityset = VALUE #( FOR header IN lt_headerdata
( businesspartnerid = header-bp_id
businesspartnerrole = header-bp_role
emailaddress = header-email_address
companyname = header-company_name
currencycode = header-currency_code
city = header-city
street = header-street
country = header-country
addresstype = header-address_type ) ).
ENDMETHOD.
