Doctrine 2 mysql FIELD 函数按顺序由

2021-11-20 00:00:00 field native mysql doctrine-orm

我正在尝试在查询的 order by 子句中使用 MySQL FIELD 函数.我假设 Doctrine 2 不支持开箱即用的 FIELD 功能 - 这是真的吗?如果是这样，我该如何使用它?我是否必须将整个查询转换为本地查询?是否有添加此功能的 Doctrine 2 扩展?

I'm trying to use the MySQL FIELD function in an order by clause in a query. I'm assuming that Doctrine 2 doesn't support the FIELD function out of the box - is that true? If so, how can I use it? Will I have to turn my whole query into a native query? Is there a Doctrine 2 extension that adds this functionality?

推荐答案

Jeremy Hicks，感谢 您的扩展程序.我不知道如何将您的职能与教义联系起来，但最终我找到了答案.

Jeremy Hicks, thanks for your extension. I didn`t know how to connect your function to doctrine, but finally i find answer.

$doctrineConfig = $this->em->getConfiguration();
$doctrineConfig->addCustomStringFunction('FIELD', 'DoctrineExtensions\Query\Mysql\Field');

我需要 FIELD 函数来对我通过 IN 表达式选择的实体进行排序.但是您只能在SELECT, WHERE, BETWEEN 子句中使用此函数，而不能在ORDER BY 中使用.

I need FIELD function to order my Entities that i select by IN expression. But you can use this function only in SELECT, WHERE, BETWEEN clause, not in ORDER BY.

解决方案:

$qb
            ->select("r, field(r.id, " . implode(", ", $ids) . ") as HIDDEN field")
            ->from("Entities\Round", "r")
            ->where($qb->expr()->in("r.id", $ids))
            ->orderBy("field");

为了避免在结果行中添加 field 别名，您需要放置 HIDDEN 关键字.所以这是如何能够在 Doctrine 2.2 中的 IN 表达式中对值进行排序.

To avoid adding field alias into your result row you need put HIDDEN keyword. So this how to be able order values in IN expression in Doctrine 2.2.

相关文章