Case insensitive filter #1141

jpenninkhof · 2016-09-13T06:09:21Z

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");

The text was updated successfully, but these errors were encountered:

Michadelic · 2016-09-14T11:30:15Z

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 · 2017-07-25T08:56:02Z

Is there an update about this issue/request?

Cheers, Emanuele.

gregorwolf · 2017-08-01T12:57:41Z

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 · 2017-08-01T13:27:43Z

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 · 2017-08-01T19:44:05Z

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 · 2017-08-16T14:45:11Z

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 · 2017-09-07T13:11:24Z

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 · 2018-09-20T14:26:22Z

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 · 2018-10-17T11:24:23Z

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 · 2018-10-17T12:26:17Z

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 · 2018-10-17T14:02:31Z

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 · 2018-10-17T14:31:23Z

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

zygimantus · 2019-11-23T22:26:19Z

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.

Aftab571 · 2021-05-28T14:45:42Z

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.

Did you find any solution or workaround for it?

uhlmannm · 2021-05-28T17:03:18Z

Hi,

I have build a small test with Northwind and added the following filter.

							oFilter = new sap.ui.model.Filter({
								path : 'ProductName',
								operator : sap.ui.model.FilterOperator.Contains,
								value1 : 'e',
								caseSensitive : false
							});
						
						oBinding.filter(oFilter);

This works like a charm. I have tested OpenUI5 1.60.1, 1.60.36, 1.71.33, 1.84.13 and 1.90.1.
In 1.71.33 I see some errors on the console (Assertion failed: Method 'class' must be called with exactly one class name) but they come from RenderManager and are not related to the filtering.
The generated request is GET Products?$skip=0&$top=20&$filter=substringof(%27E%27,toupper(ProductName)).

What needs to be done to reproduce the errors listed above?

Best regards
Mathias.

codeworrior · 2021-05-28T17:15:31Z

@uhlmannm this is OT, but the assertion failure that you see in 1.71.33 is caused by a bug in the ColumnListItemRenderer. It has been fixed with 497d10a in 1.84.0 and later, but not in 1.71 (internal incident 2080371005).

flovogt · 2022-03-31T07:21:28Z

As @uhlmannm and @ThomasChadzelek already wrote #1141 (comment). It's already supported. So, I close the issue. If it's still relevant, feel free to open a new one by providing a small sample to reproduce the issue.

Michadelic added the enhancement label Sep 14, 2016

flovogt closed this as completed Mar 31, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Case insensitive filter #1141

Case insensitive filter #1141

jpenninkhof commented Sep 13, 2016

Michadelic commented Sep 14, 2016

StErMi commented Jul 25, 2017

gregorwolf commented Aug 1, 2017 •

edited

Loading

StErMi commented Aug 1, 2017 •

edited

Loading

pdominique commented Aug 1, 2017

ManuelB commented Aug 16, 2017 •

edited

Loading

t1mb0b commented Sep 7, 2017

piejanssens commented Sep 20, 2018

mudit1993 commented Oct 17, 2018

StErMi commented Oct 17, 2018

mudit1993 commented Oct 17, 2018

ThomasChadzelek commented Oct 17, 2018

zygimantus commented Nov 23, 2019 •

edited

Loading

Aftab571 commented May 28, 2021

uhlmannm commented May 28, 2021

codeworrior commented May 28, 2021 •

edited

Loading

flovogt commented Mar 31, 2022

Case insensitive filter #1141

Case insensitive filter #1141

Comments

jpenninkhof commented Sep 13, 2016

Michadelic commented Sep 14, 2016

StErMi commented Jul 25, 2017

gregorwolf commented Aug 1, 2017 • edited Loading

StErMi commented Aug 1, 2017 • edited Loading

pdominique commented Aug 1, 2017

ManuelB commented Aug 16, 2017 • edited Loading

t1mb0b commented Sep 7, 2017

piejanssens commented Sep 20, 2018

mudit1993 commented Oct 17, 2018

StErMi commented Oct 17, 2018

mudit1993 commented Oct 17, 2018

ThomasChadzelek commented Oct 17, 2018

zygimantus commented Nov 23, 2019 • edited Loading

Aftab571 commented May 28, 2021

uhlmannm commented May 28, 2021

codeworrior commented May 28, 2021 • edited Loading

flovogt commented Mar 31, 2022

gregorwolf commented Aug 1, 2017 •

edited

Loading

StErMi commented Aug 1, 2017 •

edited

Loading

ManuelB commented Aug 16, 2017 •

edited

Loading

zygimantus commented Nov 23, 2019 •

edited

Loading

codeworrior commented May 28, 2021 •

edited

Loading