Error when using OData Filter string methods "tolower" and "toupper"

Support
1

I have…

I’m submitting a…

  • [ ] Regression (a behavior that stopped working in a new release)
  • [ ] Bug report
  • [ ] Performance issue
  • [x] Documentation issue or request

Current behavior

Using OData string functions like “toupper()” and “tolower()” returns error “OData operation is not supported for query”

Expected behavior

That toupper() or tolower() would work in OData filter allowing for developer implemented case insensitive comparison.

Minimal reproduction of the problem

Environment

App Name:

  • [ ] Self hosted with docker
  • [x] Self hosted with IIS
  • [ ] Self hosted with other version
  • [ ] Cloud version

Version: [6.7]

Browser:

  • [X] Chrome (desktop)
  • [ ] Chrome (Android)
  • [ ] Chrome (iOS)
  • [ ] Firefox
  • [ ] Safari (desktop)
  • [ ] Safari (iOS)
  • [ ] IE
  • [ ] Edge

Others:
I may be using it wrong, so an example may be all I need. I have no problem using startswith and endswith, just the two methods that manipulate the field.

2

They are not supported. Perhaps we can support it somehow, just for ignore casing. Can you make an example query?

3

We have a user schema where their internal id is a guid, so for us a graphql query would look something like :

queryUsersContents(filter: "tolower(data/internalId/iv) eq '2174ebc8-cf57-43dd-990e-f59b8a5ba07e'"){ id flatData{ displayName internalId } }

I appreciate the quick reply on this, I suppose in the meantime I can add regex validation on the schema to ensure Squidex users input the correct case when adding/editing an internal id.

4

Thanks, but I would only support this

filter: "tolower(data/internalId/iv) eq 'tolower(2174ebc8-cf57-43dd-990e-f59b8a5ba07e')"

or

filter: "toupper(data/internalId/iv) eq 'toupper(2174ebc8-cf57-43dd-990e-f59b8a5ba07e')"
5

I just realized that it is always case insensitive.

6

I can do that as well, in our current implementation the second part of the comparison is converted to lowercase on the client, the OData filter is built with this now lowercase value and sent along with the query to squidex. I would prefer to encapsulate the “tolower” manipulation all inside the filter if possible, just like in your example.

My only concern is that per the OData docs:

According to their examples found on “4.5. Filter System Query Option”
The string methods appear to only work on the first half of the filter.

7

It is case insensitive anyway :wink: … I have also added it to the docs.

8

So lets say a user has an internalId of ‘2174EBC8-CF57-43DD-990D-F59B8A5bA07E’. This query will return the correct user?

queryUsersContents(filter: data/internalId/iv eq '2174ebc8-cf57-43dd-990e-f59b8a5ba07e'"){ id flatData{ displayName internalId } }

9

I am only talking about contains, startswith and endswith

1 Like
10

But you can use matchs operator if you need case insensitive equals.

11

Ahhhh now I get what you’re saying, in regards to this match operator, could you provide an example or a link to one? not finding it on the OData docs.

Thanks again for all the help on this.

12

Yes, I will do that.

13

Done, I have added the matchs operator.

1 Like
14

love it! you’re the man

1 Like