在 Doctrine DQL 查询 (Symfony2) 中使用 COLLATE

2022-01-03 00:00:00 php symfony doctrine-orm dql

我在 Doctrine 的 DQL 查询中找不到与使用 COLLATE 相关的任何内容(当然它似乎不起作用).

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

我的具体问题:

我有一个带有 utf8_general_ci 字符集的表格.我有一个特定的字段，其中包含重音字符(如á"、ű"、ő"等)

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

与 utf8_general_ci 的基本比较无法确定常规字符与其重音对之间的区别(a = á, u = ű, o = ő)，这对我来说非常好大多数落在该表上的查询！所以，如果我有让我们说:

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!

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

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'

这仅返回无重音版本.

问题是，可以使用 Doctrine 的 createQuery(编写 dql)或查询构建器来完成这样的事情吗?

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?

推荐答案

按照 Cerad 的建议编写自定义 DQL 函数:http://www.doctrine-project.org/2010/03/29/doctrine2-custom-dql-udfs.html

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

我设法创建了这个:

namespace MyCompanyMyBundleDQL;

use DoctrineORMQueryASTFunctionsFunctionNode;
use DoctrineORMQueryLexer;

class CollateFunction extends FunctionNode
{
    public $expressionToCollate = null;
    public $collation = null;

    public function parse(DoctrineORMQueryParser $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(DoctrineORMQuerySqlWalker $sqlWalker)
    {
        return sprintf( '%s COLLATE %s', $this->expressionToCollate->dispatch($sqlWalker), $this->collation );
    }
}

当注册到 config.yml (http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html)这将寻找一个带有两个参数的 Collat​​e函数":一个字段和一个字符集(还没有有效的字符集检测).

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

工作方式(用 DQL 编写)

Works like (written in DQL)

COLLATE( field , collation )

并创建(在可运行的 MySQL 中)

And creates (in runable MySQL)

`field` COLLATE collation

当然排序规则应该是一个有效的字符集(例如utf8_bin)，否则你会得到一个 MySQL 错误.

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.

相关文章