$orderby Query Option in SAP OData Service

0
23953

Hello everyone, in this tutorial we will learn how to use query option $orderby in SAP netweaver gateway OData service.

Before proceeding further we assume that you know how to build OData service in sap gateway. Access all SAP Netweaver Gateway tutorials here.

Lets get started

What $orderby query option does?

Query option $orderby adds sorting capabilities to the SAP gateway OData service. So that you can able to order the OData service feed/collection based on the fields available in the Entity Type set.

Supported System Version

SAP NetWeaver Gateway Release 2.0 Support Package >=03

Business Example

A gateway OData service is getting all the products from the back-end system, but you don’t want to see the products in the order they retrieved and you want to apply orderby on the OData service so that you get required products in required order.

For example you want to order the all products by its “Price” in descending.

Syntax

http://<server>:<port>/sap/opu/odata/sap/<service_name>/ProductsSet?$orderby=Price desc

$orderby = <fieldname> <sortorder> , where:

  • <fieldname> is the name of the field.
  • <sortorder> desc/asc

How to Implement $orderby query option in OData Service

In this section we will adjust the OData service to respond to $orderby query option. $orderby query option should be applied only to Entity Type sets.

We assume that you have already build the OData service to get the list of products. if you have not click here.

Step-By-Step Procedure

1. After successful creation of OData service. Go back to service builder SEGW, Navigate to the ABAP workbench for the Product Entity set.

Go to ABAP Workbench

2. Code inside the method PRODUCTSSET_GET_ENTITYSET will look like below i.e before adjusting the service to $orderby query option.

DATA: lt_products  TYPE STANDARD TABLE OF bapi_epm_product_header,
      ls_products  TYPE bapi_epm_product_header,
      es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
      lv_max_rows  TYPE bapi_epm_max_rows.

    lv_max_rows-bapimaxrow = 10.
    CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
      EXPORTING
        max_rows   = lv_max_rows
      TABLES
        headerdata = lt_products.
    IF lt_products IS NOT INITIAL.
      LOOP AT lt_products INTO ls_products.
        MOVE-CORRESPONDING ls_products TO es_entityset.
        APPEND es_entityset TO et_entityset.
      ENDLOOP.
    ENDIF.

3. In the above code snippet, we have retrieved the list of products generally by providing the max rows.

Now we will adjust the code to adapt it to the $orderby query option.

Orderby query option is available in the method by accessing the importing parameter IT_ORDER.

4. Put the below code in the method PRODUCTSSET_GET_ENTITYSET to respond to the $orderby query option.

    DATA:  ls_order     TYPE /iwbep/s_mgw_sorting_order,
           lt_products  TYPE STANDARD TABLE OF bapi_epm_product_header,
           ls_products  TYPE bapi_epm_product_header,
           es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
           lv_max_rows  TYPE bapi_epm_max_rows.

    lv_max_rows-bapimaxrow = 10.
    CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
      EXPORTING
        max_rows   = lv_max_rows
      TABLES
        headerdata = lt_products.
    IF lt_products IS NOT INITIAL.
      LOOP AT lt_products INTO ls_products.
        MOVE-CORRESPONDING ls_products TO es_entityset.
        APPEND es_entityset TO et_entityset.
      ENDLOOP.
    ENDIF..

* Get the Order by field name
    READ TABLE it_order INTO ls_order WITH KEY property = 'Price'.
    IF sy-subrc = 0.
      CASE ls_order-order.
        WHEN 'asc'.
          SORT et_entityset BY price ASCENDING.
        WHEN 'desc'.
          SORT et_entityset BY price DESCENDING.
        WHEN OTHERS.
      ENDCASE.
    ENDIF.

5. What we did in the above step. First we got all the products and get orderby “fieldname”, “sortorder” and pass apply them on to the BAPI result data to get the required sort order of the data.

6. From code perspective we are ready, so lets test the service by using the sap gateway client /IWFND/GW_CLIENT.

7. If we execute the service without adding $orderby query option, then we will get all the products available in the system.

If we add the $orderby query option to the service, our service will respond to that and it will give the results based on the sort order provided.

Test the service by providing the following URI with $orderby=Price desc.

/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/ProductsSet?$orderby=Price desc’.

8. Check the collection/feed, now you should get the products in descending order of the Price .

You can add N number fields with $orderby query option.

Final Output

Stay tuned to us for more SAP Netweaver Gateway tutorials. Please feel free to comment and let us know your feedback.

In step-4 i statically declaring the sort field name and order, you can also do it more dynamically in this way. Replace the code in step-4 with the below code.

DATA:  ls_order     TYPE /iwbep/s_mgw_sorting_order,
       lt_products  TYPE STANDARD TABLE OF bapi_epm_product_header,
       ls_products  TYPE bapi_epm_product_header,
       es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
       lv_max_rows  TYPE bapi_epm_max_rows.

    lv_max_rows-bapimaxrow = 10.
    CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
      EXPORTING
        max_rows   = lv_max_rows
      TABLES
        headerdata = lt_products.
    IF lt_products IS NOT INITIAL.
      LOOP AT lt_products INTO ls_products.
        MOVE-CORRESPONDING ls_products TO es_entityset.
        APPEND es_entityset TO et_entityset.
      ENDLOOP.
    ENDIF..

* Get the Order by field name
    DATA: lt_tech_order TYPE /iwbep/t_mgw_tech_order,
          ls_tech_order TYPE /iwbep/s_mgw_tech_order,
          otab          TYPE abap_sortorder_tab,
          oline         TYPE abap_sortorder.

    lt_tech_order = io_tech_request_context->get_orderby( ).
    LOOP AT lt_tech_order INTO ls_tech_order.
      oline-name = ls_tech_order-property.
      IF ls_tech_order-order = 'desc'.
        oline-descending = 'X'.
      ELSE.
        oline-descending = space.
      ENDIF.
      APPEND oline TO otab.
    ENDLOOP.

    SORT et_entityset BY (otab).

Congrats!! you have successfully learned how to implement $orderby query option in SAP OData Service.

Check out our other SAP OData Tutorials here.

Please feel free to comment and let us know your feedback. Subscribe for more updates

If you liked it, please share it! Thanks!