CODEWITHSUNDEEP
phpdoctrinesql-order-by
zootropo
zootropo

Reputation: 2491

Order by multiple columns with Doctrine

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?

Upvotes: 139

Views: 151265

Answers (6)

jonathanlerin
jonathanlerin

Reputation: 131

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

Upvotes: 13

Thomas Hansen
Thomas Hansen

Reputation: 193

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)
   ;

Upvotes: 1

Diego Agulló
Diego Agulló

Reputation: 9576

You have to add the order direction right after the column name:

$qb->orderBy('column1 ASC, column2 DESC');

As you have noted, multiple calls to orderBy do not stack, but you can make multiple calls to addOrderBy:

$qb->addOrderBy('column1', 'ASC')
   ->addOrderBy('column2', 'DESC');

Upvotes: 277

Victor S
Victor S

Reputation: 5142

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]).

Upvotes: 0

Anjana Silva
Anjana Silva

Reputation: 9201

In Doctrine 2.x you can't pass multiple order by using doctrine 'orderBy' or 'addOrderBy' as above examples. Because, it automatically adds the 'ASC' at the end of the last column name when you left the second parameter blank, such as in the 'orderBy' function.

For an example ->orderBy('a.fist_name ASC, a.last_name ASC') will output SQL something like this 'ORDER BY first_name ASC, last_name ASC ASC'. So this is SQL syntax error. Simply because default of the orderBy or addOrderBy is 'ASC'.

To add multiple order by's you need to use 'add' function. And it will be like this.

->add('orderBy','first_name ASC, last_name ASC'). This will give you the correctly formatted SQL.

More info on add() function. https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/query-builder.html#low-level-api

Hope this helps. Cheers!

Upvotes: 22

Christian Huber
Christian Huber

Reputation: 818

you can use ->addOrderBy($sort, $order)

Add:Doctrine Querybuilder btw. often uses "special" modifications of the normal methods, see select-addSelect, where-andWhere-orWhere, groupBy-addgroupBy...

Upvotes: 11

Related Questions