Search mode "exactMatch" - PostgreSQL - Equal operator (possible fix) [Doc]

[robbykrlos]

This is a documentation ticket.

Description

We are trying out PostgreSQL lately, and we found some slight deviations from MySQL. One of them, was experienced in the table's search modes - "exactMatch".

This is a ticket describing our solution for a particular issue with casts and "=" operator on PostgreSQL so that others in the same situation have something to start with.

Something interesting we found is that by default (our of the box Laravel + Laravel Enso) queries generated for Table search modes "contains", "startWith", "endsWith" were casting automatically the columns to text:

...
OR "table"."integer_column"::text ILIKE '%1234%' 
OR "table"."inet_column"::text ILIKE '%1234%'

The particular case of 'exactMatch' where the operator "=" was used, had some difficulties for our PostgreSQL env, due to type mismatch.

Strings passed as search value that reached where conditions using "=" sign will not be treated for type match.
Ex:
– If value is 123 and it will be compared to a varchar/text column, it will be placed under single-quotes : '123'.
– If value is abc and it will be compared to a integer column, it will not be placed under single-quotes, it will be used as-is abc.
– If value is 123 and it will be compared to a inet column, no matter how it will be used (with or without ') it will fail due to pattern not matching IPv4/v6.

In all these 3 situations the Enso table will fail with an error due to the queries being generated incorrectly for PostgreSQL's syntax.

Solution

Because the "=" operator will not deal with casting data to proper type for correct type comparison, we had to find a solution. And the solution we adopted is to overwrite the LaravelEnso\Filters\Enums\ComparisonOperators with our own 'flavor'

class ComparisonOperators extends EnsoComparisonOperators
{
    public const Equal = 'ILIKE';
}

(extended Enso's class + AppServiceProvider binding)

Which resulted in the end with a similar behavior - exact match was working since wildcard '%' is not used, and automatic casting to text is done for both column and value.

Note that there may be some exception / special situations depending on your database.

Ex: inet column type does not cast 1:1 to text. This means that IP::text where IP=1.2.3.4 will not result after cast as 1.2.3.4. It will end up as 1.2.3.4/32. And probably there are some other situations where casting to text will result in a different result compared to initial value.

We've tried in a limited time to work out this problem from the core, by keeping the "=" operator, and dealing with correct casting, but fist, it defeated the purpose of strict "=" comparator from PostgreSQL, and secondly we did not find an easy fix this.

REFs:

https://laracasts.com/discuss/channels/eloquent/eloquent-passes-values-as-strings-instead-of-integers
https://stackoverflow.com/questions/25008004/laravel-eloquent-converts-query-parameter-to-integer-before-comparison
https://stackoverflow.com/questions/20079320/how-do-i-return-integer-and-numeric-columns-from-mysql-as-integers-and-numerics
https://www.php.net/manual/en/pdo.setattribute.php
laravel/framework#11780

[robbykrlos]

@enso-team, your input is always appreciated, but for the moment I just wanted to help whomever needs this explanation/solution. I'll close this for the moment.