Define the order of result of a SQL Query in Doctrine

Question

My concrete problem in a Symfony2 - Doctrine application:

I want to order search results of a query in a self defined order (w.winnerType: 'main','special', 'normal', 'web) which is NOT ASC or DSC,

I've tried several versions as proposed in ORDER BY the IN value list (link to Stackoverflow)

Version 1

  $em = $this->getDoctrine()->getManager();
  $query = $em->createQuery (
     "select w
        FROM MyBundle:Winner w
        WHERE
            w.winnerType IN ('main', 'special', 'normal', 'web')  
.
        ORDER BY w.winnerType='main' DESC, w.winnerType='special' DESC,
            w.winnerType='normal' DESC, w.winnerType='web' DESC  
       ") ;

This gives the error message:

[Syntax Error]: Error Expected end of string, got '='

Version 2

ORDER BY FIELD (w.winnerType, 'main','special', 'normal', 'web')

Gives the error message

[Syntax Error] Error: Expected known function, got 'FIELD'

Version 3

ORDER BY CASE w.winnerType 
                    WHEN 'main' THEN 1
                    WHEN 'special' THEN 2
                    WHEN 'normal' THEN 3
                    WHEN 'web' THEN 4
                    ELSE 999

Gives the error message

QueryException: [Syntax Error] line 0, col 314: Error: Expected end of string, got 'w'

Debug Info: CRITICAL - Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Syntax Error] line 0, col 314: Error: Expected end of string, got 'w'" at C:\Users\sDev2\xampp\htdocs\bvbp\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php

None of the standard SQL custom sort methods (posted in several stackoverflow items) works in my symfony/doctrine environment.

If I use the "ASC" parameter (which does not solve my problem): I don't have problems in my Symfony application

Example (working without problems)

  $em = $this->getDoctrine()->getManager();
  $query = $em->createQuery (
     "select w
        FROM MyBundle:Winner w
        WHERE
            w.winnerType IN ('main', 'special', 'normal', 'web')  
.
        ORDER BY w.winnerType ASC  
       ") ;

What is the syntax to be used in symfony2 / doctrine? Thanks

Possible duplicate of [mysql custom sort](http://stackoverflow.com/questions/2175439/mysql-custom-sort) — Theo, Jan 04 '17 at 21:30
Although you may be using Symphony2 - Doctrine, your question is strictly related to SQL Syntax. The Symphony2 and Doctrine tags were removed because they are not relevant to the question. — Theo, Jan 04 '17 at 21:45
Ok to remove the doctrine and symphony tag - accepting that you are the expert :-) my only concern is: the stackoverflow-link to "ORDER BY the IN value list" (see my desciption ) lists exactly the syntax I am using and it seems to work for other users. This is why I assumed it has to do with doctrine / symfony — Mike, Jan 04 '17 at 21:56

score 2 · Answer 1 · answered Jan 13 '17 at 08:21

I want to order search results of a query in a self defined order (w.winnerType: 'main','special', 'normal', 'web) which is NOT ASC or DSC,

if you can use query like below than it will fix.

$em->createQueryBuilder('w')
                ->select('w')
                ->addSelect("(CASE WHEN w.winnerType like 'main' THEN 0
           WHEN w.winnerType like 'special' THEN 1
           WHEN w.winnerType like 'normal' THEN 2
           WHEN w.winnerType like 'web' THEN 3
           ELSE 999 END) AS HIDDEN fixed_order")
                ->from('WolfBvbpBundle:Winner', 'w')
                ->andWhere('w.winnerType IN (:typesdata)')
                ->setParameters(array(
                    'typesdata' => array('main', 'special', 'normal', 'web')))
                ->orderBy('fixed_order', 'ASC')
            ->getQuery();

Note that you must have "HIDDEN".

You can do with doctrine native query as well.

Theo · Answer 2 · 2017-01-04T22:05:30.973

1

The Syntax you are using is not valid SQL. The correct syntax is to either use the column ordinals OR the column names. For Example:

SELECT w.winnerType 
FROM MyBundle:Winner w
WHERE
    w.winnerType IN ('main', 'special', 'normal', 'web')  
ORDER BY CASE w.winnerType 
    WHEN 'main' THEN 1
    WHEN 'special' THEN 2
    WHEN 'normal' THEN 3
    WHEN 'web' THEN 4
    ELSE 999
END

edited Jan 04 '17 at 22:05

answered Jan 04 '17 at 21:29

Theo

885
6
16

Thanks Theo: I've simplified the example in the latest version, now using only one field (winnerType) for which I want to be able to define any sequence (not ASC or DESC). With this, I am afraid, you solution will not work. – Mike Jan 04 '17 at 21:55
The solution has been updated based on the edits you have made to your post. You can modify to get a customized sort order based on this answer HERE: http://stackoverflow.com/questions/2175439/mysql-custom-sort – Theo Jan 04 '17 at 22:06
Exactly, it is a costom sort, I am looking for. And to my understanding the two "order by" versions, I've tried should serve this purpose. In addition I've tried the "ORDER BY CASE WHEN .... THEN '1'" approach, which gave a Syntax error. Again: I am not sure whether Symfony/Doctrine sql parser is the problem, as any of the standard custom sort methods I've tried gives a specific error message which points to the syntax – Mike Jan 04 '17 at 22:11
Thanks I've copied your code I get the following error message from symfony: QueryException: [Syntax Error] line 0, col 314: Error: Expected end of string, got 'w' CRITICAL - Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Syntax Error] line 0, col 314: Error: Expected end of string, got 'w'" at C:\Users\sDev2\xampp\htdocs\bvbp\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php line 52 – Mike Jan 04 '17 at 22:23
Theo - I've added the code, you've proposed and the resulting error message to my problem description. Do you share my assumption that the problem is related to Symfony2/and Doctrine. I've added those two tags, to get input from this community. Hope you agree. – Mike Jan 04 '17 at 22:52

Define the order of result of a SQL Query in Doctrine

2 Answers2