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();
}

wich gives the following error:

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

alx alx · Accepted Answer · 2019-06-24T20:11:35

You could do this:

$select->select('o')
           ->from($this->entityPath, 'o')
           ->where("o.origin = :origin")
           ->setParameter('origin', $origin)
           ->addSelect('CAST(RIGHT(o.id, LENGTH(o.id)-3) AS UNSIGNED) AS HIDDEN orderCol')
           ->orderBy('orderCol', 'DESC')
           ->setMaxResults('1');

So, you keep orderBy() simple by using alias defined elsewhere, and this helps you to overcome its limitations.

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

2 Answers