Doctrine: use user defined function in ORDERBY

Question

What I want to achieve is something like this:

SELECT * FROM users ORDER BY (IF ranking IS NULL, 9999, ranking) ASC

So, I need an if in my orderby. But it seems that user defined functions (I created one named ComplexIf) are not working in OrderBy.

->addOrderBy('ComplexIf(u.ranking IS NULL, 9999, u.ranking )', 'asc')

What am I doing wrong? How can I achieve this?

I'm not sure about your actual question, but have you tried using `CASE` instead of `IF`? See [this answer on another question](http://stackoverflow.com/a/14107313/1001110) which suggests it should work in Doctrine. — Nic Wortel, May 09 '14 at 08:20
Thank you! that was exactly what I needed, I finally made it work — Chr Testo, May 09 '14 at 12:00

score 5 · Accepted Answer · answered May 09 '14 at 12:04

Thanks SO MUCH to Nic, I finally found the solution with a CASE instead of an IF!

$query = $this
            ->createQueryBuilder( 'a' )
            ->select('a')
            ->add('from', 'path\to\whatever\table a')
            ->addSelect('CASE WHEN a.ranking IS NULL THEN 9999 ELSE a.ranking END as HIDDEN ORD')
            ->where( 'a.deleted IS NULL' )
            ->orderBy(  'ORD', 'asc' )
            ->getQuery()
            ;

Doctrine: use user defined function in ORDERBY

1 Answers1