Recomended way for Case Insensitive Index or SearchIndex
Say for a table "users" with a field username, What is the recomended way to query for records matching username(in case insensitive way) ?
We want to preserve the case the username was chosen by the user but the same username should not be taken by others.
7 Replies
Thanks for posting in <#1088161997662724167>.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.
- Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
- Use search.convex.dev to search Docs, Stack, and Discord all at once.
- Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI.
- Avoid tagging staff unless specifically instructed.
Thank you!
to query the username if its already exist is not good enough?
@jamalsoueidan The question is about making a case-insensitive comparison. When the indexed username is "Username" and @SamD wants to prevent a new user from using "userName", how do you make the comparison in an indexed query?
My first thought would be to create an extra field with the username saved in lowercase and index on that instead of the primary username field. (I'm new to Convex so I'm not sure if there's a more efficient approach.)
Convex Ents - Convex Ents
Relations, default values, unique fields and more for Convex
Did you look at this?
`
Maybe it does what you need.
@Clever Tagline creating a new field to store lowercase is my back up option. I am slightly hesitant to do this since this can become a patten through our app.
@jamalsoueidan let me check that unique prop.
yeah, I'd recommend storing
caseInsensitiveUsername in a separate field and indexing on that.
that way, you have a lot of control over what you mean precisely by case insensitivity, which can become pretty complicated if you want to support different languages.
- upper and lower case can be different in different languages (notably, lowercasing I is different in turkish)! see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/toLocaleLowerCase
- you may also want to do unicode normalization if developers are entering text on different operating systems (see normalizing to NFC in https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/normalize).
you may not need to handle all of these complexities up front, but having a well-defined separate uniqueness key will make handling this stuff easier later on.MDN Web Docs
String.prototype.toLocaleLowerCase() - JavaScript | MDN
The toLocaleLowerCase() method of String values returns this string converted to lower case, according to any locale-specific case mappings.