Knex Query with Like + '%' is not working

Question

I have this query that have to select all books filtering by a description ignoring uppercase/lowercase.

So I make this query in adonis.js / node.js:

 const queryBook = Book .query() .with('user') queryBook.where('description', 'like', '%'+bookDescription[0]+'%')

I have records with this bookDescription:

"Espanhol for Students ed.1 "

But when I try to filter using only "es" in lowercase, the knex don't return any record.

When I put "Es", return the book with the description that I put, so, the like %es% is not working.

I put one debug and I catch this:

knex:query select * from "books" where "description" like ? limit ? undefined +7ms knex:bindings [ '%es%', 10 ] undefined +6ms

Apparently I don't find any wrong, but I think the like must return the record in lowercase..

I'm forgetting something?

i also tried with ilike: queryBook.where('description', 'ilike', bookDescription[0]) but i'm still receiving no records — bla
– bla, Commented Nov 4, 2019 at 14:04
can you run the query in your SQL client and see if it returns the results? — Pavan
– Pavan, Commented Nov 4, 2019 at 14:06

Amit Kadivar · Accepted Answer · 2019-11-05 06:27:42Z

You can use like this

const queryBook = Book .query() .with('user') queryBook.where('description', 'like', `%${bookDescription[0]}%`)

or

 const queryBook = Book .query() .with('user') queryBook.where('description', 'ilike', `%${bookDescription[0]}%`)

More Info. view knexjs docuemnts

Ankur Patel · Accepted Answer · 2019-11-05 04:53:46Z

For case insensitive search you can use following like query

const queryBook = Book .query() .with('user') queryBook.whereRaw(`LOWER(description) LIKE ?`, [`%${bookDescription[0]}%`])

krishnazden · Accepted Answer · 2022-06-19 09:02:23Z

if you are using latest knexjs (> 1.x.x) one can use whereIlike or orWhereILike

const result = await select('*').from('books').whereILike('description',`${'%'+searchterm+'%'}`)

Akif Başormancı · Accepted Answer · 2022-06-29 05:14:22Z

0

you can use this best way

knex.whereILike('name',`%${search.toLowerCase()}%`);

answered Jun 29, 2022 at 5:14

211 bronze badge

1 Comment

Welcome to Stackoverflow. Please, always explain your answer.