Order by multiple columns with Doctrine

Question

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   - gt 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

User · Answer

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- gt createQueryBuilder  a          - gt addOrderBy  a column1    ASC          - gt addOrderBy  a column2    ASC          - gt addOrderBy  a column3    DESC              Or  using a OrderBy object      orderBy   new OrderBy  a column1    ASC        orderBy- gt add  a column2    ASC        orderBy- gt add  a column3    DESC         myResults    this- gt createQueryBuilder  a          - gt orderBy  orderBy

User · Answer

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 - gt 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   - gt 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

User · Answer

you can use - gt addOrderBy  sort   order   Add Doctrine Querybuilder btw  often uses  special  modifications of the normal methods  see select-addSelect  where-andWhere-orWhere  groupBy-addgroupBy

User · Answer

You have to add the order direction right after the column name    qb- gt 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- gt addOrderBy  column1    ASC      - gt addOrderBy  column2    DESC

User · Answer

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- gt    field    gt  Criteria  ASC

[php] Order by multiple columns with Doctrine

Examples related to php

Examples related to doctrine

Examples related to sql-order-by