We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

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?



93.7k

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



16.0k
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

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

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



125.7k
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


77.7k

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


127

Use the FIELD() function in the ORDER BY clause:

ORDER BY FIElD(id, '1', '5', '2', ...);