Create a hybrid_property to return the value of a previous record

Question

I've got as far as this when trying to create a hybrid_property to return the value of the previous record:

from datetime import date
from sqlalchemy import Column, Integer, Date, select, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class A(Base):

    __tablename__ = "a"

    id_ = Column(Integer, primary_key=True)
    record_date = Column(Date)
    example_value = Column(Integer)

    @hybrid_property
    def prev_value(self):
        return
    
    @prev_value.expression
    def prev_value(cls):
        stmt = select(A.example_value)
        stmt = stmt.order_by(A.record_date.desc())
        stmt = stmt.limit(1)
        stmt = stmt.label("prev_value")
        return stmt


engine = create_engine("sqlite:///:memory:")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

session.add(A(record_date=date(2022, 5 ,1), example_value=1))
session.add(A(record_date=date(2022, 5 ,2), example_value=2))
session.add(A(record_date=date(2022, 5 ,3), example_value=3))
session.commit()

prev_value = session.execute(select(A.prev_value).where(A.id_ == 3)).scalar()
print(prev_value)

This is currently returning None when it should return 2.

What should I put in the in-Python method and the expression variant?

Answer 1

SOLUTION:

@prev_value.expression
def prev_value(cls):
    A1 = aliased(A, name="a_prev")
    stmt = select(A1.example_value)
    stmt = stmt.filter(A1.record_date < cls.record_date)
    stmt = stmt.order_by(A1.record_date.desc())
    stmt = stmt.limit(1)
    stmt = stmt.label("prev_value")
    return stmt

---

Explanation

When I run the code from your question verbatim (using sqlite), the result I get is actually 3 and not None as you indicated. In fact, the result returned for all rows is the same value of 3 for all the rows asked by a query session.execute(select(A, A.prev_value)) # (1):

(<A [1] (example_value = 1, id_ = 1, record_date = datetime.date(2022, 5, 1))>, 3)
(<A [2] (example_value = 2, id_ = 2, record_date = datetime.date(2022, 5, 2))>, 3)
(<A [3] (example_value = 3, id_ = 3, record_date = datetime.date(2022, 5, 3))>, 3)

Why do I get 3 for your sample code?

I think this is because the sub-query does not have any condition linking it to the requested row. Assuming the previous value should be previous "by record_date", the link to add to the query should be:

stmt = stmt.filter(A.record_date < cls.record_date)

Running it, however, will now generate None for all the result. Let's look at the generated SQL and the reason why none rows are found:

SELECT a.id_,
       a.record_date,
       a.example_value,

  (SELECT a.example_value
   FROM a
   WHERE a.record_date < a.record_date  # >>> the ISSUE is here: always FALSE
   ORDER BY a.record_date DESC
   LIMIT 1) AS prev_value
FROM a

The problem is that the main query and the sub-query are pointing to the same table/view.

Solve the subquery: In order to solve it, we just need to explicitly create a sub-query, and the problem is solved:

@prev_value.expression
def prev_value(cls):
    A1 = aliased(A, name="a_prev")
    stmt = select(A1.example_value)
    stmt = stmt.filter(A1.record_date < cls.record_date)
    stmt = stmt.order_by(A1.record_date.desc())
    stmt = stmt.limit(1)
    stmt = stmt.label("prev_value")
    return stmt

and the same query (1) produces the following result:

(<A [1] (example_value = 1, id_ = 1, record_date = datetime.date(2022, 5, 1))>, None)
(<A [2] (example_value = 2, id_ = 2, record_date = datetime.date(2022, 5, 2))>, 1)
(<A [3] (example_value = 3, id_ = 3, record_date = datetime.date(2022, 5, 3))>, 2)

based on the following generated SQL:

SELECT a.id_,
       a.record_date,
       a.example_value,

  (SELECT a_prev.example_value
   FROM a AS a_prev
   WHERE a_prev.record_date < a.record_date
   ORDER BY a_prev.record_date DESC
   LIMIT 1) AS prev_value
FROM a