SAP GW – Implement a better OrderBy for Cust EXT Class
Intro
If you have defined your OData Service in transaction SEGW and use the own class (* _DPC_EXT) for Service Implementation,
you may have asked yourself how to use the parameter IT_ORDER of Type /IWBEP/T_MGW_SORTING_ORDER to implement sorting of your entity table.
One way could be to use standard GW util class /IWBEP/CL_MGW_DATA_UTIL and its method orderBy.
But by using this, it might happen that you stumble upon a constraint where you get an exception if you use a parameter in your orderBy clause which has a underline in its name definition.
For example you would use this URL where StartDate is the attribute you want to sort descending:
/sap/opu/odata/sap/<YOUR_SERVICE_NAME>/ProjectSet?$orderby=StartDate%20desc
The Problem is that ‘StartDate’ is the attribute name of your gateway service entity and in you backend table it could have the name ‘START_DATE’.
so ‘StartDate’ <> ‘START_DATE’
Because of the standard implementation of method orderBy you will get an Error : “RFC Error: Incorrect value in the dynamic table.”
HowTo
To get arround this pittfall you could use your own util class to sort the table.
Where you can use o_tech_request_context object.
This object does the mapping to get the corresponding database field name (see comment of Ron).
It could look like this:
IT_ORDER TYPE /IWBEP/T_MGW_TECH_ORDER "*the sorting order
CT_DATA TYPE STANDARD TABLE " * CT_DATA is your Entetyset table returning just after the select from db. METHOD orderby.
DATA: lt_otab TYPE abap_sortorder_tab,
ls_oline TYPE abap_sortorder.
DATA: ls_order LIKE LINE OF it_order.
CONSTANTS:BEGIN OF lcs_sorting_order,
descending TYPE string VALUE 'desc',
ascending TYPE string VALUE 'asc',
END OF lcs_sorting_order.
LOOP AT it_order INTO ls_order.
ls_otab-name = ls_order-property.
IF ls_order-order = lcs_sorting_order-descending.
ls_otab-descending = abap_true.
ELSE.
ls_otab-descending = abap_false.
ENDIF.
APPEND ls_otab TO lt_otab.
ENDLOOP.
SORT ct_data BY (lt_otab).
ENDMETHOD.And you can use this method like this:
IF it_order[] IS NOT INITIAL.
"* get technical names for order table
DATA: lt_tech_order TYPE /iwbep/t_mgw_tech_order.
lt_tech_order = io_tech_request_context->get_orderby( ).
zcl_cl_util=>orderby(
EXPORTING
it_order = lt_tech_order " the sorting order
CHANGING
ct_data = et_feed
).
ENDIF.
Alexander, you have raised a valid point about the differences in names that are external and internal. However the transformation part of the solution isn't necessary and doesn't cover actual name changes, e.g. MATNR being "Material" externally.
What you should do is use the context access class, which has some model introspection built into it. If the name of this property changed internally, the code could break down.
Here is the "correct" way to determine the internal name for "orderby" properties and other such inputs.
data: lt_tech_order type /iwbep/t_mgw_tech_order.
lt_tech_order = io_tech_request_context->get_orderby( ).
The 'property' column of this order table contains the name in the correct ABAP format if it is derived from an ABAP element, so there is no need to transform it.
Regards
Ron.
Hi Ron,
thanks a lot.
You are right.
It would be a better way to map OData fieldnames to backend fieldnames.
I would update the post soon.
Regards,
Alex
Hi Alex,
The use of the technical context object hasn't really been promoted and it wasn't in the method interface in older versions of GW, so it does get overlooked. We now have a level of duplication in that there are parameters and objects exposing the same things (the context object has both internal and external request value sets in it).
I am assuming that SAP will deprecate the mandatory parameters eventually and expose the request context using the object. I'd prefer this as it means all those mandatory parameters aren't required if you need to call the method in your own code (which I have done). Using the context object also allows you to modify the request by copying, altering and forwarding the context object, which I have found really useful in some extensions I have written to the Odata handler.
Regards
Ron.
Hi Ron ,
One question regarding sorting.How should it work in conjunction with pagination.
Ideally it should be sorting first and then pagination.
If I have to handle both in GW side (In GET_ENTITY_SET) method
I should first get all the records from the backend .
Fill the et_entity_set (looping thru all the backendrecords)
Call this Utility method to get the sorted results back,.
Do the pagination stuff loop et_entity_set from lv_skip to lv_top .
Is there any better way to handle this other than multiple loops?
Regards
Arshad
Hi Arshad,
If you are using a BAPI for the service, it is most likely not going to support pagination and sorting very well, which is why a direct DB selection strategy would work best in the DPC.
If you do it this way, you can apply the "order by" to your selection with 'skip' number of rows. That's easy enough, the tricky part is establishing the selection start point on subsequent pages. This would most likely need to involve a $skiptoken, there are some discussions about this on other SCN posts.
Regards
Ron.
Hi Ron,
Thanks for your answer
We designed the Backend FM such that it takes care of only filtering . The Sorting /Pagination should happend from the Gateway layer .
The fieldnames in GW property and ABAP backend FMs are different . So inorder to avoid this multiple loop . I will do sorting of the backend FM returning param internal table with ABAP mapping fields using the same Utility method .
How can I get the corresponding ABAP mapping field names corresponding to GW property name dynamically?
Regards
Arshad
Hi Ron, I'm working on $orderby but have no idea how to code. Would u help me with that since I'm new on abap.
Hi Nono,
Alexander has already supplied example code, what is your problem?
Regards
Ron.
Hi,
Can Orderby option is used along with the filters, Please explain ?