Sqlalchemy order by calculated column

Question

I'm trying to build a select query using sqlalchemy, but I need to sort the results by a calculated value and I'm not sure how to do it.

Basically, I have a 'start_time' and 'end_time' columns and I want to order the results based on start_time and then end_time but if end_time < start_time I want to add 86400000 to it:

end_time + (86400000 if end_time < start_time else 0)

I can't figure out how to do it. Is there any simple way to add a calculated property to my table class and have the query retrieve that property?

I've tried using @property to create a getter for that calculated end_time but it didn't work.

Answer 1

First you need to define column that will contain your formula implemented as sql function(s)

Than you build your query using defined column:

col = tclass.end_time + case([(tclass.end_time<tclass.start_time, 86400000)], else_=0 )
q = session.query(col).order_by(col)
print q

There is also way to add such calculated column to the Mapped Object class defintion:

class TName(Base):
    end_time = Column(Integer)
    start_time = Column(Integer)
    calc_column = end_time + case([(end_time<start_time, 86400000)], else_=0 )

q2 = session.query(TName.calc_column).order_by(TName.calc_column)

Answer 2

The sqlalchemy documentation now seems to recommend using a hybrid to do this.

Example:

from sqlalchemy.ext.hybrid import hybrid_property

class Tname(Base):
    end_time = Column(Integer)
    start_time = Column(Integer)

    @hybrid_property
    def calc_column(self):
        if self.end_time < self.start_time:
            return self.end_time + 86400000 
        else: 
            return self.end_time

Then you can just run a typical query and order by calc_column.