DOCTRINE : IN statement ordered?

1
votes

I am using Symfony2 and doctrine 2, and I have a problem with this query :

    $query = $em->createQuery('SELECT a FROM MyBundle:Artiste a WHERE a.id IN (4,12,1)');
    $result = $query->getArrayResult();

And I always get results order by a.id, ie 1 then 4 then 12 while I would like to display the results ordered as the list of ids : 4 then 12 then 1.

UPDATE Thanks to @Bram Gerritsent comment, I register a custom DQL function FIELD, so here is what I have done :

  1. In MyBundle/DQL/Field.php, I have inserted the following code (https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Field.php) (I've just changed the namespace to be namespace MyBundle\DQL;

  2. Then, I add the following in my config.yml as shown in the Symfony2 documentation (http://symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html)

    orm: auto_generate_proxy_classes: "%kernel.debug%" entity_managers: default: auto_mapping: true dql: string_functions: field: MyBundle\DQL\Field

  3. So, I wrote the following query $query = $em->createQuery('SELECT a FROM MyBundle:Artiste a WHERE a.id IN (4,12,1) ORDER BY FIELD(4,12,1)'); but I'm getting this error : [Syntax Error] line 0, col 75: Error: Expected end of string, got '('

2
sqlsymfonydoctrine-orm

2 Answers

5
votes

You need to have a look into MySql FIELD function.

In native MySql you would do something like this:

ORDER BY FIELD(a.id,4,12,1)

The field function isn't part of the Doctrine 2 distribution, but you can get it from the DoctrineExtensions.

See this StackOverflow post for more information about using the FIELD function in Doctrine 2

EDIT

I have tested it using your query but got the same syntax error. The following query works for me. Not sure why you cannot use ORDER BY field(a.id,4,12,1) directly, but you have to create a HIDDEN field in your select first.

SELECT a, field(a.id,4,12,1) as HIDDEN field FROM MyBundle:Artiste a WHERE a.id IN (4,12,1) ORDER BY field

EDIT2

I have done some more debugging and researching and the DQL parser doesn't seem to support string functions in the order by clause. I've fixed the issue and created a Pull Request.

0
votes

Not so nice as FIELD function but should work: 

  SELECT output.a FROM (
    SELECT a, ( CASE WHEN a.id = 4 THEN 1 WHEN a.id = 12 THEN 2 a.id = 1 THEN 3 END ) ord FROM MyBundle:Artiste a WHERE a.id IN (4,12,1)) output ORDER BY output.ord