php findby - Order by multiple columns with Doctrine




annotation relation (4)

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?


Answers

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


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. http://www.doctrine-project.org/api/orm/2.2/class-Doctrine.ORM.QueryBuilder.html#_add

Hope this helps. Cheers!


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

one funny thing.

CONVERT TO CHARSET and CONVERT()/CAST() suggested by Anshu will work fine if charset in the table is in right encoding.

If for some reason latin1 column containts utf8 text, CONVERT() and CAST() will not be able to help. I had "messed" my database with that setup so spend bit more time on solving this.

to fix this in addition to character set conversion, there are several exercises required.

  1. "Hard one" is to recreate the database from dump that will be converted via console
  2. "Simple one" is to convert row by row or table by table:
INSERT INTO UTF8_TABLE (UTF8_FIELD)
SELECT convert(cast(convert(LATIN1_FIELD using latin1) as binary) using utf8)
  FROM LATIN1_TABLE;

basically, both cases will process string to original symbols and then to right encoding, that won't happen with simple convert(field using encoding) from table; command.





php doctrine sql-order-by order