I’m using SQLAlchemy v2 and have two models: User and Transaction. Here, Transaction refers to money transactions between users, not database transactions. I have defined a hybrid property in the User model that calculates the balance for each user based on their incoming and outgoing money transactions. However, I am encountering issues when trying to use this hybrid property in where or filter for querying users based on there balances.

Here is how I defined my models and hybrid property:

from __future__ import annotations

from decimal import Decimal
from typing import List

from sqlalchemy import ForeignKey, SQLColumnExpression, create_engine, func, select
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
    sessionmaker,
)

class Base(DeclarativeBase):
    pass

class Transaction(Base):
    __tablename__ = "transactions"
    id: Mapped[int] = mapped_column(primary_key=True)

    amount: Mapped[float]
    sender_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    recipient_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

    sender: Mapped[User] = relationship(foreign_keys=[sender_id])
    recipient: Mapped[User] = relationship(foreign_keys=[recipient_id])

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)

    sent_transactions: Mapped[List[Transaction]] = relationship(
        foreign_keys=[Transaction.sender_id], overlaps="sender"
    )
    received_transactions: Mapped[List[Transaction]] = relationship(
        foreign_keys=[Transaction.recipient_id], overlaps="recipient"
    )

    @hybrid_property
    def balance(self) -> Decimal:
        incoming = sum(txn.amount for txn in self.received_transactions)
        outgoing = sum(txn.amount for txn in self.sent_transactions)
        balance = incoming - outgoing
        return balance

    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
        return select(
            (
                func.coalesce(
                    select(func.sum(Transaction.amount))
                    .where(Transaction.recipient_id == 1)
                    .scalar_subquery(),
                    0,
                )
                - func.coalesce(
                    select(func.sum(Transaction.amount))
                    .where(Transaction.sender_id == 1)
                    .scalar_subquery(),
                    0,
                ).label("balance")
            )
        )

And here is the code I ran to query users with balance greater than 1 (not working as expected):

engine = create_engine("sqlite:///db.db")
Session = sessionmaker(engine)

with Session() as session:
    stmt = select(User).where(User.balance > 1)
    session.execute(stmt)

After running the above cd,encountered the following errors:

Traceback (most recent call last):
  File "c:UsersNimaDesktopuserbalancemain.py", line 111, in <module>
    stmt = select(User).where(User.balance > 1)
                              ^^^^^^^^^^^^^^^^
  File "C:UsersNimaAppDataLocalProgramsPythonPython311Libsite-packagessqlalchemysqloperators.py", line 629, in __gt__
    return self.operate(gt, other)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersNimaAppDataLocalProgramsPythonPython311Libsite-packagessqlalchemyormattributes.py", line 453, in operate
    return op(self.comparator, *other, **kwargs)  # type: ignore[no-any-return]  # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersNimaAppDataLocalProgramsPythonPython311Libsite-packagessqlalchemysqloperators.py", line 629, in __gt__
    return self.operate(gt, other)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:UsersNimaAppDataLocalProgramsPythonPython311Libsite-packagessqlalchemyexthybrid.py", line 1509, in operate
    return op(self.expression, *other, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: '>' not supported between instances of 'Select' and 'int'