phpsymfonydoctrine-ormdql

Using COLLATE inside Doctrine DQL Query (Symfony2)


I can't find anything related to using COLLATE in a DQL query with Doctrine (and ofcourse it doesn't seem to work).

My specific problem:

I have a table with utf8_general_ci charset. I have one specific field in it which has accented characters (like 'á', 'ű', 'ő' etc.)

A basic comparison with utf8_general_ci is not able to determine the difference between regular chars and their accented pairs (a = á, u = ű, o = ő), which is perfectly fine for me for the majority of the queries that land on that table! So if I have let's say:

 col1 |  col2
------|-------
   1  | árvíz  
------|-------
   2  | arviz

This query will return both results:

SELECT * FROM `table` WHERE `col2` = 'arviz'

Again, this is perfectly fine for me for most of the use cases!

But there is one specific funcionality, where I need to determine the difference, and in regular MySQL I could use:

SELECT * FROM `table` WHERE `col2` COLLATE utf8_bin = 'arviz'

This returns only the unaccented version.

The question is, can something like this be done using either Doctrine's createQuery (write the dql), or query builder?

I think I've read throught all the relevant documentation, but cannot find a way to do this. Is is possible somehow?


Solution

  • Following Cerad 's suggestion to write a custom DQL function: http://www.doctrine-project.org/2010/03/29/doctrine2-custom-dql-udfs.html

    I managed to create this:

    namespace MyCompany\MyBundle\DQL;
    
    use Doctrine\ORM\Query\AST\Functions\FunctionNode;
    use Doctrine\ORM\Query\Lexer;
    
    class CollateFunction extends FunctionNode
    {
        public $expressionToCollate = null;
        public $collation = null;
    
        public function parse(\Doctrine\ORM\Query\Parser $parser)
        {
            $parser->match(Lexer::T_IDENTIFIER);
            $parser->match(Lexer::T_OPEN_PARENTHESIS);
            $this->expressionToCollate = $parser->StringPrimary();
    
            $parser->match(Lexer::T_COMMA);
    
            $parser->match(Lexer::T_IDENTIFIER);
            $lexer = $parser->getLexer();
            $this->collation = $lexer->token['value'];
    
            $parser->match(Lexer::T_CLOSE_PARENTHESIS);
        }
    
        public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
        {
            return sprintf( '%s COLLATE %s', $this->expressionToCollate->dispatch($sqlWalker), $this->collation );
        }
    }
    

    When registered to the config.yml (http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html) This will look for a Collate 'function' with two arguments: a field and a charset (no valid charset detection yet).

    Works like (written in DQL)

    COLLATE( field , collation ) 
    

    And creates (in runable MySQL)

    `field` COLLATE collation 
    

    Ofcourse collation should be a valid charset (such as utf8_bin) or you will get a MySQL error.

    I guess there is a simpler solution, but I only could create this as a 'function'. At least the problem is solved.