4

I am working on a project which is built using Symfony and Doctrine, however I am still new to the whole setup.

Cut a long story short I have need to order a result set by the order of the array used in the select.

Which I believe could be done using the FIELD() function, however from my investigations it doesn't look like its possible using Doctrine.

For example, I am stumped as how I can utilise FIELD() in a query like below (if its even possible):

return $this
            ->createQueryBuilder('a')
            ->where('a.identifier IN (:identifiers)')
            ->setParameter('identifiers',$identifiers)
            ->getQuery()
            ->getResult();
cosmicsafari
  • 3,949
  • 11
  • 37
  • 56
  • 2
    Seems you could use DoctrineExtensions to [register FIELD() as a custom method](http://stackoverflow.com/a/10164133/1078488). Do note that using extensions may break compatibility for other RDBS types. – JimL May 16 '16 at 16:23
  • [XY Problem](https://meta.stackexchange.com/questions/66377). – eggyal May 16 '16 at 16:28
  • I am happy to accept any solution, this was just my initial thoughts. – cosmicsafari May 16 '16 at 16:31
  • Can't propose another solution, since I've no idea what problem you're trying to solve. In particular, whence do you obtain the desired ordering? – eggyal May 16 '16 at 16:40
  • The original desired ordering comes from a query to a third party which returns a list of identifiers in an array. The order of the array is the order which I need to pull the data by identifier from the local database. – cosmicsafari May 17 '16 at 09:42
  • JimL solution worked a treat, if he adds it as an answer I will accept it. – cosmicsafari May 18 '16 at 10:01

2 Answers2

4

I am using doctrine version 1.2.4 and using following query to order by specific values.

$query->orderBy('FIELD(str,str1,str2,str3,...)');
Sreenadh Tg
  • 146
  • 4
  • 1
    `Expected known function, got 'FIELD'` with `doctrine/orm` v2.5.14. – Luc PHAN Sep 26 '19 at 09:36
  • 1
    `$queryBuilder->orderBy('FIELD(my_alias.id,1,2,3)');` in Doctrine 2.6 works just fine – pmishev Nov 21 '19 at 17:02
  • 2
    @pmishev I [don't think so](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#dql-functions). Not without [beberlei/DoctrineExtensions](https://github.com/beberlei/DoctrineExtensions). – Adrian Günter Aug 02 '22 at 20:52
0

I'm using doctrine v2.5.x. It does not support the FIELD function.

I found and used this file. https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/Field.php

Or you can install the extension.

CN Lee
  • 124
  • 3