Search code examples
mysqldql

Order by date expect one that should be last

My query:

SELECT c FROM MyBundle:Category c ORDER BY c.name = 'Archive', c.createdAt DESC

I get 

Error: Expected end of string, got '='

I want all entries to be orded by date except the archive which should be the last. Any ideas what I am doing wrong?

Solution

  • This will sort records in descending order by date, keeping the Archive records at the bottom:

    SELECT c 
FROM MyBundle:Category c 
ORDER BY case when c.name = 'Archive' then 2 else 1 end, 
    c.createdAt DESC

    SQL Fiddle Example

    As an extension to this answer, this is what I had to change to make it work with DQL:

    SELECT c,
  CASE WHEN c.name = 'Archive' THEN 2 ELSE 1 END AS HIDDEN sortCondition
  FROM MyBundle:Category c ORDER BY sortCondition, c.createdAt DESC