RFC ORM: Add collate param to QueryExpression->like() & notLike()

[celsowm]

This is a (multiple allowed):

• [] bug

• enhancement

• feature-discussion (RFC)

• CakePHP Version: 3 or major

• Platform and Target: all

What you did

Insensitve Accent like on all "main dbs":

mysql: http://sqlfiddle.com/#!9/cba20d/1
sqlserver: http://sqlfiddle.com/#!18/cba20/3
postgres: http://sqlfiddle.com/#!17/cba20/8
sqlite: http://sqlfiddle.com/#!5/cba20d/1

What happened

For us, non ASCII speakers, It is very tediuos to search thing like "José" or "Jose" when your database, by default is not accent insensitive

Recently, in our portuguese reform, the word idéia is now ideia, without the accent.
So, since years, in our main CakePHP app our solutions are things like:
(in mssql case):

 $table->find()
           ->where(['field COLLATE Latin1_general_CI_AI Like' => '%idéia%']);

Very very ugly....

What you expected to happen

Something like (a pun!, hehe):

$query = $table->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->like('field', '%idéia%', 'Latin1_general_CI_AI');
    });

or even maybe:

$query = $table->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->likeAi('field', '%idéia%');
    });

P.S. Remember, an issue is not the place to ask questions. You can use Stack Overflow
for that or join the #cakephp channel on irc.freenode.net, where we will be more
than happy to help answer your questions.

Before you open an issue, please check if a similar issue already exists or has been closed before.

[ADmad]

I am curious why don't you configure your database to use defaults that better suit your needs? Or just change the collation of the columns on which you need to do accent insensitive matches?

[celsowm]

The are severe problems when you do that after all table and data be created on Sqlserver (shame on you Bill Gates) Em ter, 5 de mai de 2020 01:08, ADmad <notifications@github.com> escreveu:

…

I am curious why don't you configure your database to use defaults that better suit your needs? Or just change the collation of the columns on which you need to do accent insensitive matches? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#14546 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAC2FOFQZ47LQPCANELLDITRP6GK3ANCNFSM4MZHWYBA> .

[markstory]

    return $exp->like('field', '%idéia%', 'Latin1_general_CI_AI');

We couldn't use this signature as the 3rd parameter is currently reserved for the column type. Adding new methods where we can include the encoding in a useful spot seems like a better plan to me.

[celsowm]

    return $exp->like('field', '%idéia%', 'Latin1_general_CI_AI');

We couldn't use this signature as the 3rd parameter is currently reserved for the column type. Adding new methods where we can include the encoding in a useful spot seems like a better plan to me.

Agreed!
How about $exp->likeWithCollate?

[ndm2]

This is a rather general problem that applies to all sorts of comparisons, so maybe a more general solution could be found?

I once had the questionable pleasure to do this in a project, as it was using a legacy database that couldn't be changed, and it was required in various types of comparions, and also in ordering. What I did was using an extended identifier expression and a value expression that would accept a charset and a collation respectively, so I could do stuff like this:

->where(['col LIKE' => new StringValue('%term%', '_utf8', 'utf8_bin')])

and:

->where(['col' => new StringValue('term', '_utf8', 'utf8_bin')])

as well as:

->orderAsc(new Identifier('col', 'utf8_general_ci'));

Not saying that's a good fit for the core, but if something similarly generic/flexible could be integrated, then that would certainly be appreciated.

[dereuromark]

In the past with IdeHelper I also came to love the idea of such value objects to encapsulate these behavior traits, rather than a complex main API.

[markstory]

A wrapper object/expression would be great if the cast expression is consistent.

[celsowm]

A wrapper object/expression would be great if the cast expression is consistent.

Something like this?

$query = $table->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->like('field', fn(StringExpression $e) 
                                       => $e->collate("%idéia%","Latin1_general_CI_AI"));
    });

and please @othercorey , this is not only a sqlserver RFC, it is valid for all cake dialects (see my first post)

[othercorey]

I think we could have a CollateInterface and StringExpression that implements it. IdentifierExpression would also implement it.

[othercorey]

This was added in 4.2.