Summary: Can we do ORDER BY parent_id = id DESC in Doctrine?
We have a database table Category which contains hierarchical article categories. The table looks like this:
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 ;
The hierarchy is only one level deep and the top level is identified with their parent_id being the same as their id.
We'd like to fetch all the rows using the Doctrine QueryBuilder (DQL would do as well) so that all the categories are listed consecutively, the category's parent being the first and it's children following directly afterwards. Like this:
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
Using this native MySQL query we are able to get the items in the order we want:
select * from Category order by parent_id asc, parent_id = id desc;
However, we don't know how to do that in the QueryBuilder or DQL. The problem is the parent_id = id sort condition which results in an error in DQL: [Syntax Error] line 0, col 109: Error: Expected end of string, got '='
We have a workaround with $em->createNativeQuery() but we would really like to find a proper way to do this the ORM way.
Try this query
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;
My reputation doesn't allow me to comment on the answer from aykut but i just wanted to point out that i had the exact same problem as the topic-poster and the CASE statement solved it nicely.
Kaivosukeltaja reports that using a CASE results in a Doctrine error, but in my case :-) this didn't happen. It seems CaseExpressions have been added since version 2.2: https://github.com/doctrine/doctrine2/commit/816ce41f638d28934c79a12ef27f954124b2639e