Postgres need ILIKE

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?

1 Like

@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:

  1. First, design what fields will be searched with the GET search param:

  1. Save and close the Server Action

  2. Open the Server Action in Code View, locate the “wheres” clause:

  1. Modify all the operations you need from “=” to “ILIKE”

image

  1. Save the file and test your query.

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?

1 Like

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 :stuck_out_tongue_winking_eye:

@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.

3 Likes

I used MySQL on my projects previously, but for a new one I want to try PostgreSQL.

Turns out, there is a big problem, because case-insensitive search is not available for Postgres in Wappler.

I get that the reason behind it is to keep Query Builder DBMS-agnostic, that’s why it supports only SQL standard procedures.
As Patrik investigated and explained here, the issue is not simple, and the obvious solution has its drawbacks.

I see that users are struggling with different workarounds like using custom queries, making duplicate fields in DB, duplicating filters, etc. For me this is not worth it, so I might just stick with MySQL.

But Postgres is obviously more promising then MySQL and case-insensitive search is a crucial feature for most of the apps.
So I think it is worth finding and implementing a solution.

2 Likes

I had already reported this problem many years ago, as I had no success in explaining I left postgre aside and only use mysql nowadays. If it’s possible to do searches with uppercase and lowercase letters I will go back to using postgre.

2 Likes

Hi Wapplers, any update on this? Case insensitive query is desperately needed for PostgreSQL…

Case-Insensitive Search In Postgres No news so far
But Wappler team in Beta channel introduced ILIKE filtering - https://community.wappler.io/uploads/default/original/3X/8/8/8888ad1afef90fb99acfc2c64cdb4c3b7f97a4ee.png

Oooh nice. Is that for front end only, not server side?

I haven’t tested it by myself, but yes - it’s filtering on client side.