Openui5: Case insensitive filter

[jpenninkhof]

It would be nice if the ODataModel filter would support case insensitive filtering besides the current default filtering. When this case insensitive filtering would be used, instead of e.g.

People?$filter=substringof(%27Smith%27,FullName)

The following filter would be used:

People?$filter=substringof(tolower(%27Smith%27),tolower(FullName))

You could think of e.g. implementing this using an additional set of operators, perhaps pre- or postfixed with an "I" to indicate they're case-insensitive:

var filter = new Filter("FullName", FilterOperator.ContainsI, "smith");

and

var filter = new Filter("FullName", FilterOperator.EQI, "smith");

[Michadelic]

Hello Jan,

good idea, it has been requested a lot of times already and it seems to be a missing feature in the odata specification. While we could add new filter operators in the odata model itself, the standard would not support it.

There are two very common workarounds:

• apply str.toLower() on the client
• implement the filter operation on the backend side case-insensitive

See also typical posts about this on goolge:
https://www.google.de/search?q=odata+case+insensitive+search&oq=odata+case+insen&aqs=chrome.2.69i57j0l5.4601j0j1&sourceid=chrome&ie=UTF-8

Nevertheless i will forward your request to the OData experts so that they can take your enhancement as input and follow up with you.

Kind Regards,
Michael

[StErMi]

Is there an update about this issue/request?

Cheers, Emanuele.

[gregorwolf]

Hi Michael,

when I look at the OData URI Conventions I find the option tolower. Here are some live examples which show that if SAPUI5 would support such a filter that we could get results:

• Results for Filter with tolower by Location eq münchen
• Results for Filter by Location eq München
• No Results for Filter by Location eq münchen

I hope that the option which is part of the OData Standard and also is supported in SAP HANA can be added soon.

Best regards
Gregor

[StErMi]

Hi @gregorwolf I can confirm that with our actual project we're folliwing that example.

Here's how to create the filter

createFilter: function(key, value, operator, useToLower) {
    return new Filter(useToLower ? "tolower(" + key + ")" : key, operator, useToLower ? "'" + value.toLowerCase() + "'" : value)
},

[pdominique]

As @gregorwolf already mentioned, tolower and toupper are part of the oData v2 URI conventions so it would be nice if ui5 and gateway would support them.

[ManuelB]

Hi guys,
we monkey patch our ODataModel:

/**
 * Create a single filter segment of the OData filter
 * parameters
 * 
 * @private
 */
sap.ui.model.odata.ODataUtils._createFilterSegment = function(
        sPath, oMetadata, oEntityType, sOperator,
        oValue1, oValue2, sFilterParam) {

    var oPropertyMetadata, sType;
    if (oEntityType) {
        oPropertyMetadata = oMetadata
                ._getPropertyMetadata(oEntityType,
                        sPath);
        sType = oPropertyMetadata
                && oPropertyMetadata.type;
        jQuery.sap.assert(oPropertyMetadata,
                "PropertyType for property " + sPath
                        + " of EntityType "
                        + oEntityType.name
                        + " not found!");
    }

    if (isNaN(oValue1)) {
        oValue1 = oValue1.trim();
    }
    if ((oValue2 != undefined) && (oValue2 != null)
            && (isNaN(oValue2))) {
        oValue2 = oValue2.trim();
    }
    if (sType) {
        oValue1 = this.formatValue(oValue1, sType);
        oValue2 = (oValue2 != null) ? this.formatValue(
                oValue2, sType) : null;
    } else if(sOperator != "IsOf") {
        jQuery.sap
                .assert(null,
                        "Type for filter property could not be found in metadata!");
    }

    if (oValue1) {
        oValue1 = jQuery.sap.encodeURL(String(oValue1));
    }
    if (oValue2) {
        oValue2 = jQuery.sap.encodeURL(String(oValue2));
    }

    // TODO embed 2nd value
    switch (sOperator) {
    case "EQ":
    case "NE":
    case "GT":
    case "GE":
    case "LT":
    case "LE":
        sFilterParam += sPath + "%20"
                + sOperator.toLowerCase() + "%20"
                + oValue1;
        break;
    case "BT":
        sFilterParam += "(" + sPath + "%20ge%20"
                + oValue1 + "%20and%20" + sPath
                + "%20le%20" + oValue2 + ")";
        break;
    case "Contains":
        // sFilterParam += "substringof(" + oValue1 +
        // "," + sPath + ")";
        sFilterParam += "substringof(tolower("
                + oValue1 + "),tolower(" + sPath + "))";
        break;
    case "StartsWith":
        // sFilterParam += "startswith(" + sPath + "," +
        // oValue1 + ")";
        sFilterParam += "startswith(tolower(" + sPath
                + "),tolower(" + oValue1 + "))";
        break;
    case "EndsWith":
        // sFilterParam += "endswith(" + sPath + "," +
        // oValue1 + ")";
        sFilterParam += "endswith(tolower(" + sPath
                + "),tolower(" + oValue1 + "))";
        break;
    case "IsOf":
        sFilterParam += "isof(" + oValue1 + ")";
        break;
    default:
        sFilterParam += "true";
    }
    return sFilterParam;
};

An ILIKE operator would be more useful.

Hope that helps
Manuel

[t1mb0b]

I was able to implement @StErMi solution above using the 'createFilter' function he provided:

aFilters.push(this._createFilter("client_name", sCliQuery, "Contains", true));

Cheers,

Tim

[piejanssens]

I would mark this as a critical bug instead of an enhancement...
SAP's own products such as SuccessFactors do not allow to implement case insensitive search on server side.

[mudit1993]

I searched the web but am not able to find the solution to it. Is it still in progress or do we have a solution for Filters case insensitive?
I tried implementing the custom functions provided in the document but it throws error.

[StErMi]

I searched the web but am not able to find the solution to it. Is it still in progress or do we have a solution for Filters case insensitive?
I tried implementing the custom functions provided in the document but it throws error.

What error does it throw? The implementation on the frontend side is correct but it doesn't mean that the backend part support the tolower function

[mudit1993]

I searched the web but am not able to find the solution to it. Is it still in progress or do we have a solution for Filters case insensitive?
I tried implementing the custom functions provided in the document but it throws error.

What error does it throw? The implementation on the frontend side is correct but it doesn't mean that the backend part support the tolower function

Thanks, got it!

[ThomasChadzelek]

Please see property caseSensitive of sap.ui.model.Filter.

[zygimantus]

Is there any solution for this? The F12 menu bursts into errors like this:

Assertion failed: PropertyType for property tolower(CUSTOM_FIELD) of EntityType CUSTOM_ENTITYType not found!
a @ assert-dbg.js:34
O._createFilterSegment @ ODataUtils-dbg.js:393
u @ ODataUtils-dbg.js:105
w @ ODataUtils-dbg.js:121
u @ ODataUtils-dbg.js:103
O._createFilterParams @ ODataUtils-dbg.js:142
O.createFilterParams @ ODataUtils-dbg.js:86
n.createFilterParams @ ODataListBinding-dbg.js:1459
n.filter @ ODataListBinding-dbg.js:1394
_filter @ BaseClsfController.js?eval:312
tableFilter @ BaseClsfController.js?eval:430
b.fireEvent @ EventProvider-dbg.js:228
b.fireEvent @ Element-dbg.js:544
(anonymous) @ ManagedObjectMetadata-dbg.js:763
j.filter @ Column.js?eval:6
eval @ ColumnMenu.js?eval:6
b.fireEvent @ EventProvider-dbg.js:228
b.fireEvent @ Element-dbg.js:544
(anonymous) @ ManagedObjectMetadata-dbg.js:763
j.selectItem @ Menu.js?eval:6
b.onkeyup @ MenuTextFieldItem.js?eval:6
b._handleEvent @ Element-dbg.js:259
U._handleEvent @ UIArea-dbg.js:921
dispatch @ jquery-dbg.js:4737
c3.handle @ jquery-dbg.js:4549

when filtering with tolower function, caseSensitive option does not help either.