CODEWITHSUNDEEP
phpmysqldoctrine-orm
Bruno Souza
Bruno Souza

Reputation: 218

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

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'

Upvotes: 1

Views: 708

Answers (3)

colonelclick
colonelclick

Reputation: 2215

Doctrine core implements the parts of SQL that are supported by all vendors. You can use extensions for access to language-specific functions, such as the CAST function of MySql.

For example, the well-known beberlei extensions have dozens of MySql extensions, including CAST.

Upvotes: 0

Bruno Souza
Bruno Souza

Reputation: 218

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.

Upvotes: 0

alx
alx

Reputation: 2367

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.

Upvotes: 0

Related Questions