如何在Doctrine2中按案例排序（Symfony2）

I want to run this query by using Doctrine in Symfony 2.3. But it seems like Doctrine does not understand CASE statement. Can anyone help? Thank you in advance!

我想在Symfony 2.3中使用Doctrine来运行此查询。但似乎Doctrine不理解CASE声明。有人可以帮忙吗？先谢谢你！

SELECT max(id) id, name
FROM cards
WHERE name like '%John%'
GROUP BY name
ORDER BY CASE WHEN name like 'John %' THEN 0
           WHEN name like 'John%' THEN 1
           WHEN name like '% John%' THEN 2
           ELSE 3
      END, name

2 个解决方案

#1

If you are using createQueryBuilder then you can use like

如果您使用的是createQueryBuilder，那么您可以使用like

$query->addSelect("(CASE WHEN name like 'John %' THEN 0
           WHEN name like 'John%' THEN 1
           WHEN name like '% John%' THEN 2
           ELSE 3 END) AS HIDDEN ORD ");
$query->orderBy('ORD', 'DESC');

Note that you must have "HIDDEN".

请注意，您必须拥有“隐藏”。

You can do with doctrine native query as well.

您也可以使用doctrine本机查询。

#2

CASE is vendor-specific and not supported natively by doctrine.

CASE是特定于供应商的，并且原则上不受原则支持。

If the result is smallish, my recommendation is to pull the whole result set then sort the array.

如果结果很小，我的建议是拉出整个结果集然后对数组进行排序。

If the result set will be too large, you should write a native query and hydrate the entity. See the Doctrine Documentation on Native SQL for more information on this. It looks scary, but makes sense once you walk through an example.

如果结果集太大，则应编写本机查询并对实体进行水合。有关此内容的详细信息，请参阅Native SQL上的Doctrine文档。它看起来很可怕，但是一旦你走过一个例子就有意义了。

As a last resort, you could just bypass doctrine and use low-level native SQL. See this post for details.

作为最后的手段，您可以绕过doctrine并使用低级本机SQL。有关详细信息，请参阅此帖

I know Doctrine Extensions has an IfElse function that may work, but I haven't heard many success stories.

我知道Doctrine Extensions有一个可以工作的IfElse函数，但我没有听过很多成功案例。

#1