在Doctrine 2 ORDER BY中使用DQL函数 [英] Using DQL functions inside Doctrine 2 ORDER BY
问题描述
我正在使用MySQL数据库在Symfony 2.3和Doctrine 2.4中进行项目.
I'm doing a project in Symfony 2.3 with Doctrine 2.4 using MySQL database.
我有一个FieldValue实体(简化):
I have an Entity of FieldValue (simplified):
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;
我遇到的问题是，尽管$userValue在我的数据库中为LONGTEXT，但它可以表示实际的文本值，日期或数字，具体取决于Field的类型.
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.
该字段可以动态添加.在向任何一个用户添加一个后，其他用户也可以填写该字段自己的值.
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.
查询数据库时，我使用orderBy对特定列进行排序，该列也可以是这些字段之一.在这种情况下，我需要对$userValue进行排序.当我需要将数字字段按数字而不是字符串排序时，这是有问题的(在这种情况下，"123"小于"9" ...).
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...).
它的解决方案(我认为)是CAST $sort，所以我会得到如下的SQL:
The solution for it (I thought) is to CAST the $sort, so I would get SQL like:
ORDER BY CAST(age AS SIGNED INTEGER) ASC
由于Doctrine没有内置的DQL函数，因此我自由地将其作为INT DQL函数添加到我的项目中(感谢
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):
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);
产生了预期的DQL:
ORDER BY INT(age) ASC
但也产生了异常:
在呈现模板的过程中引发了异常("[语法错误]行0，列12272:错误:预期的字符串结尾，在MyProject中得到了'('")...
所以我试图找出正在发生的事情，并在Doctrine\ORM\Query\Parser.php中对此进行了研究:
So I've tried to find out what is going on and got into this in Doctrine\ORM\Query\Parser.php:
/**
* OrderByItem ::= (
* SimpleArithmeticExpression | SingleValuedPathExpression |
* ScalarExpression | ResultVariable
* ) ["ASC" | "DESC"]
*
* @return \Doctrine\ORM\Query\AST\OrderByItem
*/
public function OrderByItem()
{
...
}
这是否意味着不可能在ORDER BY中使用DQL函数?
如果是这种情况，还有其他方法可以实现吗?
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?
更新
我实际上已经在CASE WHEN内的选择查询中使用了INT:
I actually already have INT used in my select query, inside CASE WHEN:
if ($field->getFieldType() == 'number') {
$valueThen = "INT(".$valueThen.")";
}
$newFieldAlias = array("
(CASE
WHEN ...
THEN ".$valueThen."
ELSE ...
END
) as ".$field->getFieldKey());
稍后将$newFieldAlias上的内容添加到查询中.
Later on the $newFieldAlias is being added to the query.
什么都没改变...
更新2
即使我在查询中添加了额外的选择，这也会导致此DQL:
Even when I add an extra select to the query, which will result in this DQL:
SELECT age, INT(age) as int_age
，然后像这样排序:
ORDER BY int_age ASC
我仍然没有得到正确的结果.
I still don't het the correct result.
我已经从$query->getResult()中检查了var_dump，这就是我得到的:
I've checked var_dump from $query->getResult(), and this is what I got:
'age' => string '57' (length=2)
'int_age' => string '57' (length=2)
像CAST一样没关系.我一无所知...
Like CAST does not matter. I'm clueless...
推荐答案
Doctrine DQL不接受函数作为排序条件，但确实接受结果变量".这意味着您可以执行以下操作:
Doctrine DQL does not accept functions as sort criteria but it does accept a "result variable". It means that you can do the following:
$q = $this->createQueryBuilder('e')
->addSelect('INT(age) as HIDDEN int_age')
->orderBy('int_age');
这篇关于在Doctrine 2 ORDER BY中使用DQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
