Implementing All OData Query/URI Options - Part 2

nitinksh1 · ‎03-26-2023

Introduction

In the previous blog, we have discussed about the URI calls which do not require any custom implementation. In this blog, we will go through the URI's which do require custom implementation.

Steps

We can divide OData URI to 2 parts:

Do Not Need Custom Implementation (Implementing All OData Query/URI Options – Part 1)
1. $select
2. $count
3. $expand
4. $format
5. $links
6. $value

Need Custom Implementation (This Blog)
1. $orderby
2. $top
3. $skip
4. $filter
5. $inlinecount
6. $skiptoken

Implementation

1. $orderby: The $orderby option is used to specify a sort order for the results of a query. It is used in the URL of an OData service to indicate how the results should be sorted.

The syntax for the $orderby option is as follows: $orderby=propertyName [asc|desc], where propertyName is the name of the property by which the results should be sorted, and "asc" or "desc" is used to specify the sort order (ascending or descending, respectively).

For example, if we want to retrieve a list of customers and sort them by last name in descending order, the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$orderby=LastName desc

It is also possible to sort by multiple properties using the $orderby option, by separating each property with a comma.

http://<host>/sap/opu/odata/sap/<service>/Customers?$orderby=LastName desc, FirstName asc

The code the $orderby can be written as:

  METHOD ekkoset_get_entityset.



*- To get data from DB

    SELECT * FROM ekko INTO TABLE @DATA(lt_ekko) UP TO 10 ROWS.



*- Check the $orderby in the Odata Query

    READ TABLE it_order INTO DATA(ls_order) INDEX 1.

    IF sy-subrc IS INITIAL.

      IF ls_order-order = 'desc'.

        SORT lt_ekko BY (ls_order-property) DESCENDING.

      ELSE.

        SORT lt_ekko BY (ls_order-property) ASCENDING.

      ENDIF.

    ENDIF.



*- Check the size of the table for $inlinecount

    IF io_tech_request_context->has_inlinecount( ) = abap_true.

      DESCRIBE TABLE lt_ekko LINES DATA(lv_size).

      es_response_context-inlinecount = lv_size.

    ENDIF.



    MOVE-CORRESPONDING lt_ekko TO et_entityset.



  ENDMETHOD.

Below is the output:

1. $orderby.jpg

Another better way to use the $orderby can be the standard way of using it, displayed below:

*- Check the $orderby in the Odata Query

    /iwbep/cl_mgw_data_util=>orderby(

      EXPORTING

        it_order =  it_order                " the sorting order

      CHANGING

        ct_data  = lt_ekko

    ).

2. $top: The $top option is used to specify the maximum number of results that should be returned in a query. It is used in the URL of an OData service to indicate how many results should be returned. The syntax for the $top option is as follows: $top=n, where n is an integer indicating the number of results that should be returned.

For example, if we want to retrieve the top 10 customers, the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$top=10

The $top option can be used in combination with other options such as $filter, $select and $orderby to further refine the results of a query.

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=Country eq 'US'&$top=5&$orderby=LastName desc

It's important to note that the $top option is used to limit the number of records returned in one response and not to restrict the total number of records returned by the OData service.

  METHOD ekkoset_get_entityset.



*- To get data from DB

    SELECT * FROM ekko INTO TABLE @DATA(lt_ekko) UP TO 10 ROWS.



*- Check the $orderby in the Odata Query

    /iwbep/cl_mgw_data_util=>orderby(

      EXPORTING

        it_order =  it_order                " the sorting order

      CHANGING

        ct_data  = lt_ekko

    ).



*- For paging i.e. $top and $skip

    /iwbep/cl_mgw_data_util=>paging(

      EXPORTING

        is_paging = is_paging                " paging structure

      CHANGING

        ct_data   = lt_ekko

    ).



*- Check the size of the table for $inlinecount

    IF io_tech_request_context->has_inlinecount( ) = abap_true.

      DESCRIBE TABLE lt_ekko LINES DATA(lv_size).

      es_response_context-inlinecount = lv_size.

    ENDIF.



    MOVE-CORRESPONDING lt_ekko TO et_entityset.



  ENDMETHOD.

We can use it as below:

2. $top.jpg

3. $skip: The $skip option is used to specify the number of results that should be skipped before returning the results in a query. It is used in the URL of an OData service to indicate how many results should be skipped. The syntax for the $skip option is as follows: $skip=n, where n is an integer indicating the number of results that should be skipped.

For example, if we want to retrieve all customers but skip the first 10, the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$skip=10

The $skip option can be used in combination with other options such as $top, $filter, $select, and $orderby to further refine the results of a query.

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=Country eq 'US'&$skip=5&$top=5&$orderby=LastName desc

It's important to note that the $skip option is used to skip the records from the beginning of the result set, so it is typically used in combination with the $top option to retrieve a specific page of results.

Same code of paging(used in 3. $top). The below snippet is showing the second 2nd record as it is fetching the 1st record after skipping 1st one.

3. $skip.jpg

4. $filter: The $filter option is used to specify filter criteria for the results of a query. It is used in the URL of an OData service to indicate which results should be returned based on certain conditions. The syntax for the $filter option is as follows: $filter=condition, where the condition is a logical expression that evaluates to true or false.

The condition can be built using comparison and logical operators (eq, ne, gt, ge, lt, le, and, or, not).

For example, if we want to retrieve all customers whose last name starts with "S", the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=startswith(LastName,'S')

If we want to retrieve all customers whose last name starts with "S" and whose first name is "John", the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=startswith(LastName,'S') and FirstName eq 'John'

The $filter option can be used in combination with other options such as $top, $skip, $select, and $orderby to further refine the results of a query.

  METHOD ekkoset_get_entityset.



*- To get data from DB

*- it_filter_select_options will have filter values for $filter keyword

    IF it_filter_select_options IS INITIAL.

      SELECT * FROM ekko INTO TABLE @DATA(lt_ekko) UP TO 10 ROWS.

    ELSE.

      READ TABLE it_filter_select_options INTO DATA(ls_sopt)

                                           WITH KEY property = 'Ebeln'.

      IF sy-subrc IS INITIAL.

        SELECT * FROM ekko INTO TABLE @lt_ekko WHERE ebeln IN @ls_sopt-select_options.

      ENDIF.

    ENDIF.



*- Check the $orderby in the Odata Query

  /iwbep/cl_mgw_data_util=>orderby(

    EXPORTING

      it_order =  it_order                " the sorting order

    CHANGING

      ct_data  = lt_ekko

  ).



*- For paging i.e. $top and $skip

  /iwbep/cl_mgw_data_util=>paging(

    EXPORTING

      is_paging = is_paging                " paging structure

    CHANGING

      ct_data   = lt_ekko

  ).



*- Check the size of the table for $inlinecount

  IF io_tech_request_context->has_inlinecount( ) = abap_true.

    DESCRIBE TABLE lt_ekko LINES DATA(lv_size).

    es_response_context-inlinecount = lv_size.

  ENDIF.



  MOVE-CORRESPONDING lt_ekko TO et_entityset.



ENDMETHOD.

We can test the above code as:

4. $filter.jpg

5. $inlinecount: It is used to include the total count of all the matching results in the response. It is used in the URL of an OData service to indicate that the total count of the matching results should be included in the response. The syntax for the $inlinecount option is as follows: $inlinecount=allpages, where "allpages" is a keyword indicating that the total count should be included.

For example, if we want to retrieve all customers and include the total count of customers in the response, the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$inlinecount=allpages

The $inlinecount option can be used in combination with other options such as $filter, $top, $skip, $select and $orderby to further refine the results of a query.

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=Country eq 'US'&$top=5&$skip=5&$orderby=LastName desc&$inlinecount=allpages

It's important to note that the $inlinecount option will include the count of the matching results in the response, the count will be included in the ".count" attribute of the response.

5. $filter.jpg

6. $skiptoken: The $skiptoken option is used with the $top option to retrieve a specific page of results by providing a token that represents the position in the result set from where the next set of results should be retrieved. When a partial response is returned by the server, the $skiptoken value is included in the __next link of the response, allowing clients to easily retrieve the next set of results without having to construct or interpret the $skiptoken value themselves.

The syntax for the $skiptoken option is as follows: $skiptoken=token, where the token is a string value representing the position in the result set from where the next set of results should be retrieved.

For example, if we want to retrieve the next set of 10 customers starting from the 11th customer, the URL would look like this:

http://<host>/sap/opu/odata/sap/<service>/Customers?$top=10&$skiptoken='11'

The $skiptoken option can be used in combination with other options such as $filter, $top, $select and $orderby to further refine the results of a query.

http://<host>/sap/opu/odata/sap/<service>/Customers?$filter=Country eq 'US'&$top=5&$skiptoken='11'&$orderby=LastName desc

It's important to note that the $skiptoken option is used to navigate through the result set in a pagination manner, and it should be used in conjunction with $top option and the result set should be sorted in the same order as in the query that generated the skiptoken.

6. $skiptoken.jpg

The $skip and $skiptoken looks same in first look but they are some difference. To know further about $skip and $skiptoken you can visit this blog: Difference between $skip and $skiptoken in Odata

Conclusion

SAP has provided us multiple options to play with for real-time requirements with a little effort. I would recommend you to try these query options and debug to see how it works. The blog was divided into the following parts.

$orderby: sorts data by one or more fields.

$top and $skip: enable server-side paging.

$filter: retrieves specific data from an entity set based on a set of criteria.

$inlinecount: provides the total number of records in an entity set.

$skiptoken: enables server-side paging using a continuation token.

Edit 1: Thanks @gregorw for helping to enrich the blog.

In the world of Odata services, there are two types of services -

Those created from SEGW

Those based on CDS views

When creating Odata services from SEGW, custom implementation is required for the $orderby, $top, $skip, $filter, and $inlinecount query options. However, this is not the case when the Odata service is based on a CDS view. In this scenario, SAP provides these query options without the need for implementation, making the development process simpler and more efficient.

It’s important to note that this difference exists because of the fundamental architectural differences between SEGW and CDS views. SEGW generates ABAP code based on metadata, whereas CDS views are defined in the ABAP repository and translated into Open SQL. By leveraging the capabilities of CDS views, developers can benefit from SAP’s pre-built query options and focus on delivering value to their customers.

If you have any thoughts or questions on the topic, please feel free to leave a comment below. I would love to hear from you.

If you found this post helpful, please like and share it with your network 🙂

Kind Regards,

Nitin Sharma