Gateway OData service – how to implement generic filtering ($filter), sorting ($orderby) and paging ($top and $skip)

Hi Experts.

I have always found it annoying that SAP has not delivered standard functionality to perform generic filtering, sorting and paging in Query (GetEntitySet) methods of the Data Provider Class when building custom OData services in SAP Gateway.

In this Blog Post I will provide generic code that can be applied to any Query (GetEntitySet) method, so that one can easily support these query options, without having to code them explicitly for each EntitySet.

However, this code will be added to the end of your existing code, and especially for filtering options this may be very inefficient, but for small sets or in situations where you will have to extract all data and do filtering afterwards anyway, this approach may be ok.

The code snippets will be added at the end of this Blog Post.

In the Data Provider Extension class I have added three new methods, one for each of the three query options: Sorting, Filtering and Paging.

They can now be called generically in any Query (…_GET_ENTITYSET) method.

They must be called after ET_ENTITYSET have been filled with the gross list of data.

Filtering

Extract of the generic filtering method.

Filtering example

We have entityset UserSet, and filter on property LastName for an equal match on ‘Hansen’.

The internal table IT_FILTER_SELECT_OPTIONS (of the …_GET_ENTITYSET method) contains the Property name and a range table with the search criterias.

The result table ET_ENTITYSET (ofcourse) has ABAP names and not the Service Property name.

That’s why we need the above coding to extract the service model (metadata), to know which ABAP field in the internal table to evaluate.

After this we loop through all filter options, map the property name of the filter option to the corresponding ABAP name, and evaluate the select options against every entry in the input table, while deleting those entries that does not suffice the conditions.

Ordering

Extract of the generic ordering method. The idea is the same as for filtering.

Utility method to raise an exception.

For Ordering and Filtering operations, exceptions must be handled, so we use utility method RAISE_EXCEPTION_FROM_MESSAGE for easy exception propagation.

Paging

For Paging the implementation is very simple.

I hope you find this useful, it has made it possible for me to always support these query options for simple datasets in a very simple way.

Søren Hansen, Senior Mobile Consultant, 2BM A/S, Denmark

Appendix (Code snippets):

class ZCL_ZMY_SERVICE_DPC_EXT definition.

protected section.

  methods RAISE_EXCEPTION_FROM_MESSAGE
    importing
      !IV_MESSAGE type BAPI_MSG
    raising
      /IWBEP/CX_MGW_BUSI_EXCEPTION .
  methods ENTITYSET_ORDER
    importing
      !IT_ORDER type /IWBEP/T_MGW_SORTING_ORDER
      !IV_ENTITY_NAME type STRING
    changing
      !CT_ENTITYSET type TABLE
    raising
      /IWBEP/CX_MGW_BUSI_EXCEPTION .
  methods ENTITYSET_FILTER
    importing
      !IT_FILTER_SELECT_OPTIONS type /IWBEP/T_MGW_SELECT_OPTION
      !IV_ENTITY_NAME type STRING
    changing
      !CT_ENTITYSET type TABLE
    raising
      /IWBEP/CX_MGW_BUSI_EXCEPTION .
  methods ENTITYSET_PAGING
    importing
      !IS_PAGING type /IWBEP/S_MGW_PAGING
    changing
      !CT_ENTITYSET type TABLE
    raising
      /IWBEP/CX_MGW_BUSI_EXCEPTION .


endclass.


class ZCL_ZMY_SERVICE_DPC_EXT implementation.

method RAISE_EXCEPTION_FROM_MESSAGE.

* Raise business exception with supplied message.
  RAISE EXCEPTION TYPE /IWBEP/CX_MGW_BUSI_EXCEPTION
  EXPORTING
    TEXTID  = /IWBEP/CX_MGW_BUSI_EXCEPTION=>BUSINESS_ERROR
    MESSAGE = IV_MESSAGE.

endmethod.


method ENTITYSET_FILTER.

* Generic method to filter any entityset table of the corresponding Model.

  data:
  lx_root         type ref to CX_ROOT,
  lo_data_descr   type ref to CL_ABAP_DATADESCR,
  lo_table_descr  type ref to CL_ABAP_TABLEDESCR,
  lo_dp_facade    type ref to /IWBEP/CL_MGW_DP_FACADE,  "/IWBEP/IF_MGW_DP_FACADE,
  lo_model        type ref to /IWBEP/IF_MGW_ODATA_RE_MODEL,
  ls_entity_props type /IWBEP/IF_MGW_ODATA_RE_PROP=>TY_S_MGW_ODATA_PROPERTY,
  lt_entity_props type /IWBEP/IF_MGW_ODATA_RE_PROP=>TY_T_MGW_ODATA_PROPERTIES,
  ls_filter_sel   type /IWBEP/S_MGW_SELECT_OPTION,
  lv_entity_name  type /IWBEP/MED_EXTERNAL_NAME,
  lv_tabix        type I,
  lv_type         type STRING.

  field-symbols:
  <fs_val>   type DATA,
  <fs_data>  type DATA.

* Pre-check.
  check lines( IT_FILTER_SELECT_OPTIONS ) > 0.

* 'Type-cast' datatype.
  lv_entity_name = IV_ENTITY_NAME.

* Get type of table.
  TRY.
*   Get DP facade.
    lo_dp_facade ?= me->/IWBEP/IF_MGW_CONV_SRV_RUNTIME~GET_DP_FACADE( ).
*   Get Model
    lo_model = lo_dp_facade->/IWBEP/IF_MGW_DP_INT_FACADE~GET_MODEL( ).
*   Get Entity Properties.
    lt_entity_props = lo_model->GET_ENTITY_TYPE( lv_entity_name )->GET_PROPERTIES( ).

*   Traverse filters.
    loop at IT_FILTER_SELECT_OPTIONS into ls_filter_sel.
*     Map Model Property to ABAP field name.
      READ TABLE lt_entity_props into ls_entity_props
      WITH KEY NAME = ls_filter_sel-PROPERTY.
      if sy-subrc = 0.
*       Evaluate (single) Property filter on EntitySet.
        loop at CT_ENTITYSET ASSIGNING <fs_data>.
          lv_tabix = sy-tabix.
*         Get Property value.
          ASSIGN COMPONENT ls_entity_props-TECHNICAL_NAME OF STRUCTURE <fs_data> TO <fs_val>.
          if sy-subrc = 0 and <fs_val> is ASSIGNED.
*           Evaluate i'th filter (not adhering to filter => delete).
            if <fs_val> not in ls_filter_sel-SELECT_OPTIONS.
*             Delete from table, when not adhering to filter.
              delete CT_ENTITYSET index lv_tabix.
            endif.
          endif.
        endloop.
      endif.
    endloop.
  CATCH CX_ROOT into lx_root.
    me->RAISE_EXCEPTION_FROM_MESSAGE( 'Error in method ENTITYSET_FILTER :' && lx_root->GET_TEXT( ) ).
  ENDTRY.

endmethod.


method ENTITYSET_ORDER.

* Generic method to sort any entityset table of the corresponding Model.

  data:
  lx_root         type ref to CX_ROOT,
  lo_data_descr   type ref to CL_ABAP_DATADESCR,
  lo_table_descr  type ref to CL_ABAP_TABLEDESCR,
  lo_dp_facade    type ref to /IWBEP/CL_MGW_DP_FACADE,  "/IWBEP/IF_MGW_DP_FACADE,
  lo_model        type ref to /IWBEP/IF_MGW_ODATA_RE_MODEL,
  ls_entity_props type /IWBEP/IF_MGW_ODATA_RE_PROP=>TY_S_MGW_ODATA_PROPERTY,
  lt_entity_props type /IWBEP/IF_MGW_ODATA_RE_PROP=>TY_T_MGW_ODATA_PROPERTIES,
  ls_order        type /IWBEP/S_MGW_SORTING_ORDER,
  lv_entity_name  type /IWBEP/MED_EXTERNAL_NAME,
  lv_type         type STRING,
  ls_sortorder    type ABAP_SORTORDER,
  lt_sortorder    type ABAP_SORTORDER_TAB.

* Pre-check.
  check lines( IT_ORDER ) > 0.

* 'Type-cast' datatype.
  lv_entity_name = IV_ENTITY_NAME.

* Get type of table.
  TRY.
*   Get DP facade.
    lo_dp_facade ?= me->/IWBEP/IF_MGW_CONV_SRV_RUNTIME~GET_DP_FACADE( ).
*   Get Model
    lo_model = lo_dp_facade->/IWBEP/IF_MGW_DP_INT_FACADE~GET_MODEL( ).
*   Get Entity Properties.
    lt_entity_props = lo_model->GET_ENTITY_TYPE( lv_entity_name )->GET_PROPERTIES( ).
*   Convert sorting table ('OData' -> ABAP).
    loop at IT_ORDER into ls_order.
*     Map Model Property to ABAP field name.
      READ TABLE lt_entity_props into ls_entity_props
      WITH KEY NAME = ls_order-PROPERTY.
      if sy-subrc = 0.
*       Build ABAP sort order table.
        clear ls_sortorder.
        ls_sortorder-NAME = ls_entity_props-TECHNICAL_NAME.
        if TO_UPPER( ls_order-ORDER ) = 'DESC'.
          ls_sortorder-DESCENDING = ABAP_TRUE.
        endif.
        append ls_sortorder to lt_sortorder.
      else.
*       Consider raising exception !.
      endif.
    endloop.
*   Perform sorting.
    if lines( lt_sortorder ) > 0.
      sort CT_ENTITYSET by (lt_sortorder).
    endif.

  CATCH CX_ROOT into lx_root.
    me->RAISE_EXCEPTION_FROM_MESSAGE( 'Error in method ENTITYSET_ORDER :' && lx_root->GET_TEXT( ) ).
  ENDTRY.

endmethod.


method ENTITYSET_PAGING.

  data:
  lv_beg_ix  type I,
  lv_end_ix  type I.

* Exit if TOP and SKIP are both initial.
  check IS_PAGING is not initial.

* Calculate Begin and End index.
  lv_beg_ix = IS_PAGING-SKIP + 1.
  lv_end_ix = lv_beg_ix + IS_PAGING-TOP.

* Delete all entries outside of top and skip.
  loop at CT_ENTITYSET ASSIGNING FIELD-SYMBOL(<fs_dummy>).
    if sy-tabix < lv_beg_ix OR sy-tabix > lv_end_ix.
      delete CT_ENTITYSET index sy-tabix.
    endif.
  endloop.

endmethod.

endclass.