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

The construct you are looking for is called column_property. You could use a secondary mapper to actually replace the amount column. Are you sure you are not making things too difficult for yourself by not just storing the negative values in the database directly or giving the "corrected" column a different name?

from sqlalchemy.orm import mapper, column_property
wrongmapper = sqlalchemy.orm.mapper(Transaction, Transaction.__table,
    non_primary = True,
    properties = {'amount':
        column_property(case([(Transaction.transfer_account_id==1, -1*Transaction.amount)], 
        else_=Transaction.amount)})

Session.query(wrongmapper).filter(...)

Answer 2

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