Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add case sensitive/insensitive querying #3611

Open
sapkra opened this Issue Nov 27, 2018 · 9 comments

Comments

Projects
None yet
9 participants
@sapkra
Copy link

sapkra commented Nov 27, 2018

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

This comment has been minimized.

Copy link

nsignes commented Nov 28, 2018

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

This comment has been minimized.

Copy link
Contributor

SpaceK33z commented Dec 5, 2018

I have the same problem too.

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

@mattdamon108

This comment has been minimized.

Copy link

mattdamon108 commented Dec 28, 2018

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

@chuckntaylor

This comment has been minimized.

Copy link

chuckntaylor commented Dec 28, 2018

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

This comment has been minimized.

Copy link
Author

sapkra commented Dec 28, 2018

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

This comment has been minimized.

Copy link

mattdamon108 commented Dec 28, 2018

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

This comment has been minimized.

Copy link

Putnam14 commented Jan 16, 2019

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

This comment has been minimized.

Copy link

williamluke4 commented Mar 1, 2019

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

@williamluke4

This comment has been minimized.

Copy link

williamluke4 commented Mar 8, 2019

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(' ');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.