orderBy several Columns - Doctrine QueryBuilder

Question

I want to use orderBY on several Columns, but they should act like one single column. The table looks something like that:

col1 | col2
5    |
2    |
     | 3
7    |
     | 1
     | 1

The result should look like that:

col1 | col2
     | 1
     | 1
2    |
     | 3
5    |
7    |

If we would be using raw SQL there would be Methods, like using COALESCE.

But how could this be achieved in the Doctrine QueryBuilder?

Edit:

I tried the orderBy like this:

qb->orderBy("COALESCE(col1, col2)", "DESC");

And like this

qb->add("orderBy", "COALESCE(col1, col2) DESC");

But both times I was thrown the following Error:

[Syntax Error] line 0, col 700: Error: Expected end of string, got '('

As the good solution - create custom doctrine function for implement method COALESCE. — ZhukV, Nov 13 '13 at 12:07
@Lolo Thank you for your comment. I edited my Question above. Maybe I used it the wrong way? — Robin, Nov 13 '13 at 12:19
@ZhukV Sorry, but I am probably not fit enough with Doctrine to do so. Do you have any further documentation/information on that? — Robin, Nov 13 '13 at 12:20
Seems to be impossible: http://stackoverflow.com/questions/18148818/using-is-null-and-coalesce-in-orderby-doctrine-querybuilder — Stephan Vierkant, Nov 13 '13 at 12:34
@StephanVierkant Thanks, I saw this post before, but they just found no solution. I thought maybe we can find something else. I am also not bound to coalesce, this just would have been my solution. — Robin, Nov 13 '13 at 12:37

Alexey B. · Accepted Answer · 2013-11-17T14:28:27.193

5

Try to add order column in select statement and then order by it.

$qb = $em->createQueryBuilder();
$qb
    ->select('entity', 'COALESCE(col1, col2) as orderCol')
    ->from('Namespace/Entity', 'entity')
    ->orderBy('orderCol', 'DESC')

Maybe it can be helpful to use IF from beberlei/DoctrineExtensions library. Install it with composer(or you can just copy one file if you want) and

register function in config.yml

doctrine:
    orm:
      entity_managers:
        default:
          dql:
            string_functions:
              IF: DoctrineExtensions\Query\Mysql\IfElse

and build a query

$qb = $em->createQueryBuilder();
$qb
    ->select('entity', 'IF(col1, col1, col2) as orderCol')
    ->from('Namespace/Entity', 'entity')
    ->orderBy('orderCol', 'DESC')

Another option is to use Native Query with pure mysql and map result to entities.

edited Nov 17 '13 at 14:28

answered Nov 17 '13 at 14:14

Alexey B.

11,965
2
49
73

Thank you for your reply. I will test this tomorrow and will give you feedback on this. But I really don't want to use raw mysql queries. Do you know what would be more efficient. The IF or the COALESCE? – Robin Nov 20 '13 at 10:17
8

I can confirm that @forgottenbas's solution will work as I had similar situation few days ago. As addition I would also include `HIDDEN` keyword to preserve clean resultset structure, but that is by no means mandatory: `COALESCE(col1, col2) as HIDDEN orderCol` – Jovan Perovic Nov 20 '13 at 23:48
@Robin dont know which is faster, usually its not the slowest part of a query, but where are some information in [this question](http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster) – Alexey B. Nov 21 '13 at 04:06
@Jovan Perovic: Thanks for **HIDDEN!** This is mandatory if you use it as the 'query_builder' argument for an EntityType field in Symfony 2.8! – Thomas Landauer May 06 '16 at 08:37

orderBy several Columns - Doctrine QueryBuilder

Edit:

1 Answers1