如何在Doctrine2(Symfony2)中按案例排序 [英] How to ORDER BY CASE in Doctrine2 (Symfony2)

问题描述

我想通过使用Symfony 2.3中的Doctrine运行此查询.但是似乎Doctrine不了解CASE语句.有人可以帮忙吗?预先谢谢你！

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!

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

推荐答案

如果您使用的是createQueryBuilder，则可以使用

If you are using createQueryBuilder then you can use 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.

这篇关于如何在Doctrine2(Symfony2)中按案例排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！