1

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 '('

Reveclair
  • 2,399
  • 7
  • 37
  • 59

2 Answers2

5

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.

Community
  • 1
  • 1
Bram Gerritsen
  • 7,178
  • 4
  • 35
  • 45
  • You have a duplicate `)` in your query. try: `$em->createQuery('SELECT a FROM MyBundle:Artiste a WHERE a.id IN (4,12,1) ORDER BY FIELD(4,12,1)');` – Bram Gerritsen Mar 05 '13 at 12:07
  • Sorry, unfortunately, it was an error on stackoverflow but it was right on my code $query = $em->createQuery('SELECT a FROM MyBundle:Artiste a WHERE a.id IN (4,12,1) ORDER BY FIELD(4,12,1)'); So I'm still with the same error.. – Reveclair Mar 05 '13 at 23:18
  • I don't have any errors with your new query but the result is always ordered like this 1,4,12 with no regard to the order of ids in "field" statement and "in" in statement – Reveclair Mar 06 '13 at 10:20
  • I get the right sort when I use the query. Could you get the raw SQL string and execute it directly against your DB? `$query->getSql()` – Bram Gerritsen Mar 06 '13 at 10:24
  • Here is the raw sql query from this doctrine query ($query = $em->createQuery('SELECT a.id, field(12,4,1) as HIDDEN field FROM MyBundle:Artiste a WHERE a.id IN (12,4,1) ORDER BY field');) ----raw sql----'SELECT a0_.id AS id0, FIELD(12,4,1) AS sclr1 FROM Artiste a0_ WHERE a0_.id IN (12, 4, 1) ORDER BY sclr1 ASC' – Reveclair Mar 06 '13 at 11:23
  • You didn't copy my exact query. did you? because the first parameter in the `FIELD()` function needs te be the field you want to order on. So it has to be `FIELD(a.id,4,12,1)` instead of `FIELD(4,12,1)`. – Bram Gerritsen Mar 06 '13 at 11:28
  • You're right! I'm sorry. That works great now! I'll now try with your pull request. Thanks a lot for your help. – Reveclair Mar 06 '13 at 11:46
  • No problem your welcome ;). Let me know if it works with the PR. Hope it gets accepted by the Doctrine team. – Bram Gerritsen Mar 06 '13 at 12:06
0

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
www
  • 4,365
  • 1
  • 23
  • 24