Case insensitive query with Lucid (PostgresSQL)

[kaposke]

Hey!

I'm using PostgresSQL, and I'm trying to do a case insensitive where. Here is what would work for MySQL:
query.where('name', 'like', '%${term}%')

A common solution on a raw query would be to do:
SELECT * FROM table WHERE LOWER(name) LIKE LOWER('%Kaposke%')
but I'm trying to avoid raw queries as they are unsafe.

I got around the problem using Regular Expressions, but I really don't like the looks of it, and there is probably a better way to do it.
query.where('name', '~*', '.*${term}.*')

Thank you in advance.

[thetutlage]

Nope, raw queries are equally safe. You just need to use bind parameters when defining a raw where clause. Infact, the core of the framework uses the lower method. Here's the source https://github.com/adonisjs/lucid/blob/develop/src/Bindings/Validator.ts#L168

[lucasdoamaral]

Since you're using PostgreSQL you could use the insensitive operator ilike:

query.where('name', 'ilike', '%${term}%')

[kaposke]

Marking this as the answer instead, since it was the part I was missing about Postgres. Thanks your reply.

[roneydourados]

Hey, thanks, your saved my life!! kkkkk

[gusflopes]

just got saved by this also. lol

[georgebsmith-tech]

And saved me also