Order by multiple columns with Doctrine

user_383920 0
Viewed 128449

I need to order data by two columns (when the rows have different values for column number 1, order by it; otherwise, order by column number 2)

I'm using a QueryBuilder to create the query.

If I call the orderBy method a second time, it replaces any previously specified orderings.

I can pass two columns as the first parameter:

->orderBy('r.firstColumn, r.secondColumn', 'DESC');

But I cannot pass two ordering directions for the second parameter, so when I execute this query the first column is ordered in an ascending direction and the second one, descending. I would like to use descending for both of them.

Is there a way to do this using QueryBuilder? Do I need to use DQL?

doctrine sql-order-by php
6 Answers

You can use orderBy() followed by an addOrderBy() - nesting several orderBy()'s is not possible, but nesting several addOrderBy()'s also works after the initial orderBy().

Example:

$this->createQueryBuilder('entity')
       ->orderBy('entity.addDate', 'DESC')
       ->addOrderBy('entity.id', 'DESC')
user_136904200

The orderBy method requires either two strings or an Expr\OrderBy object. If you want to add multiple order declarations, the correct thing is to use addOrderBy method, or instantiate an OrderBy object and populate it accordingly:

   # Inside a Repository method:
   $myResults = $this->createQueryBuilder('a')
       ->addOrderBy('a.column1', 'ASC')
       ->addOrderBy('a.column2', 'ASC')
       ->addOrderBy('a.column3', 'DESC')
   ;

   # Or, using a OrderBy object:
   $orderBy = new OrderBy('a.column1', 'ASC');
   $orderBy->add('a.column2', 'ASC');
   $orderBy->add('a.column3', 'DESC');

   $myResults = $this->createQueryBuilder('a')
       ->orderBy($orderBy)
   ;
user_25332330

The comment for orderBy source code notes: Keys are field and values are the order, being either ASC or DESC.. So you can do orderBy->(['field' => Criteria::ASC]).

user_4076150
Related