Order by list with Doctrine

Ordering by values is super simple with Doctrine with build in functionality. But have you tried order by a specific list of statuses? Without having every status assigned a number and the number stored in the database?
There is a function in MySQL that can do this.

It's called FIELD. And if there's a MySQL function for it, then there's a pretty good chance that it's also available through beberlei/doctrineextensions (which it is). So let's enable it as a string function in our doctrine.yml:

doctrine:
  orm:
    dql:
      string_functions:
        FIELD: DoctrineExtensions\Query\Mysql\Field

From there we can use it in our repositories like this:

public function findAllOrderedByStatus(): array
{
    return $this->getEntityManager()->createQuery('
      SELECT fc
      FROM App\Entity\Job j
      ORDER BY FIELD(j.status, :statuses)
    ')
        ->setParameter('statuses', [
            Job::STATUS_CREATED,
            Job::STATUS_IN_PROGRESS,
            Job::STATUS_DONE,
        ])
        ->getResult()
    ;
}

Happy ordering!