Gateway OData service – how to implement generic filtering ($filter), sorting ($orderby) and paging ($top and $skip)
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.
Extract of the generic filtering method.
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.
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.
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.
nice blog 😉
last week I stumbled over this blog from Martin Fischer
With the statement
osql_where_clause = io_tech_request_context->get_osql_where_clause( ).
a where clause string is build dynamically from the filter settings
io_tech_request_context is part of the function parameters.
But I don't know from which Basis Release on this is available . On 7.50 it's available.
Also for paging and sorting we use /iwbep/cl_mgw_data_util methods ( but again I don't know which Basis Release is needed.
Thanks for your input - I was not aware of these.
Brgds, Søren Hansen
I like the approach of doing a generic filtering but I would suggest not to use IT_FILTER_SELECT_OPTIONS but the io_tech_request_context object.
The paramters such as IT_FILTER_SELECT_OPTIONS are obsolete but could not be removed from the signature to ensure backwards compatibility.
With io_tech_request_context there are better options to handle conversions.