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.