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
15

Hello @Sebastian and @ClaytonMoore

I’ve carefully reviewed this thread, and I don’t understand what I’m doing wrong. None of the things mentioned above are working for me.

An example of my test schema, which has just one string field, “stringField”:

{
    "previewUrls": {},
    "properties": {
        "validateOnPublish": false
    },
    "scripts": {},
    "isPublished": true,
    "fieldRules": [],
    "fieldsInLists": [],
    "fieldsInReferences": [],
    "fields": [
        {
            "name": "stringField",
            "properties": {
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "fieldType": "String",
                "createEnum": false,
                "editor": "Input",
                "inlineEditable": false,
                "isEmbeddable": false,
                "isUnique": false,
                "contentType": "Unspecified"
            },
            "isLocked": false,
            "isHidden": false,
            "isDisabled": false,
            "partitioning": "invariant"
        }
    ],
    "type": "Default"
}

Example test content:

{
  "data": {
    "queryTestContents": [
      {
        "id": "4459f5c3-92c9-4555-a4fb-56d20cca6633",
        "flatData": {
          "stringField": "Test"
        }
      },
      {
        "id": "e703eecb-deab-43bc-bc5e-bbe4028cdff4",
        "flatData": {
          "stringField": "test"
        }
      },
      {
        "id": "85a2be10-d39a-4ac5-aefb-5585d2b4fe39",
        "flatData": {
          "stringField": "TEST"
        }
      },
      {
        "id": "41464cd7-0d95-487c-a581-d25daaa42307",
        "flatData": {
          "stringField": "tEST"
        }
      }
    ]
  },
  ...
}

You claim that the eq operator should be case-insensitive, but that’s not the case:

Query:

{
  queryTestContents (filter: "data/stringField/iv eq 'test'") {
    id
    flatData {
      stringField
    } 
  }
}

Response:

{
  "data": {
    "queryTestContents": [
      {
        "id": "e703eecb-deab-43bc-bc5e-bbe4028cdff4",
        "flatData": {
          "stringField": "test"
        }
      }
    ]
  },
  ...
}

Query:

{
  queryTestContents (filter: "data/stringField/iv eq 'Test'") {
    id
    flatData {
      stringField
    } 
  }
}

Response:

{
  "data": {
    "queryTestContents": [
      {
        "id": "4459f5c3-92c9-4555-a4fb-56d20cca6633",
        "flatData": {
          "stringField": "Test"
        }
      }
    ]
  },
  ...
}

And so on.

You also mention that the tolower function can be used, but that doesn’t work either:

Query:

{
  queryTestContents (filter: "tolower(data/stringField/iv) eq 'Test'") {
    id
    flatData {
      stringField
    } 
  }
}

Response:

{
  "errors": [
    {
      "message": "OData operation is not supported for query: ?$skip=0&$filter=tolower(data/stringField/iv) eq 'Test'.",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "queryTestContents"
      ],
      "extensions": {
        "code": "VALIDATION",
        "codes": [
          "VALIDATION"
        ]
      }
    }
  ],
  "data": {
    "queryTestContents": null
  },
 ...
}

Please help me understand what I’m doing wrong. How can I achieve case-insensitive queries with the eq operator in our Squidex instance? Our build version is: squidex:dev-8285.

16

I think tolower has never been implemented. But there should be a regex / match filter.

17

Hello @Sebastian, thanks for rapid reply. I’m not sure I understand what you mean. What I try to achieve is to do case-insensitive equality comparison. Could you please show an example of what you mean by regex / match filter?

18

You could do something like

$filter=matchs(data/field/iv, '/^hello$/i') // Case insensitive

From: Queries | Squidex

1 Like
19

@Sebastian That works, thanks a lot!

Query
{

  queryTestContents (filter: "matchs(data/stringField/iv, '/test/i')") {
    id
    flatData {
      stringField
    } 
  }
}

Response

{
  "data": {
    "queryTestContents": [
      {
        "id": "4459f5c3-92c9-4555-a4fb-56d20cca6633",
        "flatData": {
          "stringField": "Test"
        }
      },
      {
        "id": "e703eecb-deab-43bc-bc5e-bbe4028cdff4",
        "flatData": {
          "stringField": "test"
        }
      },
      {
        "id": "85a2be10-d39a-4ac5-aefb-5585d2b4fe39",
        "flatData": {
          "stringField": "TEST"
        }
      },
      {
        "id": "41464cd7-0d95-487c-a581-d25daaa42307",
        "flatData": {
          "stringField": "tEST"
        }
      }
    ]
  },
 ...
}
1 Like