How to use SQLAlchemy hybrid_property in where or filter method?

Question

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:\Users\Nima\Desktop\userbalance\main.py", line 111, in <module>
    stmt = select(User).where(User.balance > 1)
                              ^^^^^^^^^^^^^^^^
  File "C:\Users\Nima\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\sql\operators.py", line 629, in __gt__
    return self.operate(gt, other)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Nima\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\orm\attributes.py", line 453, in operate
    return op(self.comparator, *other, **kwargs)  # type: ignore[no-any-return]  # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Nima\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\sql\operators.py", line 629, in __gt__
    return self.operate(gt, other)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Nima\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\ext\hybrid.py", line 1509, in operate
    return op(self.expression, *other, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: '>' not supported between instances of 'Select' and 'int'

Answer 1

The User.balance hybrid property returns a Select object as defined in your _balance_expression function. Select is a subquery, and SQLAlchemy wouldn't know how to compare that subquery with an integer value (1 in your case).

To solve this issue, you need to modify the _balance_expression method to return a scalar value instead of a subquery. You can do it this way:

from sqlalchemy import case
    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
        incoming = (
            select(func.coalesce(func.sum(Transaction.amount), 0))
            .where(Transaction.recipient_id == cls.id)
            .scalar_subquery()
        )
        outgoing = (
            select(func.coalesce(func.sum(Transaction.amount), 0))
            .where(Transaction.sender_id == cls.id)
            .scalar_subquery()
        )
        return case(
(incoming - outgoing, incoming - outgoing), else_=0).label("balance")