Hi All, Sorry About my previous post - did not read the posting Guidelines.
Using Sqlalchemy 1.3.23 In this example using SQLlite memory, but is also happening in Postgresql 12.9 class TotalCostComparator(Comparator): def __init__(self, cls): expr = case( (cls.Type == "SELL", cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) - cls.Brokerage), else_=cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) + cls.Brokerage ) def asc(self): expr = self.__clause_element__() return asc(expr) def desc(self): expr = self.__clause_element__() return desc(expr) class Transaction(Base): __tablename__ = "Transactions" Id = Column(Integer, autoincrement=True, primary_key=True, nullable=False) Type = Column(Enum("BUY", "SELL", name="HoldingTransactionType"), nullable=False, default="BUY" ) Units = Column(Integer, nullable=False) UnitPrice = Column(Numeric(9, 4), nullable=False) Brokerage = Column(Numeric(9, 2)) # calculated columns @hybrid_property def total_value(self): return self.Units * self.UnitPrice @total_value.comparator def total_value(cls): return TotalValueComparator(cls) @hybrid_property def total_cost(self): if self.Type == "SELL": return self.total_value - self.Brokerage return self.total_value + self.Brokerage @total_cost.comparator def total_cost(cls): return TotalCostComparator(cls) I am getting an error in this code: session.query(Transaction).order_by(desc(Transaction.total_cost)).all() with this traceback: Traceback (most recent call last): File "testhybrid.py", line 122, in <module> trans = db_session.query(Transaction).order_by(desc(Transaction.total_cost)).all() File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/ext/hybrid.py", line 898, in __get__ return self._expr_comparator(owner) File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/ext/hybrid.py", line 1105, in expr_comparator comparator(owner), File "testhybrid.py", line 75, in total_cost return TotalCostComparator(cls) File "testhybrid.py", line 31, in __init__ expr = case( File "<string>", line 2, in case File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 2437, in __init__ whenlist = [ File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 2439, in <listcomp> for (c, r) in whens File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__ return self.operate(getitem, index) File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 762, in operate return op(self.comparator, *other, **kwargs) File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__ return self.operate(getitem, index) File "<string>", line 1, in <lambda> File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 67, in operate return o[0](self.expr, op, *(other + o[1:]), **kwargs) File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 237, in _getitem_impl _unsupported_impl(expr, op, other, **kw) File "/home/gvv/Projects/uby/venv/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 241, in _unsupported_impl raise NotImplementedError( NotImplementedError: Operator 'getitem' is not supported on this expression Sorry couldnt align the code. But I have attached the code to replicate it. Thanks in advance, George -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ddc7e6f0-5e3e-459d-9adf-e02d3220f67cn%40googlegroups.com.
from datetime import date from sqlalchemy import asc, desc, cast, case, create_engine from sqlalchemy import Column, Integer, Numeric, Enum, Date from sqlalchemy.orm import configure_mappers, sessionmaker from sqlalchemy.ext.hybrid import hybrid_property, Comparator from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:', echo=False) Base = declarative_base() class TotalValueComparator(Comparator): def __init__(self, cls): # need to cast to 2 decimals - display is 2 decimals # cls.UnitPrice is 4 decimals expr = cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) super().__init__(expr) def asc(self): expr = self.__clause_element__() return asc(expr) def desc(self): expr = self.__clause_element__() return desc(expr) class TotalCostComparator(Comparator): def __init__(self, cls): expr = case( (cls.Type == "SELL", cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) - cls.Brokerage), else_=cast(cls.Units * cls.UnitPrice, Numeric(9, 2)) + cls.Brokerage ) def asc(self): expr = self.__clause_element__() return asc(expr) def desc(self): expr = self.__clause_element__() return desc(expr) class Transaction(Base): __tablename__ = "Transactions" Id = Column(Integer, autoincrement=True, primary_key=True, nullable=False) Type = Column(Enum("BUY", "SELL", name="HoldingTransactionType"), nullable=False, default="BUY" ) Units = Column(Integer, nullable=False) UnitPrice = Column(Numeric(9, 4), nullable=False) Brokerage = Column(Numeric(9, 2)) # calculated columns @hybrid_property def total_value(self): return self.Units * self.UnitPrice @total_value.comparator def total_value(cls): return TotalValueComparator(cls) @hybrid_property def total_cost(self): if self.Type == "SELL": return self.total_value - self.Brokerage return self.total_value + self.Brokerage @total_cost.comparator def total_cost(cls): return TotalCostComparator(cls) if __name__ == '__main__': configure_mappers() Base.metadata.create_all(engine) db_session = sessionmaker(bind=engine)() trans = Transaction() trans.Type = "BUY" trans.Units = 250000 trans.UnitPrice = 0.0200 trans.Brokerage = 19.95 db_session.add(trans) trans = Transaction() trans.Type = "SELL" trans.Units = 250000 trans.UnitPrice = 0.0210 trans.Brokerage = 19.95 db_session.add(trans) db_session.commit() print("\nTransactions") trans = db_session.query(Transaction).all() format_string = "{:<4} {:>7} {:>9} {:>10} {:>9} {:>10}" print(format_string.format("Type","Units","UnitPrice","TotalValue", "Brokerage","TotalCost")) for tran in trans: print(format_string.format(tran.Type, str(tran.Units), str(tran.UnitPrice), "{:.2f}".format(tran.total_value), str(tran.Brokerage), "{:.2f}".format(tran.total_cost))) print("\nTransactions - Sort by TotalValue desc - OK") trans = db_session.query(Transaction).order_by(desc(Transaction.total_value)).all() print(format_string.format("Type","Units","UnitPrice","TotalValue", "Brokerage","TotalCost")) for tran in trans: print(format_string.format(tran.Type, str(tran.Units), str(tran.UnitPrice), "{:.2f}".format(tran.total_value), str(tran.Brokerage), "{:.2f}".format(tran.total_cost))) print("\nTransactions - Sort by TotalCost desc - NOT OK") trans = db_session.query(Transaction).order_by(desc(Transaction.total_cost)).all() # trans = db_session.query(Transaction).order_by(Transaction.total_cost.desc()).all() print(format_string.format("Type","Units","UnitPrice","TotalValue", "Brokerage","TotalCost")) for tran in trans: print(format_string.format(tran.Type, str(tran.Units), str(tran.UnitPrice), "{:.2f}".format(tran.total_value), str(tran.Brokerage), "{:.2f}".format(tran.total_cost)))