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.

Chronopher

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)

Crude

Thid does work, but is there a way to add a calculated property to my table so that the query will retrieve it always? Note that this property should not be stored in the DB. – Chronopher

This is also doable with SQLAlchemy - see update to my answer – Crude

So simple and elegant! Amazing! 10x – Chronopher

What if the calculated value depends on some variable (and is not computed only on other columns)? – Itinerancy

I asked a question about this variant of the same problem over here – Itinerancy

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.

People

Will the calculation be done inside the database, or in Python application code? If in Python, that could involve shuffling a lot of data over the network that could be avoided by doing the computation inside the DB (e.g. when using calc_column in a WHERE clause). – Chobot

If you specify expression for property it will be used to calculate value on db side, in Python otherwise. – Fabiano