I'm trying to find out how to do something like the answer to this question but using SQLAlchemy.
Having a hard time finding how to order query results by the difference between two columns without resorting to raw SQL.
Any help is greatly appreciated!!
Also, just out of curiosity, is it possible to create a column that automatically calculates the difference between two other columns? For example, you would have a revenue and loss column, and then a profit column that automatically combined those.
session.query((Table.revenue - Table.loss).label('profit')).order_by('profit desc').all()
For automatically calculate column you can use events
from sqlalchemy import event class Table(Model): id = Column(Integer, primary_key=True) revenue = Column(Integer) loss = Column(Integer) profit = Column(Integer) @event.listens_for(Table.revenue, 'set') def revenue_listener(target, value, oldvalue, initiator): # update profit when revenue change target.profit = value - (target.loss or 0) @event.listens_for(Table.loss, 'set') def loss_listener(target, value, oldvalue, initiator): # update profit when loss change target.profit = (target.revenue or 0) - value