在Doctrine DQL Query（Symfony2）中使用COLLATE [英] Using COLLATE inside Doctrine DQL Query (Symfony2)

问题描述

我的具体问题是：我在DQL查询中找不到与Doctrine一起使用COLLATE的任何内容（并且它似乎不起作用）

我有一个表，其中包含 utf8_general_ci charset。我有一个特定的字段，它具有重音字符（如á，ű，ő等。）

与 utf8_general_ci 无法确定常规字符与其重音对之间的区别（a =á，u =ű，o =ő），对于大多数查询我完全可以那桌子上的土地！所以如果我让我们说：

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

此查询将返回两个结果：

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

再次，对于大多数用例来说，这对我来说是非常好的！

但是有一个具体的功能，我需要确定差异，在常规MySQL我可以使用：

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

这只返回不重要的版本。

是，可以使用Doctrine的createQuery（写入dql）或查询构建器来完成这样的事情吗？

我想我已经阅读了所有相关的文档，但是找不到这样做的方法。

解决方案

根据Cerad的建议，编写一个自定义的DQL函数：
http://www.doctrine-project.org/2010/03 /29/doctrine2-custom-dql-udfs.html

我设法创建了这个：

 命名空间MyCompany\MyBundle\DQL; 
 
使用Doctrine\ORM\Query\AST\Functions\FunctionNode; 
使用Doctrine\ORM\Query\Lexer; 
 
 class Collat​​eFunction extends FunctionNode 
 {
 public $ expressionToCollat​​e = null; 
 public $ collat​​ion = null; 
 
 public function parse（\Doctrine\ORM\Query\Parser $ parser）
 {
 $ parser-> match（Lexer :: T_IDENTIFIER）; 
 $ parser-> match（Lexer :: T_OPEN_PARENTHESIS）; 
 $ this-> expressionToCollat​​e = $ parser-> StringPrimary（）; 
 
 $ parser-> match（Lexer :: T_COMMA）; 
 
 $ parser-> match（Lexer :: T_IDENTIFIER）; 
 $ lexer = $ parser-> getLexer（）; 
 $ this-> collat​​ion = $ lexer-> token ['value']; 
 
 $ parser-> match（Lexer :: T_CLOSE_PARENTHESIS）; 
} 
 
 public function getSql（\Doctrine\ORM\Query\SqlWalker $ sqlWalker）
 {
 return sprintf（'％s COLLATE％s '，$ this-> expressionToCollat​​e-> dispatch（$ sqlWalker），$ this-> sortlation）; 
} 
} 
  

注册到config.yml（ http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html ）
这将查找一个具有两个参数的Collat​​e函数：一个字段和一个字符集（没有有效的字符集检测）。

以DQL编写）

  COLLATE（字段，整理）
  

并创建（在可运行的MySQL中）

 `field` COLLATE collat​​ion 
  

Ofcourse排序规则应该是一个有效的字符集（如 utf8_bin ）或者你会收到一个MySQL错误。

我想有一个更简单的解决方案，但是我只能将它创建为一个函数。至少问题解决了。

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.

这篇关于在Doctrine DQL Query（Symfony2）中使用COLLATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！