Order by multiple columns with Doctrine

php doctrine sql-order-by
zootropo picture zootropo · Jul 20, 2012 · Viewed 103.7k times · Source

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?

Answer

Diego Agulló picture Diego Agulló · Jul 20, 2012

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

Related questions

Doctrine - How to print out the real sql, not just the prepared statement?

We're using Doctrine, a PHP ORM. I am creating a query like this: $q = Doctrine_Query::create()->select('id')->from('MyTable'); and then in the function I'm adding in various where clauses and things as appropriate, …

Read More
Execute raw SQL using Doctrine 2

I want to execute raw SQL using Doctrine 2 I need to truncate the database tables and initialize tables with default test data.

Read More
Doctrine 2: Update query with query builder

Hi I've got the following query but it doesn't seem to work. $q = $this->em->createQueryBuilder() ->update('models\User', 'u') ->set('u.username', $username) ->set('u.email', $email) ->where('u.…

Read More