Prisma1: Add case sensitive/insensitive querying

[sapkra]

Right now we are implementing a search field in our frontend but we noticed that it's not possible to have case insensitive queries. So everything you are typing in our search field has to be case sensitive. That's a big problem!

Solution
A solution would be to have a new field for the where attribute e.g. the name_contains field should also have name_contains_case_insensitive.
Maybe there are better solutions but this was my first idea.

This problem was also noticed in #3301.

[nsignes]

I also encountered that need for an entity lookup feature. Adding this in would help a lot, as we need to have both sensitive and insensitive query options, which blocks the easy way out (DB collation).

[SpaceK33z]

I have the same problem too.

Django names this filter "icontains", e.g. name_icontains. Might also be a good name for Prisma.

[mattdamon108]

I have the same issue here.
As @SpaceK33z raised the e.g. Django filter property, Prisma needs one.

[chuckntaylor]

I have hit the same issue myself for a project I'm starting, in which product names need to be searchable in a case insensitive fashion. Is there any sort of manual workaround in the meantime?

[sapkra]

Yes there are some hacks like using a string with all searchable information chained & lowercase. After doing that you have to lowercase the search query and use searchString_contains.

Don't forget to update the string while you are running mutations.

[mattdamon108]

Is there any hacky way to resolve this?
I have data such as John in DB field. How can I run query and filter for this kind of data in DB?

If I tried to change my args in resolvers to uppercase or lowercase, it can be matched with first input letter, but no way after second letter input.

someResolver: (root, args, ctx, info) => {
  const match = ctx.prisma.users({
    where: {
      OR: [
            { name_starts_with: args.search.toUpperCase() },
            { name_starts_with: args.search.toLowerCase() }
          ]
        }
    }
  })
}

[Putnam14]

A work around I'm implementing right now is having a separate Index type for whatever you're going to be querying, and store the relevant data there lowercased. Then you can search with .toLowerCase() on your args.

In my case, I have a Book type with author, title, isbn, description, etc. I have a BookIndex with the author, title, and isbn, author and title being case insensitive. I retrieve the ISBN from there and then search query for the actual data I want.

E: If you want to see more on how I implemented the work-around, I wrote a blog post about it: https://bridgerputnam.me/blog/case-insensitive-search-with-prisma-using-indexes

[williamluke4]

Hey @schickling, Has there been any progress on this or at least a recommended workaround?

[williamluke4]

This is what I'm currently using

const variables = {
  where:{
    OR: [
      {name_contains: inputValue},
      {name_contains: inputValue.toLowerCase()},
      {name_contains: inputValue.toUpperCase()},
      {name_contains: titleCase(inputValue)},
    ]
  }
}

export function titleCase(str: string):string {
  let string = str.toLowerCase().split(' ');
  for (var i = 0; i < string.length; i++) {
    string[i] = string[i].charAt(0).toUpperCase() + string[i].slice(1); 
  }
  return string.join(' ');
}

[iRoachie]

@schickling Any updates on this? Kind of lacking when you compare to other ORMs that simulate "LIKE" statements in MySQL.

[schickling]

That's a great point. Thanks a lot for pinging me @iRoachie. We'll make sure to consider this for the upcoming client API redesign in Prisma 2.

[chemicalkosek]

Ok, everybody here wrote that it doesn't support case insensitive search.
But in my case, it's actually working on the Prisma demo servers (which internally use MySQL)
But when I have set up my own Prisma Server based on PostgreSQL (using Dokku on Digital Ocean - I couldn't deploy with MySQL - that's another issue) - it doesn't work anymore and it's only case sensitive.
What gives?

My search is actually straightforward:

#schema.graphql
type Query {
...
offers(where: OfferWhereInput, orderBy: OfferOrderByInput): [Offer]!
...
}

#resolvers/Query.js
const { forwardTo } = require("prisma-binding");
const Query = {
 ...
  offers: forwardTo('db'),
 ...



md5-1b3e8ee41bf82dc73c569279a130cf80



# React Component
const SEARCH_OFFERS_QUERY = gql`
  query SEARCH_OFFERS_QUERY($searchTerm: String!) {
    offers(
      where: {
        OR: [
          { contractorName_contains: $searchTerm }
          { contractorCity_contains: $searchTerm }
          { offerName_contains: $searchTerm }
        ]
      }
    ) {
      id
      contractorName
      offerName
      contractorCity
    }
  }
`;

[leafyshark]

I would like to know how queries can be case insensitive. Any news on this?

[iRoachie]

Doing a workaround at the moment by storing an extra variable for every param I'll expose to search.

[leafyshark]

@iRoachie interestingly enough, I have decided to do the same with the index as suggested by @Putnam14. However, slightly different I think.

I essentially create a relationship between Item and ItemIndex whilst exposing params like you said, like so:

type ItemIndex {
  id: ID! @id
  title: String
  description: String
  image: String
  largeImage: String
  price: Int
  createdAt: DateTime
  item: Item! @relation(link: INLINE)
}

In ItemIndex, I only store the { title } in lower case as well as the { item { id } }. Then I have written a Query which queries each Item based on the relationship of ItemIndex to Item using the id. I do so like this:

    const arr = []
    for (var {
      item: { id }
    } of itemIndexes) {
      const item = await ctx.prisma.query.item({
        where: { id }
      });
      arr.push(item);
    }

The query on the client side has all the params necessary, and returning the array with those params enables me to expose them all on the front end. Whether or not this is the most efficient way of doing things I do not know, but I'd like to know if you have any idea 😄

[chenzhendong]

Case incensitive query is a critical feature
1) Standard GraphQL demo server support case incesitive query by _contains.
2) User inputs are always messy, wihtut ambigous match, it is hard for developer implement some features.
Please consider adding it as eariler as possible.

[ZenSoftware]

I've had to circumvent this by creating an additional field that my resolver copies and transforms to lowercase. I then do queries over that field utilizing a search parameter that is transformed to lowercase as well. This approach is a bit of a pain to maintain, and is error prone. It is possible to have stale data if you forget to update the extra field during mutations.

Case insensitive querying would solve all of these issues. This feature is at the top of my wish list.

Though, if we are going to dream about features... being able to to do something similar to SQL WHERE LIKE would open up so many doors. Though I understand that dealing with character encodings is a developers worst nightmare. Prisma was designed to be database agnostic. Doing complex string queries over a diverse set of character encodings may not have a solution.

[AndreiBacescu]

Hello everyone!
What about using raw queries? I think is the best solution for now.

[debianmaster]

facing same issue, any updates on this?

[AshkanHovold]

We just had the same problem in our app that uses GraphQL with Apollo and Neo4j. We will be writing custom queries for our search queries to get around this.

[ericauv]

Any update on when this will be available?

[frandiox]

As mentioned before, it works correctly in demo servers with MySQL but doesn't in Postgres.

@schickling Will this be considered as a bug to be fixed in Prisma v1 under maintenance mode?

[debianmaster]

we had to all kinds of hacks (raw queries) to get this case sensitive search working.

[nealoke]

Sees this issue, dies internally

Prisma is great and thanks for the hard work but this seems a very needed functionality 😢

[debianmaster]

not complaining, but we are thinking to get away from prisma as this needs a lot of hacks for our code. i wish there was a workaround.

[sapkra]

@schickling Do you have an update on this topic because the link you've provided to the API redesign for prisma 2 is not available anymore and this feature is still not implemented in prisma 2?

[omarragi]

Im using prisma with mongo connector.
Is it possible to use mongoose is case insensitive query. ? Would that be a solution ?

[williamkwao]

This feature is really needed. I don’t mind taking a stab and making a or for this if the Prisma team is willing

[justicejoe]

Any idea on this ? Is it work now ?

[williamluke4]

@williamkwao If you have a go at it, I'll help get it reviewed and merged

[nfabacus]

It has been more than 1 year since this thread was created but I see no clear solution from Prisma...
As debianmaster said, it is very disappointing...

I have many things related to my items I need to search case-insensitively - Items with Category, User, ProgressRecords and its Tally ,etc.
It is not practical for me to create and manage duplicates of everything in lowercase in my case.

I am now trying to use rawAccess option (I am using Postgres for databse). However, I found that all the relationships are managed solely on mapping tables in database without any indexing in each object tables due to the prisma database structure.
To get all the things in relationship to my item (before making WHERE query), I discovered that I have to make a complicated sql query (from my SQL novice point of view) using a series of 'LEFT JOIN' with all individual tables and mapping tables as organised by Prisma.
This is do-able, but this defeats the whole purpose of Prisma....not to access database directly. But, there is no other way around for me at this point...

Also, I have to be very careful accessing my database directly for search as I have to consider SQL injection issue...

[nfabacus]

As another similar option, Ben Awad has this youtube video explaining another solution to connect database directly. https://www.youtube.com/watch?time_continue=12&v=YUjlBuI8xsU&feature=emb_logo
But, this is again another extra effort to achieve the feature Prisma is lacking at the moment.

Update (20 Dec 2019):
I ended up using this Ben Awad's solution to access the postgres instance and making sql queries directly to my database, which let me access the full capability of the postgres whereas rawAccess doesn't seem to - such as an out of box way to prevent SQL injection.
Along the way, I also discovered I cannot make query based on null value with prisma, which yet again I solved using the direct access to my database...

[justicejoe]

add it in prisma2 pls

[sapkra]

FYI @herbertpimentel opened an issue for the photon project of prisma 2.
https://github.com/prisma/photonjs/issues/343

I think it would be great if everybody would upvote and follow/subscribe this one.

[jayktaylor]

Please add this into Prisma 1. I can't migrate to Prisma 2 yet as there are missing features that I am using.

[analoguezone]

What is the best practice for this in 2021?

[sapkra]

@analoguezone To migrate to prisma 2 which now has this capability.

[svnshikhil]

Try mode
@sapkra @williamluke4

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
      mode: "insensitive", // Default value: default
    },
  },
});

[chemicalkosek]

@svnshikhil This is an issue for Prisma 1

[esteban-gs]

Try mode
@sapkra @williamluke4

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
      mode: "insensitive", // Default value: default
    },
  },
});

It worked for me. Thanks!