SQLAlchemy custom query column

Question

I have a declarative table defined like this:

class Transaction(Base):
    __tablename__ = "transactions"
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer)
    transfer_account_id = Column(Integer)
    amount = Column(Numeric(12, 2))
    ...

The query should be:

SELECT id, (CASE WHEN transfer_account_id=1 THEN -amount ELSE amount) AS amount
FROM transactions
WHERE account_id = 1 OR transfer_account_id = 1

My code is:

query = Transaction.query.filter_by(account_id=1, transfer_account_id=1)
query = query.add_column(case(...).label("amount"))

But it doesn't replace the amount column.

Been trying to do this with for hours and I don't want to use raw SQL.

Answer 1

Any query you do will not replace original amount column. But you can load another column using following query:

q = session.query(Transaction,
                  case([(Transaction.transfer_account_id==1, -1*Transaction.amount)], else_=Transaction.amount).label('special_amount')
                  )
q = q.filter(or_(Transaction.account_id==1, Transaction.transfer_account_id==1))

This will not return only Transaction objects, but rather tuple(Transaction, Decimal)

---

But if you want this property be part of your object, then:
Since your case when ... function is completely independent from the condition in WHERE, I would suggest that you change your code in following way:

1) add a property to you object, which does the case when ... check as following:

@property
def special_amount(self):
    return -self.amount if self.transfer_account_id == 1 else self.amount

You can completely wrap this special handling of the amount providing a setter property as well:

@special_amount.setter
def special_amount(self, value):
    if self.transfer_account_id is None:
        raise Exception('Cannot decide on special handling, because transfer_account_id is not set')
    self.amount = -value if self.transfer_account_id == 1 else value

2) fix your query to only have a filter clause with or_ clause (it looks like your query does not work at all):

q = session.query(Transaction).filter(
    or_(Transaction.account_id==1, 
        Transaction.transfer_account_id==1)
)

# then get your results with the proper amount sign:
for t in q.all():
    print q.id, q.special_amount