Orm: DDC-3311: Add ESCAPE clause support after LIKE pattern, in Expr class

[doctrinebot]

Jira issue originally created by user Vicente69:

Actually, it is not possible to specify an ESCAPE character in a LIKE clause.
The _Expr::like_ method only takes two arguments, and an optional third one shall be added.
Behaviour in common databases shall be checked, but I guess ESCAPE belongs to the ISO specification(?)

[doctrinebot]

Comment created by @ocramius:

Can you add a few usage examples? What APIs are affected?

[doctrinebot]

Comment created by Vicente69:

Sorry, I don't understand what kind of information you're missing.
The API is the _Doctrine\ORM\Query\Expr_ class, in the ORM.
The need is to improve the _like_ method by addind a third optional parameter, that would allow to push an escape character, which seems to me "standard" SQL.
See LIKE specs in vendors documentation, for usage examples:

• Oracle 12g: http://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141
• Mysql 5.6: http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html#operator_like
  The goal is to be able to generate SQL queries with :

LIKE *pattern_ [ESCAPE _character*]

Actually, the Expr API doesn't support it.

[doctrinebot]

Comment created by @ocramius:

[~Vicente69] this is exactly what the kind of feedback I was asking for. As of http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf, the ESCAPE char is actually supported:

LikeExpression ::= StringExpression ["NOT"] "LIKE" StringPrimary ["ESCAPE" char]

I suppose it just needs to be added to the query builder.

[doctrinebot]

Comment created by Vicente69:

Sure, that's it :), using direct DQL for requests, it is possible to write such conditions. However, as you notice it, using the _QueryBuilder_ and the _Expr_ methods, it is not possible to generate such queries. Thanks Marco!

[eugenekurasov]

Hi, any news?
Is Query builder will have escaping string?
Do you have information about PR with escaping chars like in http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#ebnf ?

[Ocramius]

Is Query builder will have escaping string?

Use named or positional parameters.

[eugenekurasov]

Hi, I did not accurately describe the problem.
I What about LIKE and chars "%_"?
For Example:

$qb = $em->createQueryBuilder('qb');
$string = '%strin_g%'
$qb->select('t')
    ->from('table', 't')
    ->where($qb->expr()->like('t.string', :string)
    ->setParameter('string', $string);

This problem actual for Mysql/Postgresql - so % and _ - is special chars for LIKE.
Use LikeExpression you can to escape custom chars.
Can I to escape custom chars by QueryBuilder?

[eugenekurasov]

ping @Ocramius
I can create pull request for escape custom chars for like. To add 3 param for escapeing chars in the like.

$qb->expr()->like('t.string', :string, '%_')

[bachhuong]

Your idea looks awesome @eugenekurasov
How about the status of your PR ?

[eugenekurasov]

Hi, I am am stopped my PR, in future I will be back to this PR, but if you have time you can create PR.