Because under the hood Wappler uses the knexjs query builder for database queries, it’s not entirely clear to me why Wappler uses whereLike method, but does not use whereILike method, which is more universal?
When using postgresql, this becomes a big problem, because postgresql is case-sensitive, which is why the search works poorly. To get around this, I have to independently make extensions with knex queries in which I use whereILike. But it creates a lot of routine. If the Wappler independently used the whereILike method, I would use the standard Wappler query builder in most cases.
It is only Postgres that uses ILIKE which isn’t a SQL standard. The QueryBuilder in Wappler is used with multiple databases (client and server-side) and for different languages (NodeJS, ASP, ASP.NET, PHP). We decided to only implement features in the QueryBuilder that is supported by all databases so that you don’t have different interfaces for each one. To do more specific database queries you can use Custom Queries.
The knex documentation does not say anything about the fact that the whereILike method can only be used with postgresql databases: https://knexjs.org/guide/query-builder.html#whereilike For this reason, I assumed that they made it universal under the hood, since they usually indicate that the method only works in certain databases if there are any restrictions. But after your message, I doubted, so I just ran tests. The whereILike() method in knex works fine not only with postgresql but also with mysql (and it seems to me that with all other types of DBMS). Therefore, I still don’t understand why not to use this method instead of whereLike().
The knex library is only used in for NodeJS and the other server languages have a different implementation. I checked the docs and they don’t mention on which databases it will work. They added the feature for ILike somewhere last year.
So, I checked the source code, and I actually don’t like how they implemented it. Here are my findings:
Postgres all should be working fine; they just generate the ILIKE query as expected.
SQLite3 will throw an error, no support there.
MySQL the whereILike method is actual the like statement and they changed the whereLike method by adding COLLATE utf8_bin to the statement to make it case sensitive.
MSSQL they seem to have another solution which I think is questionable. The whereLike uses collate SQL_Latin1_General_CP1_CS_AS and the whereILike uses collate SQL_Latin1_General_CP1_CI_AS. This will make the Like case sensitive and ILike case insensitive, but also will not support Unicode since they use the Latin1 charset.
It’s nice that they added ILike as a method for Postgres, but they also changed the default behavior of the other databases to mimic Postgres. Their implementation actually brakes the expected behavior for MySQL and MSSQL where you set the Case Sensitivity of the table with the collation. When you set the table collation in MySQL to utf8_bin it will be case-sensitive, even when using whereILike.
They should have just opted to use lower() and add a notice about performance and the need to create specific indexes. Or add both strategies and let users decide.
That way you could have added in the query builder functions/formatters for the conditions and would have been SQL-compliant.