Doctrine中按比较语句排序结果

Question

摘要：在Doctrine中，我们能否使用ORDER BY parent_id = id DESC的语法？

我们有一个数据库表格Category，其中包含分层的文章类别。该表格如下：

CREATE TABLE IF NOT EXISTS `Category` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `sort` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `IDX_FF3A7B97727ACA70` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=22 ;

层次结构只有一级，顶层通过其parent_id与其id相同来识别。

我们希望使用Doctrine QueryBuilder（DQL也可以）获取所有行，以便按顺序列出所有类别，父类别排在第一位，其子类别直接跟在后面。就像这样：

id  parent_id   sort    name
-----------------------------------
1   1           1       Animals
5   1           1       Cat
10  1           3       Dog
9   1           2       Walrus
2   2           2       Fruits
13  2           3       Apple
12  2           2       Pear
11  2           1       Melon
3   3           3       Vehicles
15  3           2       Car
6   3           3       Train
14  3           1       Paraglider

使用这个原生的MySQL查询，我们能够按照我们想要的顺序获取项目：

select * from Category order by parent_id asc, parent_id = id desc;

然而，我们不知道如何在QueryBuilder或DQL中实现。问题出在parent_id = id排序条件上，在DQL中会导致错误：[Syntax Error] line 0, col 109: Error: Expected end of string, got '=' 我们可以通过$em->createNativeQuery()来解决这个问题，但我们真的很想找到一种正确的ORM方式来实现这一点。

- Kaivosukeltaja

Answer 1

尝试这个查询。

SELECT *, CASE id WHEN parent_id THEN 1 ELSE 0 END AS custom_order
FROM Category 
WHERE ORDER BY parent_id ASC, custom_order DESC;

- aykut

---

谢谢，但我需要按照问题中展示的顺序获取所有行。查询在没有WHERE的情况下运行良好，并产生我们想要的结果。我们只是想不使用原生MySQL查询来完成它。 - Kaivosukeltaja

你想要获取所有行，但是ID等于parent_id的行首先出现吗？ - aykut

是的，我们需要它们主要按parent_id排序，其次按其parent_id是否与其id相同排序。 - Kaivosukeltaja

谢谢，但我需要将其翻译成DQL。使用CASE会导致Doctrine错误（“预期文字，得到CASE”）。 - Kaivosukeltaja

现在我无法使用DQL尝试查询，但我认为可以使用createNativeQuery（）方法进行查询。 - aykut

是的，但我们正在努力摆脱createNativeQuery。 - Kaivosukeltaja

Answer 2

我的声誉不允许我对aykut的回答发表评论，但我想指出我和主题发布者遇到了完全相同的问题，并且使用CASE语句很好地解决了它。

Kaivosukeltaja报告说使用CASE会导致Doctrine错误，但在我的情况下:-)这种情况没有发生。似乎从2.2版本开始添加了CaseExpressions： https://github.com/doctrine/doctrine2/commit/816ce41f638d28934c79a12ef27f954124b2639e

- marty