Doctrine2 case-sensitive query

Question

for some reason I need to query 'case-sensitive' in MySql + doctrine 2. Is it possible?

neither

$em->find('UserEn', 'Bob')

nor

$q = $this->em->createQuery('select u from UserEn u where u.name = :name');
$q->setParameter('name', 'Bob');
$result = $q->getResult();

is working. Any idea?

Answer 1

Maybe you are using a MySQL collation ending with "_ci", like "utf8_general_ci". "ci" stands for "case insensitive". If this is the case, it is not a Doctrine issue, but a MySQL issue.

See http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

"The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default."

Answer 2

For those that are unable to change their database collation, you can use the BINARY operator in order to force case sensitivity on the criteria.

The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character. BINARY also causes trailing spaces to be significant.

See MySQL BINARY Operator for more details.

To enable the BINARY operator in Doctrine DQLs, you can install the Doctrine Extensions library. Or create your own Binary String Function like so.

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

class Binary extends FunctionNode
{
    private $stringPrimary;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'BINARY('.$sqlWalker->walkSimpleArithmeticExpression($this->stringPrimary).')';
    }
}

Next you'll need to register the binary string function with your doctrine configuration. You can do so in your configuration settings or add it as needed like so.

$em->getConfiguration()->addCustomStringFunction('binary', 'DoctrineExtensions\\Query\\Mysql\\Binary');

Then you will be able to use the binary(...) function in your DQL like so.

$q = $em->createQuery('select u from UserEn u where binary(u.name) = :name');
echo $q->getSQL();
/* SELECT ... FROM ... WHERE BINARY(u0_.name) = ? */

To add the binary string function using the Symfony Framework, in your config.yml file change the doctrine.orm.entity_managers.%entity_manager%.dql setting like so.

doctrine:
    orm:
      #...
      entity_managers:
          #...
          default:
              #...
              dql:
                  #...
                  string_functions:
                      #...
                      binary: 'DoctrineExtensions\Query\Mysql\Binary'

See Symfony Doctrine Configuration Documentation for more details

Answer 3

It's not Doctrine issue you have to change table collation to binary then case sensitive would work.
Do alter table and change this
CHARSET=utf8 COLLATE=utf8_general_ci
to this
CHARSET=utf8 COLLATE=utf8_bin