How to use more advanced parameters on Doctrine query builder's 'orderBy()'?

Question

I'm trying to use natural sorting on MySQL through doctrine's query builder. But the orderBy() function doesn't have the parameters I need.

This is the query I need to pass to doctrine:

SELECT * FROM `ouvidoria`
ORDER BY CAST(RIGHT(`id`, LENGTH(`id`)-3) AS UNSIGNED) DESC;

I have tried the following:

public function getLastKeyOfOrigin($origin) {
    $data = $this->getRepository()->findOneBy(['origin'=>$origin->getId()]);
    $select = $this->em->createQueryBuilder();
    $select->select('o')
           ->from($this->entityPath, 'o')
           ->where("o.origin = :origin")
           ->setParameter('origin', $origin)
           ->orderBy('CAST(RIGHT(o.id, LENGTH(o.id)-3) AS UNSIGNED)', 'DESC')
           ->setMaxResults('1');
     return $select->getQuery()->getOneOrNullResult();
}

which gives the following error:

Type: Doctrine\ORM\Query\QueryException Message: [Syntax Error] line 0, col 79: Error: Expected known function, got 'CAST'

Answer 1

I solved it by ordering the query by 2 columns, but answering the asked question you can use doctrine's NativeQuery class to send naked SQL through doctrine.