OData dynamic Where through recursion for Select Queries

In this blog we will take a look at recursion for solving OData dynamic where clause.

SAP had already advanced in Gateway service technology and today’s technology (CDS) does need a Gateway service to be built manually.

However, many clients out there are still using manual Gateway services as a primary data source of Fiori or custom UI5 applications.

The challenge of building such OData services starts with the filter and its use in where clause for select. There is no standard way of building where based on entity, except manually writing each property and its select-options in the where for a given query.

This is a cumbersome and repetitive process, also it involves a lot of variables, the more variables we use the more problems they bring along.

The solution to make the development simpler is a dynamic where clause, a method should return required where irrespective of entities or properties based on the “filter select option” table.

This is my way of solving this challenge by using recursion:

Method signature:

  class-methods RECURSION
    importing
      !IV_INDEX type I
      !IT_FILTER_SELECT_OPTIONS type /IWBEP/T_MGW_SELECT_OPTION
    returning
      value(RV_WHERE) type STRING .

Method implementation:

"== Check if we are at the bottom of the table
ASSIGN it_filter_select_options[ iv_index ] TO FIELD-SYMBOL(<lfs_itab>).
CHECK <lfs_itab> IS ASSIGNED.

"== Evalute Inner table for OR conditions, could be one more recursion?
rv_where = |( | && |{ cl_shdb_seltab=>new( it_sel = <lfs_itab>-select_options[] )->sql_where_condition( <lfs_itab>-property ) }|  && | )|.

"== Add conjector for each key propety, minus last key because it's suffixe and we will end up with extra AND in the end
rv_where = rv_where && COND #( WHEN iv_index LT lines( it_filter_select_options ) THEN | AND | ELSE `` ).

"== Itrate thourgh recursion unless we reach at the bottom of the tree
rv_where = rv_where && recursion( iv_index = iv_index + 1
                                      it_filter_select_options = it_filter_select_options ).

Recursion call and conversion of entity properties to ABAP technical name:

DATA: lt_inputfilter TYPE /iwbep/t_mgw_select_option.

TRY.
  DATA(lo_service) = CAST /iwbep/if_mgw_conv_srv_runtime( ir_srv_instance ).
  DATA(lo_facade) = CAST /iwbep/cl_mgw_dp_facade( lo_service->get_dp_facade( ) ).
  DATA(lo_model) = lo_facade->/iwbep/if_mgw_dp_int_facade~get_model( ).
  DATA(li_entities)     = lo_model->get_entity_types( ).
CATCH cx_root.
  RETURN.
ENDTRY.

"== use local copy
lt_inputfilter = it_filter_select_options[].

"== replace properties with ABAP type names for exmple RecPriority = REC_PRIORITY
LOOP AT li_entities ASSIGNING FIELD-SYMBOL(<lfs_entity>) WHERE name = iv_entity_name.
DATA(lo_entity) = lo_model->get_entity_type( |{ <lfs_entity>-name }| ).
LOOP AT lo_entity->get_properties( ) ASSIGNING FIELD-SYMBOL(<lfs_prop>).
  ASSIGN lt_inputfilter[ property = <lfs_prop>-name ] TO FIELD-SYMBOL(<lfs_property>).
  IF <lfs_property> IS ASSIGNED.
  <lfs_property>-property = <lfs_prop>-technical_name.
  ENDIF.
  UNASSIGN <lfs_property>.
ENDLOOP.
ENDLOOP.

"== Generate Dynamic Where
CALL METHOD zcl_odata_utility=>recursion
  EXPORTING
   iv_index                 = 1
   it_filter_select_options = lt_inputfilter
  RECEIVING
   rv_where                 = rv_where.

The first call in the stack in GET_ENTITYSET or GET_ENTITY

"== Generate where from filter
DATA(l_where) = zcl_odata_utility=>dynamic_where(  
                                  iv_entity_name  = iv_entity_name
                                  ir_srv_instance = me
                                  it_filter_select_options =  it_filter_select_options[] ).

The above solution is built for a single entity without any navigation and also does not consider a full scope of different comparator operators but it gives an idea for building something better.

This is a very simple recursion but very effective and gives a boost to build OData service manually.

If you like this article, feel free to share, tweet, like or follow me for new articles.