Doctrine 2 mysql FIELD function in order by

Question

I'm trying to use the MySQL FIELD function in an order by clause in a query. I'm assuming that Doctrine 2 doesn't support the FIELD function out of the box - is that true? If so, how can I use it? Will I have to turn my whole query into a native query? Is there a Doctrine 2 extension that adds this functionality?

Answer 1

Jeremy Hicks, thanks for your extension. I didn`t know how to connect your function to doctrine, but finally i find answer.

$doctrineConfig = $this->em->getConfiguration();
$doctrineConfig->addCustomStringFunction('FIELD', 'DoctrineExtensionsQueryMysqlField');

I need FIELD function to order my Entities that i select by IN expression. But you can use this function only in SELECT, WHERE, BETWEEN clause, not in ORDER BY.

Solution:

$qb
            ->select("r, field(r.id, " . implode(", ", $ids) . ") as HIDDEN field")
            ->from("EntitiesRound", "r")
            ->where($qb->expr()->in("r.id", $ids))
            ->orderBy("field");

To avoid adding field alias into your result row you need put HIDDEN keyword. So this how to be able order values in IN expression in Doctrine 2.2.