SQLALchemy custom integer sort order

Question

I have a table with a column of integers that represent the type of the row. These integers range from 1 - 5. How do I specify a custom sort order in SQLAlchemy, for example if I want the rows sorted in the following order [4, 2, 3, 5, 1]?

I'm aware of the Enum datatype, but that maps to a VARCHAR in the database backend and changing the schema is not an option.

score 6 · Accepted Answer · edited Jun 24 '15 at 13:44

It is my guess that you must already have some table which contains some information about the type or row. And if you do not, maybe you should have such a look-up table. In case this ordering is always the same, I would then add a column to this table to indicate the ordering, join on it and use it.

class TypeInfo(Base):
    __tablename__ = 'typeinfo'
    id = Column(Integer, primary_key=True)
    sort_order = Column(Integer)

# assiming the database contains values like:
tis = [ TypeInfo(id=1, sort_order=50),
        TypeInfo(id=2, sort_order=20),
        TypeInfo(id=3, sort_order=30),
        TypeInfo(id=4, sort_order=10),
        TypeInfo(id=5, sort_order=40),
        ]

# your query might look like below:
q = session.query(SomeTable)
q = q.join(TypeInfo, SomeTable.type == TypeInfo.id).order_by(TypeInfo.sort_order)

In case when you cannot change the database, or the ordering might be different, you can solve this with the case expression, although it might not be quick performer on large datasets:

_whens = {4: 1, 2: 2, 3: 3, 5: 4, 1: 5}
sort_order = case(value=SomeTable.type, whens=_whens)
q = session.query(SomeTable)
q = q.order_by(sort_order)

That's an interesting approach, and while I'm not averse to creating a table that contains the sort order that does seem a little overkill to me. I personally wouldn't do that in SQL. Databases provide you with some neat [syntax](http://stackoverflow.com/a/4088921/463782), like `ORDER BY idx(array[4, 2, 3, 5, 1], s.type)`. The case one is much closer to what I had in mind, but I'll keep an eye on the performance. — CadentOrange, Jan 17 '14 at 11:05
After some testing, the `case` expression works well and I don't notice any performance dips. Thanks for the answer. — CadentOrange, Jan 20 '14 at 14:03
This does not solves the generic issue the tile represents. There are infinite integers so you cannot just create a look-up table to map all possible values. — BorjaEst, Dec 07 '21 at 09:33

SQLALchemy custom integer sort order

1 Answers1