[LOW] comparisonOperator::ILIKE and 'collation' => 'utf8mb4_unicode_ci'

[robbykrlos]

This is a question.

Hi,

Some observations I made recently, made me wonder...

1. Are you using Postgres? ...because if you set the config/tables.php comparisonOperator to ILIKE (as mentioned in description comments) it will not work in MySQL / MariaDB. I tried to use a more literal value like "like binary" but the vendor/laravel-enso/filters/src/Services/Search.php won't let me:

public function comparisonOperator(string $comparisonOperator): self
    {
        if (! $this->operators::keys()->contains($comparisonOperator)) {
            throw ComparisonOperator::unknown();
        }

Maybe I can update the config documentation (comments) that this ILIKE is only specific to Postgres? Or could we have a pass on the validation? more flexible input? or maybe extend the ComparisonOperators with "like binary"? I can contribute in any direction if you feel it will help.

---

2. What collation are you using? By default in laravel-enso/enso project the database collation is:

'collation' => 'utf8mb4_unicode_ci',

but recently I discovered that if a table query has GROUP BY table.name and names are case sensitive, the GROUP BY will fail, since it's dependent on collation CS/CI. Ex:

DB
---------------
ID   | Name
1    | test
2    | test
3    | TEST
4    | TEST

GROUP BY name will only output inside the table

Enso Table:
-------------
test (id:2)
TEST (id:4)

[robbykrlos]

Oh, I just saw this vendor/laravel-enso/filters/src/Services/Search.php

$this->operators = App::make(ComparisonOperators::class);

So there is a way... 👍🏼

[aocneanu]

Hi @robbykrlos

We have a project that's using PostgreSQL while all the rest are using MySQL.

We're using the utf8mb4 charset with utf8mb4_unicode_ci collation, indeed.

As a note, we have over 20 live projects and I don't believe we're using a single group by on something else than an id.

There is no problem contributing to the filters package and add more operators to the ComparisonOperators Enum, and the corresponding implementation if needed.