SAPUI5 JS Combine multiple filters with OR

Question

I want to combine multiple filters with an or for my OData request. The filters work fine if I try them each seperate. Now I need to combine them. Those are the single filters (aFilterBetween, aFilterLeftBorder, aFilterRightBorder):

var aFilterBetween = [];
aFilterBetween.push(new Filter({
    filters: [
        new Filter("StartDate", "GE", calenderStartDate),
        new Filter("EndDate", "LE", calenderEndDate)
    ],
    and: true
}));

var aFilterLeftBorder = [];
aFilterLeftBorder.push(new Filter({
    filters: [
        new Filter("StartDate", "LT", calenderStartDate),
        new Filter("EndDate", "GT", calenderStartDate)
    ],
    and: true
}));

var aFilterRightBorder = [];
aFilterRightBorder.push(new Filter({
    filters: [
        new Filter("StartDate", "LT", calenderStartDate),
        new Filter("EndDate", "GT", calenderEndDate)
    ],
    and: true
}));

The three filters should be combined with or so that the result will show everything which matches one of those three filters. The combined filter of those three must be used for my OData request like below...

oDataModel.read("/Initiatives", {
    filters: ***here has to be the filter***,
    urlParameters: {
        "$top": "50",
        "$select": "StartDate,EndDate"
    },
    success: function(oData, oResponse) {
    ... }

I tried to connect the three filters (here even just two) using and: false but somehow it didn't work for me:

var aFilterCombined = [];
aFilterCombined.push(new Filter({
    filters: [
        aFilterBetween,
        aFilterLeftBorder
    ],
    and: false
}));

Any ideas?

Answer 1

According with SAP Note 2377685:

You can use the OR operators to apply different filters on the same field. However, you cannot use OR condition to apply filters on two different fields. For example: http://services.odata.org/OData/OData.svc/Products?$filter=Name eq 'Milk' or Name eq 'Bread'

I came across this limitation a couple of times and it does not matter if you use service development (manual implementation in DPC_EXT) or service generation (implementations based on RFC, BOR, CDS, etc).

What I recommend doing is add a new "dummy" property in your entity type and set the filter to this property only. In your ABAP code, you can get the infornation from this property and use an OR internally. As you might have different data types in the columns which you need the OR clause, I recommend using an 'Edm.String' type in that dummy property.

So instead of doing a call like this:

http://services.odata.org/OData/OData.svc/Products?$filter=Name eq 'Milk' or Name eq 'Bread'

You can make do with

http://services.odata.org/OData/OData.svc/Products?$filter=GenericFilterProperty eq 'FilterValue'

In your specific case, 'FilterValue' is probably a very long string because you are using multiple date ranges. Fear not. You can represent your filter criteria as a JavaScript object and then convert it to JSON.

var aFilterBetween = [];
aFilterBetween.push(new Filter({
    filters: [
        new Filter("StartDate", "GE", calenderStartDate),
        new Filter("EndDate", "LE", calenderEndDate)
    ],
    and: true
}));

var aFilterLeftBorder = [];
aFilterLeftBorder.push(new Filter({
    filters: [
        new Filter("StartDate", "LT", calenderStartDate),
        new Filter("EndDate", "GT", calenderStartDate)
    ],
    and: true
}));

var aFilterRightBorder = [];
aFilterRightBorder.push(new Filter({
    filters: [
        new Filter("StartDate", "LT", calenderStartDate),
        new Filter("EndDate", "GT", calenderEndDate)
    ],
    and: true
}));

// represent filters as a String
var sFilterValue = JSON.stringify(aFilterRightBorder);

then...

oDataModel.read("/Initiatives", {
    filters: [new Filter({
        path: "GenericFilterProperty",
        operator: "EQ",
        value: sFilterValue 
     })],
    urlParameters: {
        "$top": "50",
        "$select": "StartDate,EndDate"
    },
    success: function(oData, oResponse) {
    ... }

With that approach, you will need to convert a JSON to some kind of ABAP data structure (work area or internal tables). In order to do that, I recommend using class cl_fdt_json, method json_to_data.

Answer 2

Try this:

var oFilterCombined = new Filter({
    filters: [
        oFilter1,
        oFilter2
    ],
    and: false
});

Also the other filters, I'd create filter objects instead of pushing into arrays and use the sap constants instead of strings for the filter operators (https://sapui5.hana.ondemand.com/1.54.8/#/api/sap.ui.model.FilterOperator):

var oFilterSingle= new Filter({
            filters: [
                new Filter("XXX", FilterOperator.EQ, "bla"),
                new Filter("YYY", FilterOperator.EQ, "blub")
            ],
            and: true
        });

the read would like that:

oDataModel.read("/Initiatives", {
    filters: oFilterCombined,
    urlParameters: {
        "$top": "50",
        "$select": "StartDate,EndDate"
    },
    success: function(oData, oResponse) {
    ...
}

PLEASE NOTE:

The issue might not be the ui5 code but the backend. If this doesn't work please post your the batch your browser sends.

If you are using a ABAP Backend with an OData service generated by SEGW: Mappings can't differntiate between and and or filters! You need to implement that manually!