Add new filters for String types to enable case-insensitive filtering

[andreloeffelmann]

Is your feature request related to a problem? Please describe.
When filtering for fields of type String in queries the passed filter values are compared case-sensitive to the database values, see here.
It is common (at least for our data) that some values are stored with uppercase letters in the database.
When querying the API our users are often not aware of how the data is actually written (uppercase/lowercase) which leads to zero rows being returned by the filtering if the passed values do not match the underlying data exactly.

Describe the solution you'd like
It would be great if the existing String filters could be extended, e.g. like this:

• [fieldName]_IGNORING_CASE
• [fieldName]_STARTS_WITH_IGNORING_CASE
• [fieldName]_ENDS_WITH_IGNORING_CASE
• [fieldName]_CONTAINS_IGNORING_CASE
• [fieldName]_IN_IGNORING_CASE

Or maybe just with an abbreviation, like [fieldName]_IN_IC, I don't care too much about the actual spelling.

To be clear: the existing filters

• [fieldName]
• [fieldName]_STARTS_WITH
• [fieldName]_ENDS_WITH
• [fieldName]_CONTAINS
• [fieldName]_IN

should remain. They still make sense for some usecases.

If schema bloating is a concern here, maybe it is an idea to enable the additional case-insensitive filters by a directive or the global configuration...

Describe alternatives you've considered
We evaluated the _MATCHES filter via regex. Even if this can solve our problem, we hesitate to enable this filter because of the ReDoS attack possibilities as stated in your docs.

Additional context
This may also solve #4117

[btroop]

extending this to sorting could be great as well...since sorting usually prioritizes uppercase over lowercase

[andreloeffelmann]

Hey @darrellwarde, any plans on implementing this?
Some time has been passed since my initial post and no one ever replied.
The related ticket #4117 has been labelled with small, so I guess this should not be THAT troublesome to implement?

[darrellwarde]

Hey @darrellwarde, any plans on implementing this? Some time has been passed since my initial post and no one ever replied. The related ticket #4117 has been labelled with small, so I guess this should not be THAT troublesome to implement?

Hey @andreloeffelmann, this hasn't been implemented because with the library's current design, it will just massively blow up schema size. We're currently working on major releases where we aim to address this, and we will revisit features such as these at that point.

[angrykoala]

Hi. I've been looking into this for version 7, in this version we have changed our filters, giving us more flexibility to add case insensitive filters without incurring into a large schema size increase.

Proposal

The goal is to allow filtering on strings ignoring casing To do this a new intermediate input field will be added to StringScalarFilters to act as a "modifier". This type contains all the valid filters for a case insensitive string.

API

In version 7.x, the string filters look like this:

input StringScalarFilters {
  eq: String
  in: [String!]
  contains: String
  endsWith: String
  startsWith: String
}

A new field caseInsensitive or ignoreCase could be added to this type (The rest of this design will assume ignoreCase). This field will be of a new type CaseInsensitiveStringScalarFilters containing the same filters, except ignoreCase:

input StringScalarFilters {
  eq: String
  in: [String!]
  contains: String
  endsWith: String
  startsWith: String
+  ignoreCase: CaseInsensitiveStringScalarFilters
}

+ input CaseInsensitiveStringScalarFilters {
+   eq: String
+   in: [String!]
+   contains: String
+   endsWith: String
+   startsWith: String
+ }

An example of these filters:

where: {
   title: {
      ignoreCase: {
         eq: "the matrix"
       }
   }
}

The new type CaseInsensitiveStringScalarFilters does not contain ignoreCase to disallow queries such as:

where: { title: { ignoreCase: { ignoreCase: { ignoreCase: { eq: "The Matrix"} } } }}

In this case, all the intermediate ignoreCase would be no-op.

Opt-in

Case insensitive filters should be an opt-in feature:

  const neoSchema = new Neo4jGraphQL({
      features: {
          filters: {
              String: {
                  CASE_INSENSITIVE: true,
              },
          },
    },
})

Lists

StringListFilters look like this:

input StringListFilters {
  eq: [String!]
  includes: String
}

This Proposal does not include any changes to list types. Although this type could be extended in a similar manner.

Optional String Filters

If other optional string filters are enabled, these must be added to both: StringScalarFilters and CaseInsensitiveStringScalarFilters:

new Neo4jGraphQL({
      features: {
          filters: {
              String: {
                  CASE_INSENSITIVE: true,
                  GT: true,
                  GTE: true,
                  LT: true,
                  LTE: true,
                  MATCHES: true,
              },
          },
      },
  });

input StringScalarFilters {
  eq: String
  in: [String!]
  contains: String
  endsWith: String
  startsWith: String
  caseInsensitive: CaseInsensitiveStringScalarFilters
  gt: String
  gte: String
  lt: String
  lte: String
  matches: String
}


input CaseInsensitiveStringScalarFilters {
  eq: String
  in: [String!]
  contains: String
  endsWith: String
  startsWith: String
  gt: String
  gte: String
  lt: String
  lte: String
  matches: String
}

Aggregations

Filtering strings for aggregations should work with connection filters:

query {
  moviesConnection(where: { title: { caseInsensitive: { eq: "the matrix" } } }) {
    aggregate {
      count {
        nodes
      }
    }
  }
}

Examples

query {
  movies(where: { title: { caseInsensitive: { eq: "the matrix" } } }) {
    title
  }
}

query {
  moviesConnection(where: {
    alternativeNames: {},
    title: {
      caseInsensitive: {
        matches: "ma",
      },
    },
  }) {
    edges {
      node {
        title
      }
    }
  }
}

When the feature is enabled, the type CaseInsensitiveStringScalarFilters is added, along with the ignoreCase field in StringScalarFilters

Cypher

When filtering with case insensitive, the function toLower can be used on both sides of the filter operation:

MATCH (this:Movie)
- WHERE this.title = $param0
+ WHERE toLower(this.title) = toLower($param0)
RETURN this { .title } AS this

Discussion points

• caseInsensitive vs ignoreCase (or any other name)
• Should list filters be added case insensitive options
• CASE_INSENSITIVE as the feature flag

[darrellwarde]

Regarding the proposed syntax:

query {
  movies(where: { title: { caseInsensitive: { eq: "the matrix" } } }) {
    title
  }
}

I will chuck an alternative proposal in the mix here to have something like "property modifiers", for example:

query {
  movies(where: { title: { toLower: { eq: "the matrix" } } }) {
    title
  }
}

I think the readability of the above is just that little bit nicer, and provides the groundwork for similar modifiers for strings or other types in future, without having to modify the input value.

[angrykoala]

There are some details of the API that are still pending discussion, the PR #6250 needs to be merged for this issue to be closed

[angrykoala]

Hi @andreloeffelmann

We have discussed the different API options and the features has been merged. Case insensitive filters will be available in the next 7.x release soon.

Apologies for the confusion earlier