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?
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.