Architect / Cross-trackadvanced

OData Gateway Part 7: GET_ENTITYSET with Filters, Paging and Sorting

Key takeaway

Implement production-grade list reads: translate $filter into SELECT-OPTIONS-like ranges, apply $top/$skip paging in the database, map $orderby to a dynamic ORDER BY, return $inlinecount/$count, handle $search, and avoid reading everything and filtering in ABAP.

Explanation

📌 KEY POINTS
✅ Query options arrive as method parameters: it_filter_select_options ($filter), is_paging ($top/$skip), it_order ($orderby), iv_search_string ($search), plus io_tech_request_context for inlinecount and $select.
✅ Filters must reach the database: convert them into ranges and use them in WHERE. Reading all rows and filtering in ABAP kills performance.
✅ Paging must be applied after sorting and in the database: ORDER BY + UP TO (top+skip) ROWS, then drop the first skip rows — or a dedicated OFFSET in strict SQL.
⚠️ A Fiori list with growing/paging calls GET_ENTITYSET repeatedly with $skip. Without a stable ORDER BY, rows appear twice or go missing between pages.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 6 read one record. Now the list read that every Fiori list report depends on.

━━━━━━━━━━━━━━━━━━━━
▶ 1. $FILTER

Simple filters (eq, ne, gt, ge, lt, le, between, startswith for strings) arrive in it_filter_select_options: property + select_options (sign, option, low, high).
Property names here are external names (Customer); io_tech_request_context->get_filter( )->get_filter_select_options( ) gives ABAP names.
Complex filters (or across properties, functions) may only come as iv_filter_string — parse carefully or reject.

━━━━━━━━━━━━━━━━━━━━
▶ 2. RANGES IN WHERE

Build DATA lt_r_kunnr TYPE RANGE OF kunnr; fill from select_options; ALPHA-convert low/high.
SELECT … WHERE kunnr IN lt_r_kunnr AND erdat IN lt_r_erdat.
Empty range = no restriction (as with SELECT-OPTIONS).

━━━━━━━━━━━━━━━━━━━━
▶ 3. $ORDERBY

it_order: property + order ('asc'/'desc').
Map properties to DB fields (CreatedOn → ERDAT) and build a dynamic ORDER BY string; default to the key for stable paging.

━━━━━━━━━━━━━━━━━━━━
▶ 4. PAGING

is_paging-top and is_paging-skip.
Classic: SELECT … ORDER BY … UP TO (top + skip) ROWS, then DELETE the first skip rows.
Helper for small result sets: /iwbep/cl_mgw_data_util=>paging and =>orderby (in-memory, only after a selective read).

━━━━━━━━━━━━━━━━━━━━
▶ 5. $INLINECOUNT / $COUNT

If io_tech_request_context->has_inlinecount( ) is true: SELECT COUNT(*) with the same WHERE and set es_response_context-inlinecount.
Fiori shows "Sales Orders (1,234)" from this value.

━━━━━━━━━━━━━━━━━━━━
▶ 6. $SEARCH

iv_search_string: map to a pattern on a few fields (CP or LIKE), document which fields are searched.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Map Customer and CreatedOn filters to ranges.
Build ORDER BY from it_order with key as default.
Apply top+skip in SELECT, then remove skipped rows.
Return inlinecount with a COUNT(*) using the same WHERE.
Test: ?$filter=Customer eq '1000'&$orderby=CreatedOn desc&$top=20&$skip=20&$inlinecount=allpages

✅ CHECKPOINT: your list reads filter, sort, page and count in the database with stable results.

Code example

ABAP Code

* ===== STEP 1: DPC_EXT - SALESORDERSET_GET_ENTITYSET =====METHOD salesorderset_get_entityset.   DATA: ls_filter  TYPE /iwbep/s_mgw_select_option,        ls_selopt  TYPE /iwbep/s_cod_select_option,        lt_r_kunnr TYPE RANGE OF kunnr,        ls_r_kunnr LIKE LINE OF lt_r_kunnr,        lt_r_erdat TYPE RANGE OF erdat,        ls_r_erdat LIKE LINE OF lt_r_erdat,        ls_order   TYPE /iwbep/s_mgw_sorting_order,        lv_orderby TYPE string,        lv_field   TYPE string,        lv_max     TYPE i,        lv_count   TYPE i. * ===== STEP 2: $filter -> ranges =====  LOOP AT it_filter_select_options INTO ls_filter.    CASE ls_filter-property.      WHEN 'Customer'.        LOOP AT ls_filter-select_options INTO ls_selopt.          ls_r_kunnr-sign   = ls_selopt-sign.          ls_r_kunnr-option = ls_selopt-option.          CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'            EXPORTING input = ls_selopt-low IMPORTING output = ls_r_kunnr-low.          ls_r_kunnr-high = ls_selopt-high.          APPEND ls_r_kunnr TO lt_r_kunnr.        ENDLOOP.      WHEN 'CreatedOn'.        LOOP AT ls_filter-select_options INTO ls_selopt.          ls_r_erdat-sign   = ls_selopt-sign.          ls_r_erdat-option = ls_selopt-option.          ls_r_erdat-low    = ls_selopt-low.          ls_r_erdat-high   = ls_selopt-high.          APPEND ls_r_erdat TO lt_r_erdat.        ENDLOOP.    ENDCASE.  ENDLOOP. * ===== STEP 3: $orderby -> dynamic ORDER BY (key as default) =====  LOOP AT it_order INTO ls_order.    CASE ls_order-property.      WHEN 'CreatedOn'. lv_field = 'ERDAT'.      WHEN 'Customer'.  lv_field = 'KUNNR'.      WHEN OTHERS.      CONTINUE.    ENDCASE.    IF ls_order-order = 'desc'.      CONCATENATE lv_field 'DESCENDING' INTO lv_field SEPARATED BY space.    ENDIF.    CONCATENATE lv_orderby lv_field INTO lv_orderby SEPARATED BY space.  ENDLOOP.  CONCATENATE lv_orderby 'VBELN' INTO lv_orderby SEPARATED BY space. * ===== STEP 4: paging in the database =====  IF is_paging-top > 0.    lv_max = is_paging-top + is_paging-skip.  ELSE.    lv_max = 0.                                   "0 = no limit  ENDIF.   SELECT vbeln AS salesorder erdat AS createdon kunnr AS customer         netwr AS netamount waerk AS currency    FROM vbak    INTO CORRESPONDING FIELDS OF TABLE et_entityset    UP TO lv_max ROWS    WHERE kunnr IN lt_r_kunnr      AND erdat IN lt_r_erdat    ORDER BY (lv_orderby).   IF is_paging-skip > 0.    DELETE et_entityset FROM 1 TO is_paging-skip.  ENDIF. * ===== STEP 5: $inlinecount =====  IF io_tech_request_context->has_inlinecount( ) = abap_true.    SELECT COUNT(*) FROM vbak INTO lv_count      WHERE kunnr IN lt_r_kunnr        AND erdat IN lt_r_erdat.    es_response_context-inlinecount = lv_count.  ENDIF. ENDMETHOD.

Real project scenario

A Fiori "My Customers' Orders" list took 40 seconds to show 20 rows. GET_ENTITYSET read all 3 million VBAK rows, then applied the Customer filter and paging with /iwbep/cl_mgw_data_util in memory.
The developer moved the filter into WHERE via ranges, applied ORDER BY plus UP TO top+skip ROWS in the database and computed inlinecount with a COUNT(*). The first page loaded in under a second. A second bug — duplicated rows when scrolling — disappeared once VBELN was always appended to the ORDER BY for stable paging.

Common mistakes

Reading all rows and filtering/paging in ABAP.
Paging without ORDER BY — unstable pages.
Sorting in memory after the database already cut the rows.
Forgetting ALPHA conversion for filter values.
Ignoring $inlinecount, so the UI shows wrong totals.

Best practices

Push filters, sorting and paging into the SELECT.
Always add the key as last ORDER BY field.
Map only supported properties; reject or document others.
Use COUNT(*) with identical WHERE for inlinecount.
Use the in-memory data_util helpers only after a selective read.

Interview angle

Questions: "How do you implement $filter and paging in GET_ENTITYSET?", "Why do rows duplicate while scrolling?", "How do you return the total count?"
Strong answer: it_filter_select_options → ranges in WHERE (with ALPHA conversion); it_order → dynamic ORDER BY; is_paging top/skip → UP TO top+skip ROWS then drop skip rows, or OFFSET in strict SQL; inlinecount via has_inlinecount and COUNT(*) into es_response_context. Duplicates come from paging without a stable ORDER BY including the key.

Practice this lesson

Quick check

1. A list service pages with $top/$skip but users see duplicate rows while scrolling. What is missing?

2. In GET_ENTITYSET, where do $top and $skip arrive?

3. Which query option filters entities?

Interview questions

1. Users scrolling a Fiori list see some orders twice and others never. The service implements $top/$skip. What is the cause?

Answer it in your own words first, then compare.

2. An OData list service is slow and returns thousands of records. How would you improve it?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.