在第2条命令中使用DQL函数

时间：2021-04-26 06:46:48

I'm doing a project in Symfony 2.3 with Doctrine 2.4 using MySQL database.

我正在Symfony 2.3和Doctrine 2.4中使用MySQL数据库做一个项目。

I have an Entity of FieldValue (simplified):

我有一个FieldValue实体(已简化):

class FieldValue
{
    /**
     * The ID
     *
     * @var integer
     */
    protected $fieldValueId;

    /**
     * Id of associated Field entity
     *
     * @var integer
     */
    protected $fieldId;

    /**
     * Id of associated user
     *
     * @var integer
     */
    protected $userId;

    /**
     * The value for the Field that user provided
     *
     * @var string
     */
    protected $userValue;

    /**
     * @var \MyProjectBundle\Entity\Field
     */
    protected $field;

    /**
     * @var \MyProjectBundle\Entity\User
     */
    protected $user;

The problem I have is the fact that $userValue, while it's LONGTEXT in my database, can represent either actual text value , date or number, depending in the type of the Field.

我遇到的问题是，$userValue虽然在我的数据库中是LONGTEXT，但根据字段的类型，它可以表示实际的文本值、日期或数字。

The Field can be dynamically added. After adding a one to any of the users every other user can also fill it's own value for that Field.

可以动态添加字段。在向任何用户添加一个值之后，其他用户也可以为该字段填充自己的值。

While querying the database I use orderBy to sort on a certain column, which also can be one of those Fields. In that case I need to sort on $userValue. This is problematic when I need to have number fields sorted as numbers, and not as strings ('123' is less than '9' in that case...).

在查询数据库时，我使用orderBy对某个列进行排序，该列也可以是这些字段中的一个。在这种情况下，我需要对$userValue进行排序。当我需要把数字字段排序为数字而不是字符串时，这就有问题了(在这种情况下，‘123’小于‘9’)。

The solution for it (I thought) is to CAST the $sort, so I would get SQL like:

它的解决方案(我认为)是使用$sort，因此我将得到SQL，比如:

ORDER BY CAST(age AS SIGNED INTEGER) ASC

Since Doctrine does not have a built-in DQL function for that, I took the liberty of adding that to my project as INT DQL function (thanks to Jasper N. Brouwer):

由于Doctrine没有为此内置的DQL函数，因此我冒昧地将其作为INT DQL函数添加到我的项目中(感谢Jasper N. Brouwer):

class CastAsInteger extends FunctionNode
{
    public $stringPrimary;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS SIGNED INTEGER)';
    }

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

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

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

So happy with myself finding an easy solution I did that:

我很高兴自己找到了一个简单的解决办法:

$sort = "INT(".$sort.")";

$queryBuilder->orderBy($sort, $dir);

which produced expected DQL:

产生预期的DQL:

ORDER BY INT(age) ASC

But also produced an exception:

但也产生了一个例外:

An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 12272: Error: Expected end of string, got '('") in MyProject...

在呈现模板(“[语法错误])第0行，col 12272: Error:字符串的预期结束，在MyProject中获取'(')…

So I've tried to find out what is going on and got into this in Doctrine\ORM\Query\Parser.php:

所以我试着去弄清楚到底是怎么回事，并在Doctrine\ORM\Query\Parser.php中弄明白了:

/**
     * OrderByItem ::= (
     *      SimpleArithmeticExpression | SingleValuedPathExpression |
     *      ScalarExpression | ResultVariable
     * ) ["ASC" | "DESC"]
     *
     * @return \Doctrine\ORM\Query\AST\OrderByItem
     */
    public function OrderByItem()
    {
        ...
    }

Does that mean that there is no possibility to use DQL functions inside ORDER BY? And if this is the case - is there any other way to achieve this?

这是否意味着不可能在ORDER BY中使用DQL函数?如果是这样的话，还有其他的方法吗?

UPDATE

更新

I actually already have INT used in my select query, inside CASE WHEN:

实际上，我的select查询中已经使用了INT，在以下情况下:

if ($field->getFieldType() == 'number') {
    $valueThen = "INT(".$valueThen.")";
}

$newFieldAlias = array("
    (CASE
        WHEN ...
        THEN ".$valueThen."
        ELSE ...
        END
    ) as ".$field->getFieldKey());

Later on the $newFieldAlias is being added to the query.

稍后将$newFieldAlias添加到查询中。

Doesn't change anything...

并不能改变什么…

UPDATE 2

更新2

Even when I add an extra select to the query, which will result in this DQL:

即使我在查询中添加了一个额外的select，它也会导致DQL:

SELECT age, INT(age) as int_age

and then sort like that:

然后这样排序:

ORDER BY int_age ASC

I still don't het the correct result.

我仍然没有得到正确的结果。

I've checked var_dump from $query->getResult(), and this is what I got:

我检查了$query->getResult()中的var_dump，我得到的是:

'age' => string '57' (length=2)      
'int_age' => string '57' (length=2)

Like CAST does not matter. I'm clueless...

和石膏一样不重要。我笨……

3 个解决方案

#1

3  

Doctrine DQL does not accept functions as sort criteria but it does accept a "result variable". It means that you can do the following:

Doctrine DQL不接受函数作为排序标准，但它接受一个“结果变量”。这意味着你可以做以下事情:

$q = $this->createQueryBuilder('e')
    ->addSelect('INT(age) as HIDDEN int_age')
    ->orderBy('int_age');

#2

1  

Doctrine 2 does not support INT by default, but you can use age+0.

原则2默认不支持INT，但是您可以使用age+0。

$q = $this->createQueryBuilder('e')
    ->addSelect('age+0 as HIDDEN int_age')
    ->orderBy('int_age');

#3

0  

Just use this:

就用这个:

->orderBy('u.age + 0', 'ASC');

#1

3  

Doctrine DQL does not accept functions as sort criteria but it does accept a "result variable". It means that you can do the following:

Doctrine DQL不接受函数作为排序标准，但它接受一个“结果变量”。这意味着你可以做以下事情:

$q = $this->createQueryBuilder('e')
    ->addSelect('INT(age) as HIDDEN int_age')
    ->orderBy('int_age');

#2

1  

Doctrine 2 does not support INT by default, but you can use age+0.

原则2默认不支持INT，但是您可以使用age+0。

$q = $this->createQueryBuilder('e')
    ->addSelect('age+0 as HIDDEN int_age')
    ->orderBy('int_age');

#3

0  

Just use this:

就用这个:

->orderBy('u.age + 0', 'ASC');
标签：

相关文章