Specify the sort-order for an Enum field type

python, sqlalchemy

Solution

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

Problem

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?

Original source