Hi All
I need to do a case insensitive LIKE query in Postgres yet I have no options to use ILIKE, SIMILAR or the lower() option.
Example a search for “remove ms” should find “Remove MS”, “remove Ms”, “REMOVE MS”.
Note: No I can’t preset the string on save as it is free text storage - the can type in anything.
Any ideas?
Custom query?
Erm what do you mean custom query? Where do I do that?
BTW thanks for replying
My man 
So I looked at the custom query and it is great. The problem however is that we need the paged query and have a lot of conditions to apply against the query which means I will end up with lots of custom queries and it will become a mess.
@patrick @George Is there a way for me to map the functionality into the paged query? Like add to the dropdown another function iContain?
I did a temp hack and it works fine for postgres, I know it is not ideal as it will be overwritten by the update.
I changed the db.js file in core to:
if (rule.operator == 'between') {
this[where + 'Between'](column, rule.value);
} else if (rule.operator == 'not_between') {
this[where + 'NotBetween'](column, rule.value);
} else if (rule.operator == 'is_null') {
this[where + 'Null'](column);
} else if (rule.operator == 'is_not_null') {
this[where + 'NotNull'](column);
} else if (rule.operator == 'in') {
this[where + 'In'](column, rule.value);
} else if (rule.operator == 'not_in') {
this[where + 'NotIn'](column, rule.value);
} else if (rule.operator == 'begins_with') {
this[where](column, 'ilike', rule.value + '%');
} else if (rule.operator == 'not_begins_with') {
this[where + 'Not'](column, 'ilike', rule.value + '%');
} else if (rule.operator == 'ends_with') {
this[where](column, 'ilike', '%' + rule.value);
} else if (rule.operator == 'not_ends_with') {
this[where + 'Not'](column, 'ilike', '%' + rule.value);
} else if (rule.operator == 'contains') {
this[where](column, 'ilike', '%' + rule.value + '%');
} else if (rule.operator == 'not_contains') {
this[where + 'Not'](column, 'ilike', '%' + rule.value + '%');
} else {
this[where](column, rule.operation, rule.value);
}The ILIKE is a PostgreSQL extension and not in the SQL standard. We want to support the different databases with the query builder. If you want to use database specific features I would recommend to use custom queries instead. We will not be adding this feature in the query builder.
Could you not add specific features and show them only if the database connection of the query is of type, PostgreSQL here?
Or add a hook that run after Wappler files to allow us to override knex query builder?
@patrick, +1 for @Eldynn request (add custom threatment to query builder).
I have paged queries that is hard to make by hand via Custom Queries and I (we) want to search case insensitive.
I think that the best possible workaround is to edit the Server Action JSON file in order to take advantage of default behavior in db.js:
...
} else if (rule.operator == 'contains') {
this[where](column, 'like', '%' + rule.value + '%');
} else if (rule.operator == 'not_contains') {
this[where + 'Not'](column, 'like', '%' + rule.value + '%');
} else {
this[where](column, rule.operation, rule.value);
}
So, I have modified my Contain Clause from:
{
"id": "users.name",
"field": "users.name",
"type": "string",
"operator": "contains",
"value": "{{$_GET.search.default('%')}}",
"data": {
"table": "users",
"column": "name",
"type": "text"
},
"operation": "LIKE"
}
Changing operator to “custom”, operation to “ILIKE” and added “%” to value begin and end:
{
"id": "users.name",
"field": "users.name",
"type": "string",
"operator": "custom",
"value": "{{'%' + $_GET.search + '%'}}",
"data": {
"table": "users",
"column": "name",
"type": "text"
},
"operation": "ILIKE"
}
Query Builder doesn’t support this, actually:
So, if you allow adding custom operator OR a raw query clause in conditions tab would be awesome!
+1 here! Just did what @Ray suggested and it’s working… would be great to have these postgres operators in the query builder.
Problem is that db.js can be overwrited easily in an update. I opted to modify the json action instead because of this.
Does this mean that, if using Postgres as my database, I would have to write custom queries every time I want my app to perform a case insensitive search?
Put another way…does Wappler not support case insensitive queries for Postgres out of the box?
I ask because I think most of the querying my app’s users would be doing would need to be case insensitive. Like if they search for another user by name, I wouldn’t want that search to be case sensitive. Some users might capitalize the first letters of the person’s first name and last name when searching…and some might just type the name into the search field in all lowercase.
The best workaround for now I think is modify the Server Action json:
Save and close the Server Action
Open the Server Action in Code View, locate the “wheres” clause:
If you open the Server Action again you’ll see that the operation remains “=”, but in the code it is ILIKE, and even if you save it, it will stay ILIKE.
@patrick @George, can you please reflect this operation in UI to not need this workaround?
This is a handy, thanks.
@George how about make it so we can extend db.js with custom code that gets injected on launch, much like we can do with extensions?
That way you can carry on sticking to boring MySQL and the rest of us can use more fun databases like Postgres and Mongo 
@patrick will see if we can add support for case insensitive search in a more generic way and that we can support ILIKE as well out of the box.
