SAP RAP: Using if_rap_query_provider~select for Nested Data read

[Jay_Prakash_Singh]

In this post, I will explain how to implement nested read operations in a RESTful Application Programming (RAP) Business Object (BO). In managed scenarios, standard read operations are straightforward, as the RAP framework provides built-in support. However, when custom logic is required—such as applying specific filters or handling complex query parameters—we need to implement custom query operations using the if_rap_query_provider~select method.

In the following example, I will demonstrate how to create a simple custom CDS entity, implement the corresponding artifacts, and test the nested read functionality. This approach allows for tailored data retrieval, accommodating specific business requirements beyond the capabilities of standard operations.

Example Overview:

1. Creating custom entities - we are going to create 2 custom entity Parent and Child.

@EndUserText.label: 'Parent Entity'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_CE_PARENT_<add your alias>'
define root custom entity ZCE_PARENT_<add your alias>'
{
key matnr : abap.char(40);
key werks : abap.char(4);
region : abap.char(10);
bo_qty : abap.int4;
dlv_qty : abap.int4;
po_qty : abap.int4;
_Child : composition [0..*] of zce_child__<add your alias>;
}

@EndUserText.label: 'child entity'
@ObjectModel.query.implementedBy: 'ABAP:ZCL_CE_PARENT_<add your alias>'
define custom entity ZCE_CHILD_<add your alias>'
{
key matnr : abap.char(40);
key werks : abap.char(4);
key mrpa : abap.char(10);
bo_qty : abap.int4;
dlv_qty : abap.int4;
po_qty : abap.int4;
_Parent : association to parent ZCE_PARENT_<add your alias>' on _Parent.matnr = $projection.matnr
and _Parent.werks = $projection.werks;
}

2. Create Query class ZCL_CE_PARENT_<add your alias> .

You can add same class or can create different different class for each entity.

CLASS zcl_ce_parent_<add your alias> DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES: ty_parent_t TYPE TABLE OF zce_parent_<add your alias> WITH DEFAULT KEY.
TYPES: ty_child_t TYPE TABLE OF zce_child_<add your alias> WITH DEFAULT KEY.
INTERFACES if_rap_query_provider .
METHODS get_parent_data_info RETURNING VALUE(rt_parent_info) TYPE ty_parent_t.
METHODS get_child_data_info RETURNING VALUE(rt_child_info) TYPE ty_child_t.
PRIVATE SECTION.
ENDCLASS.

CLASS ZCL_CE_PARENT_<add your alias> IMPLEMENTATION.

METHOD get_child_data_info.
APPEND INITIAL LINE TO rt_child_info ASSIGNING FIELD-SYMBOL(<fs_parent>).
<fs_parent>-matnr = 'MATERIAL1'.
<fs_parent>-description = 'Test MATERIAL1'.
<fs_parent>-werks = 'YYZE'.
<fs_parent>-mrpa = 'MRPA1'.
<fs_parent>-po_qty = 60.
<fs_parent>-bo_qty = 100.
APPEND INITIAL LINE TO rt_child_info ASSIGNING <fs_parent>.
<fs_parent>-matnr = 'MATERIAL1'.
<fs_parent>-description = 'Test MATERIAL1'.
<fs_parent>-werks = 'YYZE'.
<fs_parent>-mrpa = 'MRPA2'.
<fs_parent>-po_qty = 40.
<fs_parent>-bo_qty = 100.
<fs_parent>-dlv_qty = 50.
APPEND INITIAL LINE TO rt_child_info ASSIGNING <fs_parent>.
<fs_parent>-matnr = 'MATERIAL2'.
<fs_parent>-description = 'Test MATERIAL2'.
<fs_parent>-werks = 'YYZE'.
<fs_parent>-po_qty = 400.
<fs_parent>-bo_qty = 500.
<fs_parent>-mrpa = 'MRPA1'.
ENDMETHOD.
METHOD get_parent_data_info.
APPEND INITIAL LINE TO rt_parent_info ASSIGNING FIELD-SYMBOL(<fs_parent>).
<fs_parent>-matnr = 'MATERIAL1'.
<fs_parent>-description = 'Test MATERIAL1'.
<fs_parent>-werks = 'YYZE'.
<fs_parent>-region = 'ON'.
<fs_parent>-po_qty = 100.
<fs_parent>-bo_qty = 200.

APPEND INITIAL LINE TO rt_parent_info ASSIGNING <fs_parent>.
<fs_parent>-matnr = 'MATERIAL2'.
<fs_parent>-description = 'Test MATERIAL2'.
<fs_parent>-werks = 'YYZE'.
<fs_parent>-region = 'ON'.
<fs_parent>-po_qty = 400.
<fs_parent>-bo_qty = 500.
<fs_parent>-dlv_qty = 200.
ENDMETHOD.

METHOD if_rap_query_provider~select.
DATA(lv_top) = io_request->get_paging( )->get_page_size( ).
IF lv_top < 0.
lv_top = 1.
ENDIF.

DATA(lv_skip) = io_request->get_paging( )->get_offset( ).

DATA(lt_sort) = io_request->get_sort_elements( ).

DATA(lv_conditions) = io_request->get_filter( )->get_as_sql_string( ).

DATA(lt_req_elements) = io_request->get_requested_elements( ).
CASE io_request->get_entity_id( ).
WHEN 'ZCE_PARENT_<add your alias>'.
IF io_request->is_data_requested( ).
DATA(lt_parent) = get_parent_data_info( ).
io_response->set_data( lt_parent ).
IF io_request->is_total_numb_of_rec_requested( ).
io_response->set_total_number_of_records( 1 ) .
ENDIF.
ENDIF.

WHEN 'ZCE_CHILD_<add your alias>'.
DATA(lv_top1) = io_request->get_paging( )->get_page_size( ).
IF lv_top1 < 0.
lv_top1 = 1.
ENDIF.

DATA(lv_skip1) = io_request->get_paging( )->get_offset( ).

DATA(lt_sort1) = io_request->get_sort_elements( ).

DATA(lv_conditions1) = io_request->get_filter( )->get_as_sql_string( ).

DATA(lt_req_elements1) = io_request->get_requested_elements( ).

IF io_request->is_data_requested( ).
DATA(lt_child) = get_child_data_info( ).
io_response->set_data( lt_child ).
ENDIF.

IF io_request->is_total_numb_of_rec_requested( ).
io_response->set_total_number_of_records( lines( lt_child ) ).
ENDIF.

WHEN 'ZCE_GRANDCHILD_<add your alias>'.
DATA(lv_top2) = io_request->get_paging( )->get_page_size( ).
IF lv_top2 < 0.
lv_top2 = 1.
ENDIF.

DATA(lv_skip2) = io_request->get_paging( )->get_offset( ).

DATA(lt_sort2) = io_request->get_sort_elements( ).

DATA(lv_conditions2) = io_request->get_filter( )->get_as_sql_string( ).

DATA(lt_req_elements2) = io_request->get_requested_elements( ).
IF io_request->is_data_requested( ).
DATA(lt_data) = get_grandchild_data( ).
io_response->set_data( lt_data ).
ENDIF.
IF io_request->is_total_numb_of_rec_requested( ).
io_response->set_total_number_of_records( lines( lt_data ) ).
ENDIF.
ENDCASE.

ENDMETHOD.
ENDCLASS.

3. Create behavior definition, Service definition and Service binding.

Once it is done then test your API using below URL.

https://<your server address>/sap/opu/odata/sap/<service binding>/<Entityset name>?$expand=to<Navigation name given in root entity>

Please try and feel free to update as you need or comment your questions.

Thanks,

Jay

[junwu]

the first paragraph is missleading, it sounds like the custom entity support more complex query/advanced...., no, no. you turn to custom entity only because you cannot select the data from db