Specify the sort-order for an Enum field type

Question

I have a model that uses the Enum field type:

from sqlalchemy import Column, Enum, Integer, String

class Task(Base):
    __tablename__ = 'tasks'

    id = Column(Integer, primary_key=True)
    summary = Column(String)
    priority = Column(Enum('low', 'medium', 'high'))

If I sort the records by priority (the Enum), it's sorts alphabetically:

high
low
medium

How can I specify the sort-order for this field?

Answer 1

Another way may exist, but the simplest (and most database-agnostic) solution I know of would be to use a case statement in the order by clause.

First, set up your whens dict for your case statement by getting the tuple you used to create the column's Enum:

enums = Task.priority.type.enums  # finds the Enum tuple (with the same order you originally gave it)
whens = {priority: index for index, priority in enumerate(enums)}  # dynamically creates whens dict based on the order of the Enum tuple

Or, if you must, hard code your whens dict (definitely less than ideal, since you now have to change code in two places):

whens = {'low': 0, 'medium': 1, 'high': 2}

Second, set up your sort logic in a case statement:

sort_logic = case(value=Task.priority, whens=whens).label("priority")

Third, run your query and order by your case statement:

q = session.query(Task).order_by(sort_logic)  # see the sql at the end of this answer

You now have a query sorted by your original Enum tuple:

>>> for item in q:
        print item.id, item.priority
#  now in the order you were looking for
4 low
7 low
3 medium
8 medium
2 high
6 high

Just for reference, the above query produces the following SQL:

SELECT        tasks.id AS tasks_id,
              tasks.summary AS tasks_summary,
              tasks.priority AS tasks_priority 
FROM          tasks
ORDER BY CASE tasks.priority
             WHEN :param_1 THEN :param_2
             WHEN :param_3 THEN :param_4
             WHEN :param_5 THEN :param_6
         END