CODEWITHSUNDEEP
sqlsymfonydoctrine-ormdoctrine
Robin
Robin

Reputation: 3840

orderBy several Columns - Doctrine QueryBuilder

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 '('

Upvotes: 4

Views: 3358

Answers (1)

Alexey B.
Alexey B.

Reputation: 12033

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.

Upvotes: 5

Related Questions