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.

vvladymyrov · Accepted Answer · 2013-06-07T12:47:01.163

13

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)

edited Jun 07 '13 at 12:47

answered Jun 06 '13 at 18:33

vvladymyrov

5,715
2
32
50

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. – Ofir Jun 07 '13 at 06:53
This is also doable with SQLAlchemy - see update to my answer – vvladymyrov Jun 07 '13 at 15:00
So simple and elegant! Amazing! 10x – Ofir Jun 07 '13 at 18:57
What if the calculated value depends on some variable (and is not computed only on other columns)? – cglacet Aug 04 '20 at 13:58
I asked a question about this variant of the same problem over [here](https://stackoverflow.com/questions/63249166/order-by-query-output-column) – cglacet Aug 04 '20 at 16:31

score 3 · Answer 2 · answered Jul 15 '15 at 22:04

3

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.

answered Jul 15 '15 at 22:04

Ashley Temple

311
1
4
8

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). – btubbs Dec 27 '16 at 16:56
If you specify expression for property it will be used to calculate value on db side, in Python otherwise. – zgoda May 31 '17 at 08:35

Sqlalchemy order by calculated column

2 Answers2

Linked