How to use Lucid ORM to find a record by case-insensitive match?
What is your DB management system ?
For example with MySQL, it is case-insensitive by default, so you have no extra work for your queries.
Well, i created my own approach using a PostgreSQL functions, then, i call them in my code with
await Database .raw('select * from users where username = ?', [username])
But, instead of the select, i call my function.
unaccent extension in PSQL too, you can use it for searches without accents:
await Database.raw(`CREATE EXTENSION IF NOT EXISTS unaccent;`)
Maybe the unaccent option is great only for me, my Db main language is pt-br, we have a lot of accents in our grammar.
For cleaning the search string, i use the following function:
await Database.raw(` CREATE OR REPLACE FUNCTION clean_some(text) RETURNS text AS $$ SELECT replace(replace($1, ' ',''),'-','') $$ LANGUAGE sql;`)
You can combine them, to have a good search. I use both for different cases…
I put all these functions inside migrations/seeds folder, so, they run in every app deploy.