Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Sort by field not in alphabetical order

I'm using the QueryBuilder to build my query - using it to return full model objects. I need to order the query by a "semester" column, in chronological order. The problem is the contents of the column are "F","W","G","S". That's the order I need the values to be sorted in. So all rows with "F" come first, then "W", etc.

First I tried to order with the MySQL FIELD() function, but I was getting parse exceptions, so I don't think PDO supports it. I was unable to find any documentation one way or the other.

Then, I tried using the ->columns() method of the QueryBuilder, but that renamed all my columns, and caused the return type to not be model objects.

Dropping QueryBuilder and using just raw SQL queries is not an option I'm going to consider - I have a complex framework built up around QueryBuilder and it would take too much work to rewrite.

My final attempt will be simply to change how "semester" is stored in the database, with "F" being 0, "W" being 1, etc. I'd rather not change the data if I can get this to work in code.

Any ideas?

Hey, you may consider pasting the accepted answer on stackoverflow here. In case someone needs it :)

edited Apr '16

One idea that comes in my mind is to create another table with id and value with "F" being 0, "W" being 1. Join them and sort by id. This way you don't need to modify any data.

The other way should be the FIELD function, which you seems to have problems

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field

http://stackoverflow.com/questions/16495309/custom-mysql-sort-by-field-value



56.2k
Accepted
answer
edited Apr '16

The answer that worked for me from Stack Overflow:

You could try this string with the orderBy function :

CASE 
  semester 
  WHEN 'F' THEN 0 
  WHEN 'W' THEN 1 
  WHEN 'G' THEN 2 
  ELSE 3 
END

You could extend the db dialect class to implement the FIELD function of MySQL, altough then your code would be tied to that particular adapter.

Or, use nested IF:

ORDER BY IF(semester='F',1,IF(semester='G',2,IF(semester='W',3,IF(semester='F',4,5))))