Homelab, Linux, JS & ABAP (~˘▾˘)~
 

[SuccessFactors] OData V2 – filter in (VSC Rest-API Client)

When filtering an OData V2 endpoint, you can simply list your values separated by a comma after the keyword in (option 2). Much shorter than having to repeat your filter statement all the time, like in option 1.

@user1=10010
@user2=10020

### Option 1: Filter userId using OR condition
GET {{$dotenv api_url}}/odata/v2/User?$filter=userId eq '{{user1}}' or userId eq '{{user2}}'
Authorization: Basic {{$dotenv api_auth}}
Accept: application/json

### Option 2: Filter userId using IN condition
GET {{$dotenv api_url}}/odata/v2/User?$filter=userId in '{{user1}}', '{{user2}}'
Authorization: Basic {{$dotenv api_auth}}
Accept: application/json

[ABAP] Filter table using VALUE FOR

DATA(lt_result) = VALUE z_type( FOR line IN lt_table
                                WHERE ( value IN lr_values )
                                ( field = ls_line-value ) ).

[ABAP] OData – Filtering, Sorting, Paging

    " Filter, Sort, Paging
    /iwbep/cl_mgw_data_util=>filtering( EXPORTING it_select_options = it_filter_select_options
                                        CHANGING  ct_data           = et_entityset ).
    /iwbep/cl_mgw_data_util=>orderby(   EXPORTING it_order          = it_order
                                        CHANGING  ct_data           = et_entityset ).
    /iwbep/cl_mgw_data_util=>paging(    EXPORTING is_paging         = is_paging
                                        CHANGING  ct_data           = et_entityset ).

[CAP] SAPUI5 Filter using FilterOperator.Contains with oData V2

In my SAPUI5 Freesstyle frontend I created a search field above a list. In the searchfield handler I’m creating a filter with the provided query.

const sQuery = oEvent.getParameter("query");
new Filter("firstName", FilterOperator.Contains, sQuery);

Afterwards I’m binding the filter to my list to trigger the binding refresh. But when debugging the backend handler I noticed the following…

In my CAP on Read handler, the filter gets converted into a V4 compatible filter expression:

oData V4: $filter=contains(firstName,'Max')

As I’m forwarding the request to an external V2 oData API (SuccessFactors) this would not work, as for V2 the following filter syntax is needed:

oData V2: $filter=substringof('Max',firstName) eq true

As I could not find any solution to this problem, I manually passed my filter as custom property to my CAP Service and did a manual select.

Adding the custom property in the frontend in my searchfield handler:

onSearch: function (oEvent) {
			if (oEvent.getParameters().refreshButtonPressed) {
				this.onRefresh();
				return;
			}

			let oBindingInfo = this._oList.getBindingInfo("items");
			if (!oBindingInfo.parameters) oBindingInfo.parameters = {};
			if (!oBindingInfo.parameters.custom) oBindingInfo.parameters.custom = {};

			if (oEvent.getParameter("query")) {
				oBindingInfo.parameters.custom.filter = "%" + oEvent.getParameter("query") + "%";
			} else {
				oBindingInfo.parameters.custom.filter = undefined
			}
			this._oList.bindItems(oBindingInfo);
}

My CAP handler with the filter handling:

const { Object } = srv.entities
const SF_Srv = await cds.connect.to('SF')

srv.on('READ', Object, async req => {

            if (!req._queryOptions.filter) {
                // share request context with the external service 
                return SF_Srv.tx(req).run(req.query);
            } else {
                //if filter provided, build manually a select statement using a where condition
                let input = req._queryOptions.filter;
                const tx = SF_Srv.transaction(req);
                return await tx.run(
                    SELECT
                        .from(Object)
                        .where`firstName like ${input} or lastName like ${input}`)
            }
    })

As alternative you could also add the where condition directly to the query object:

const { Object } = srv.entities
const SF_Srv = await cds.connect.to('SF')

srv.on('READ', Object, async req => {

            if (req._query.filter) {
                //if filter provided, build manually a select statement using a where condition
                let { query } = req
                let input = req._queryOptions.filter

                if (!query.SELECT.where) query.SELECT["where"] = []

                query.SELECT.where.push(
                    { ref: ['firstName'] }, 'like', { val: input }, "or",
                    { ref: ['lastName'] }, 'like', { val: input }, "or",
                    { ref: ['object'] }, 'like', { val: input })
            }

            // share request context with the external service 
            return SF_Srv.tx(req).run(req.query)
    })