Implementing Query Options

Objectives

After completing this lesson, you will be able to:

Implement query options
Implement a paging functionality

Query Options Implementation

OData query options support in SAP Gateway framework

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:

Code Snippet

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:

Code Snippet

123
SELECT 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

Source code implementing a filter operation

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:

Code Snippet

1234567891011121314151617181920212223242526272829303132333435363738394041424344
METHOD 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.

Implement Filter Functionality

Business Example

You are a developer or solution architect in your company. You need to implement filter functionality for an entity set.

Template: GW100_S_NAVIGATION (SAP Gateway Project)
Solution: GW100_S_FILTER (SAP Gateway Project)

Note

This exercise requires an SAP Learning system. Login information are provided by your system setup guide.

Note

You may continue with your solution of the previous exercise or copy the template to ZGW100_##_FILTER. Whenever the values or object names in this exercise include ##, replace ## with the number of your user.

Prerequisites

The query operation for business partner was implemented in exercise Add an Additional Entity to a Data Model.

Task 1: Implement and Test a Filter

ExerciseStart Exercise

Steps

In the SAP Gateway Service Builder of your SAP S/4HANA (S4H) system, adapt method BUSINESSPARTNERS_GET_ENTITYSET to filter business partners by company name using the selection parameter of the BAPI_EPM_BP_GET_LIST function module.

In the SAP Easy Access of your S4H, search for SAP Gateway Service Builder or start transaction SEGW.
In the SAP Gateway Service Builder, expand the Service Implementation → BusinessPartnerSet node.
In the context menu of GetEntitySet (Query), choose Go to ABAP Workbench.
In the Class Builder, choose Display <-> Change.

Add the following code or copy it from the solution:

Code Snippet

1234567891011121314151617181920212223
METHOD 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
      
      return              = lt_return.
...
ENDMETHOD.

Choose Activate.
In the Inactive Objects popup, select all objects and choose Continue.
Choose Exit.

In the SAP Gateway Client of your S4H, test the filter functionality in your service. Query the business partners that have a company name starting with "S".
1. In the SAP Gateway Service Builder of your S4H, double-click Service Maintenance.
2. Choose SAP Gateway Client.
3. In the SAP Gateway Client, select HTTPS as Protocol.
4. Choose Entity Set.
5. In the Entity Sets popup, double-click BusinessPartnerSet.
6. In the Request URI field, add ?$filter=startswith(CompanyName,'S') to the URI.
  Example
  /sap/opu/odata/SAP/ZGW100_##_STUDENT_SRV/BusinessPartnerSet?$filter=startswith(CompanyName,'S')
7. Choose Execute.
  Result
  The HTTP Response displays business partners starting with "S" .

Task 2: Document Filter as Operational in Metadata

ExerciseStart Exercise

Steps

In the SAP Gateway Service Builder of your S4H, set the CompanyName property of the BusinessPartner entity type to be Filterable.
1. In the SAP Gateway Service Builder, expand Data Model → Entity Types → BusinessPartner.
2. Double-click Properties.
3. For the CompanyName, set the Filterable checkbox.
4. Choose Generate Runtime Objects.

Implementation of a Paging Functionality

Example: Paging request and result of first page

Limiting the result set to only the first 6 entries is achieved in OData by using the statement $top=6.

The number of entries that the list contains in total is important. In the case of client side paging, this value is retrieved by using the statement $inlinecount=allpages. Together with the result set the number of all values is provided to the client.

Example: Paging request and result of second page

When navigating to the second page we must leverage the $skip parameter. Again, the client retrieves only 6 items ($top) but skips the first 6 and starts with business partner number 7.

If instead of $inlinecount, $count is used, you would have to perform two HTTP calls and therefore two server round-trips. Using $inlinecount allows you to retrieve the total number of entries in one single HTTP call.

Example: Paging request and result of third page

Navigating to the third page is achieved in our example by adding the number of $top (6) to $skip (6) resulting in 12. This continues for all following pages. If there would be a scroll bar instead of paging arrows, $skip could have any number.

In the case of client side paging, where the client controls how many values are retrieved, there can be issues when the client tries to retrieve a large amount of data from the BES.

To shield the server from such unintentional "Denial of Service attacks", you can use server side paging. With server side paging, you configure the server such that only a limited number of rows are returned. There generally isn’t any conflict for the usage of client based and server based paging.

To retrieve values for $top and $skip, call the methods get_top() and get_skip() of the io_tech_request_context parameter. The /iwbep/s_mgw_paging type consists of two integer components to save the paging information.

Function modules usually do not offer a parameter to provide skip-functionality. This originates from the databases, because many did not offer a skip functionality in the past. Instead, the parameter max_rows is used quite often to limit the number of data sets. To make use of this parameter, you should add $top and $skip, pass it to max_rows, and delete the skipped and therefore unnecessary data sets after the function module call.

Hint

SAP HANA supports the skip-functionality via the SQL addition OFFSET.

The following block provides the example source code in a copy-friendly way:

Code Snippet

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
METHOD businesspartners_get_entityset.
  DATA: lt_headerdata TYPE TABLE OF bapi_epm_bp_header,
        lt_return     TYPE TABLE OF bapiret2.

  DATA: ls_max_rows TYPE bapi_epm_max_rows,
        ls_paging   TYPE /iwbep/s_mgw_paging.

* Get paging
  ls_paging = VALUE #( top  = io_tech_request_context->get_top( )
                       skip = io_tech_request_context->get_skip( ) ).
  IF ls_paging-top > 0.
    ls_max_rows-bapimaxrow = ls_paging-top + ls_paging-skip.
  ENDIF.

* Get data
  CALL FUNCTION 'BAPI_EPM_BP_GET_LIST'
    EXPORTING
      max_rows            = ls_max_rows
    TABLES
      bpheaderdata        = lt_headerdata
      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.

* Delete skipped response data
  IF ls_paging-skip IS NOT INITIAL.
    DELETE lt_headerdata TO ls_paging-skip.
  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.

Implement Paging Functionality

Business Example

You are a developer or solution architect in your company. You need to implement paging functionality for an entity set.

Template:: GW100_S_FILTER (SAP Gateway Project)
Solution:: GW100_S_PAGING (SAP Gateway Project)

Note

This exercise requires an SAP Learning system. Login information are provided by your system setup guide.

Note

You may continue with your solution of the previous exercise or copy the template to ZGW100_##_PAGING. Whenever the values or object names in this exercise include ##, replace ## with the number of your user.

Prerequisites

The filter functionality for business partner was implemented in exercise Implement Filter Functionality.

Task 1: Implement and Test Paging

ExerciseStart Exercise

Steps

In the SAP Gateway Service Builder of your SAP S/4HANA (S4H) system, adapt method BUSINESSPARTNERS_GET_ENTITYSET to implement paging functionality for business partners using the maximum rows parameter of the BAPI_EPM_BP_GET_LIST function module.

From the SAP Easy Access screen on your S4H, open the SAP Gateway Service Builder by using transaction code SEGW.
In the SAP Gateway Service Builder, in the context menu of GetEntitySet (Query), choose Go to ABAP Workbench.
In the Class Builder, choose Display <-> Change.

Add the following code or copy it from the solution:

Code Snippet

1234567891011121314151617181920212223242526272829303132333435
METHOD businesspartners_get_entityset.
...
  DATA: lt_filters    TYPE /iwbep/t_mgw_select_option,
        ls_filter     TYPE /iwbep/s_mgw_select_option,
        lt_so_company TYPE TABLE OF bapi_epm_company_name_range.

  DATA: ls_max_rows TYPE bapi_epm_max_rows,
        ls_paging   TYPE /iwbep/s_mgw_paging.

* Get paging
  ls_paging = VALUE #( top  = io_tech_request_context->get_top( )
                       skip = io_tech_request_context->get_skip( ) ).
  IF ls_paging-top > 0.
    ls_max_rows-bapimaxrow = ls_paging-top + ls_paging-skip.
  ENDIF.

...

* Get data
  CALL FUNCTION 'BAPI_EPM_BP_GET_LIST'
    EXPORTING
      max_rows            = ls_max_rows
    TABLES
      bpheaderdata        = lt_headerdata
      selparamcompanyname = lt_so_company
      return              = lt_return.

...

* Delete skipped response data
  IF ls_paging-skip IS NOT INITIAL.
    DELETE lt_headerdata TO ls_paging-skip.
  ENDIF.
...
ENDMETHOD.

Choose Activate.
In the Inactive Objects popup, select all objects and choose Continue.
Choose Exit.

In the SAP Gateway Client of your S4H, test the paging functionality in your service. Query three business partners skipping the first two ones.
1. In the SAP Gateway Service Builder of your S4H, double-click Service Maintenance.
2. Choose SAP Gateway Client.
3. In the SAP Gateway Client, select HTTPS as Protocol.
4. Choose Entity Set.
5. In the Entity Sets popup, double-click BusinessPartnerSet.
6. In the Request URI field, add ?$top=3 to the URI.
  Example
  /sap/opu/odata/SAP/ZGW100_##_STUDENT_SRV/BusinessPartnerSet?$top=3
7. Choose Execute.
  Result
  The HTTP Response displays the first three business partners.
8. Memorize the name of the last business partner.
9. In the Request URI field, add &$skip=2 to the URI.
  Example
  /sap/opu/odata/SAP/ZGW100_##_STUDENT_SRV/BusinessPartnerSet?$top=3&$skip=2
10. Choose Execute.
  Result
  The HTTP Response displays the next three business partners skipping the first two ones. The first business partner is the one you memorized.

Task 2: Document Paging as Operational in Metadata

ExerciseStart Exercise

Steps

In the SAP Gateway Service Builder, set the BusinessPartnerSet entity set to be Pageable.
1. In the SAP Gateway Service Builder, expand Data Model.
2. Double-click Entity Sets.
3. For of the BusinessPartnerSet, set the Pageable checkbox.
4. Choose Generate Runtime Objects.

Next lesson

Implementing Query Options

Query Options Implementation

Filtering and Projecting

Implement Filter Functionality

Business Example

Prerequisites

Task 1: Implement and Test a Filter

Steps

Example

Result

Task 2: Document Filter as Operational in Metadata

Steps

Implementation of a Paging Functionality

Implement Paging Functionality

Business Example

Prerequisites

Task 1: Implement and Test Paging

Steps

Example

Result

Example

Result

Task 2: Document Paging as Operational in Metadata

Steps