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, Nov 04 '19 at 14:04
can you run the query in your SQL client and see if it returns the results? — Pavan, Nov 04 '19 at 14:06
@Jhonny Have you read this question : https://stackoverflow.com/questions/7005302/postgresql-how-to-make-case-insensitive-query — crbast, Nov 04 '19 at 17:55

Amit Kadivar · Accepted Answer · 2019-11-05T06:27:42.577

8

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

edited Nov 05 '19 at 06:27

answered Nov 05 '19 at 04:46

Amit Kadivar

score 4 · Answer 2 · answered Nov 05 '19 at 04:53

4

For case insensitive search you can use following like query

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

answered Nov 05 '19 at 04:53

Ankur Patel

score 0 · Answer 3 · answered Jun 19 '22 at 09:02

0

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

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

answered Jun 19 '22 at 09:02

krishnazden

score 0 · Answer 4 · answered Jun 29 '22 at 05:14

0

you can use this best way

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

answered Jun 29 '22 at 05:14

Akif Başormancı

Welcome to Stackoverflow. Please, always explain your answer. – Rohit Gupta Jul 05 '22 at 13:14

4 Answers4