Repository: knex/knex
Pull request #5687 (2023-09-13 19:37:52)

Fix whereILike issue with sqlite (#5604)
- This PR is an attempt to fix whereILike query with sqlite mentioned in here https://github.com/knex/knex/issues/5604#issue-1760674369
- The change is not big, but I need some help with writing tests before merging

kibertoad: @mustafabalila I'm happy to help with tests, what can I do?
kibertoad: @mustafabalila Using "LIKE" should be fine, SQLite LIKE is case-insensitive unless compiled with custom flags. Let me take a look which tests fail in CI now
coveralls: 
[![Coverage Status](https://coveralls.io/builds/64319123/badge)](https://coveralls.io/builds/64319123)

coverage: 92.788% (+0.001%) from 92.787%
when pulling **ce722ae132f022a4c6554879fdbaeef766c88902 on mustafabalila:fixing-whereILike-in-sqlite-issue-5604**
into **6243f6cbd5421c9db046979b60fe8661b523581f on knex:master**.

mohammedbalila: Hey @kibertoad 
Is this PR and PR-[5761](https://github.com/knex/knex/pull/5761) still relevant?

If the work on these two PRs is okay but the merge is delayed for any reason, I think I can pick something else, but if you think they need more work, please let me know
kibertoad: @rluvaton Could you please take a look?
rluvaton: Yes, will look at it in few hours, not near a computer
rluvaton: if I'm not responding, ping me on [twitter](https://twitter.com/rluvaton), I have a lot of GitHub notification

mohammedbalila: > if I'm not responding, ping me on [twitter](https://twitter.com/rluvaton), I have a lot of GitHub notification

DM is closed
rluvaton: > > if I'm not responding, ping me on [twitter](https://twitter.com/rluvaton), I have a lot of GitHub notification
> 
> 
> 
> DM is closed

Fixed
mohammedbalila: Thanks @rluvaton
No worries, hopefully we'll meet in another PR

Patch:
diff --git a/lib/dialects/sqlite3/query/sqlite-querycompiler.js b/lib/dialects/sqlite3/query/sqlite-querycompiler.js
index 47b282eb..0d853976 100644
--- a/lib/dialects/sqlite3/query/sqlite-querycompiler.js
+++ b/lib/dialects/sqlite3/query/sqlite-querycompiler.js
@@ -329,6 +329,13 @@ class QueryCompiler_SQLite3 extends QueryCompiler {
   onJsonPathEquals(clause) {
     return this._onJsonPathEquals('json_extract', clause);
   }
+
+  whereILike(statement) {
+    return `${this._columnClause(statement)} ${this._not(
+      statement,
+      'like '
+    )}${this._valueClause(statement)}`;
+  }
 }
 
 module.exports = QueryCompiler_SQLite3;


Test patch:
diff --git a/test/integration2/query/select/where.spec.js b/test/integration2/query/select/where.spec.js
index d94e18b9..da18a264 100644
--- a/test/integration2/query/select/where.spec.js
+++ b/test/integration2/query/select/where.spec.js
@@ -556,7 +556,14 @@ describe('Where', function () {
 
       describe('where like', async function () {
         beforeEach(function () {
-          if (!(isPostgreSQL(knex) || isMssql(knex) || isMysql(knex))) {
+          if (
+            !(
+              isPostgreSQL(knex) ||
+              isSQLite(knex) ||
+              isMssql(knex) ||
+              isMysql(knex)
+            )
+          ) {
             return this.skip();
           }
         });
@@ -630,10 +637,23 @@ describe('Where', function () {
           expect(result[7].email).to.equal('test8@example.com');
         });
 
-        it("doesn't find data using whereLike when different case sensitivity", async () => {
+        it("doesn't find data using whereLike when different case sensitivity", async function () {
           const result = await knex('accounts').whereLike('email', 'Test1%');
+          // sqlite only supports case-insensitive search
+          if (isSQLite(knex)) {
+            this.skip();
+          }
           expect(result).to.deep.equal([]);
         });
+
+        it('supports only case-insensitive searches in sqlite', async function () {
+          const result = await knex('accounts').whereILike('email', 'Test1%');
+          if (!isSQLite(knex)) {
+            this.skip();
+          }
+          expect(result.length).to.equal(1);
+          expect(result[0].email).to.equal('test1@example.com');
+        });
       });
 
       it('Retains array bindings, #228', function () {
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index cc3b160d..12c8ad16 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -879,6 +879,10 @@ describe('QueryBuilder', () => {
         sql: 'select * from [users] where [name] collate SQL_Latin1_General_CP1_CI_AS like ?',
         bindings: ['luk%'],
       },
+      sqlite3: {
+        sql: 'select * from `users` where `name` like ?',
+        bindings: ['luk%'],
+      },
     });
   });
 
@@ -903,6 +907,10 @@ describe('QueryBuilder', () => {
           sql: 'select * from [users] where [name] collate SQL_Latin1_General_CP1_CS_AS like ? and [name] collate SQL_Latin1_General_CP1_CS_AS like ? or [name] collate SQL_Latin1_General_CP1_CS_AS like ?',
           bindings: ['luk1%', 'luk2%', 'luk3%'],
         },
+        sqlite3: {
+          sql: 'select * from `users` where `name` like ? and `name` like ? or `name` like ?',
+          bindings: ['luk1%', 'luk2%', 'luk3%'],
+        },
       }
     );
   });
@@ -928,6 +936,10 @@ describe('QueryBuilder', () => {
           sql: 'select * from [users] where [name] collate SQL_Latin1_General_CP1_CI_AS like ? and [name] collate SQL_Latin1_General_CP1_CI_AS like ? or [name] collate SQL_Latin1_General_CP1_CI_AS like ?',
           bindings: ['luk1%', 'luk2%', 'luk3%'],
         },
+        sqlite3: {
+          sql: 'select * from `users` where `name` like ? and `name` like ? or `name` like ?',
+          bindings: ['luk1%', 'luk2%', 'luk3%'],
+        },
       }
     );
   });