sqlalchemy - how to convert query with subquery into relationship

Question

In the code below I want to replace all_holdings in Account with a property called holdings that returns the desired_holdings (which are the holdings representing the latest known quantity which can change over time). I'm having trouble figuring out how to construct the call to relationship.

In addition I'd appreciate any comments on the appropriateness of the pattern (keeping historic data in a single table and using a max date subquery to get most recent), as well as on better alternatives, or improvements to the query.

from sqlalchemy import Column, Integer, String, Date, DateTime, REAL, ForeignKey, func
from sqlalchemy.orm import relationship, aliased
from sqlalchemy.sql.operators import and_, eq
from sqlalchemy.ext.declarative import declarative_base
from db import session
import datetime
import string

Base = declarative_base()

class MySQLSettings(object):
    __table_args__ = {'mysql_engine':'InnoDB'}

class Account(MySQLSettings, Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    all_holdings = relationship('Holding', backref='account')

    def desired_holdings(self):
        max_date_subq = session.query(Holding.account_id.label('account_id'),
                                      Holding.stock_id.label('stock_id'),
                                      func.max(Holding.as_of).label('max_as_of')). \
                                      group_by(Holding.account_id, Holding.stock_id).subquery()

        desired_query = session.query(Holding).join(Account,
                                                    Account.id==account.id).join(max_date_subq).\
                                                    filter(max_date_subq.c.account_id==account.id).\
                                                    filter(Holding.as_of==max_date_subq.c.max_as_of).\
                                                    filter(Holding.account_id==max_date_subq.c.account_id).\
                                                    filter(Holding.stock_id==max_date_subq.c.stock_id)

        return desired_query.all()

    def __init__(self, name):
        self.name = name

class Stock(MySQLSettings, Base):
    __tablename__ = 'stock'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    def __init__(self, name):
        self.name = name

class Holding(MySQLSettings, Base):
    __tablename__ = 'holding'
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey('account.id'), nullable=False)
    stock_id = Column(Integer, ForeignKey('stock.id'), nullable=False)
    quantity = Column(REAL)
    as_of = Column(Date)

    stock = relationship('Stock')

    def __str__(self):
        return "Holding(%f, '%s' '%s')"%(self.quantity, self.stock.name, str(self.as_of))

    def __init__(self, account, stock, quantity, as_of):
        self.account_id = account.id
        self.stock_id = stock.id
        self.quantity = quantity
        self.as_of = as_of

if __name__ == "__main__":
    ibm = Stock('ibm')
    session.add(ibm)
    account = Account('a')
    session.add(account)
    session.flush()
    session.add_all([ Holding(account, ibm, 100, datetime.date(2001, 1, 1)),
                      Holding(account, ibm, 200, datetime.date(2001, 1, 3)),
                      Holding(account, ibm, 300, datetime.date(2001, 1, 5)) ])
    session.commit()

    print "All holdings by relation:\n\t", \
        string.join([ str(h) for h in account.all_holdings ], "\n\t")

    print "Desired holdings query:\n\t", \
        string.join([ str(h) for h in account.desired_holdings() ], "\n\t")

The results when run are:

All holdings by relation:
    Holding(100.000000, 'ibm' '2001-01-01')
    Holding(200.000000, 'ibm' '2001-01-03')
    Holding(300.000000, 'ibm' '2001-01-05')
Desired holdings query:
    Holding(300.000000, 'ibm' '2001-01-05')

Answer 1

Following answer provided by Michael Bayer after I posted to sqlalchemy google group:

The desired_holdings() query is pretty complicated and I'm not seeing a win by trying to get relationship() to do it. relationship() is oriented towards maintaining the persistence between two classes, not as much a reporting technique (and anything with max()/group_by in it is referring to reporting).

I would stick @property on top of desired_holdings, use object_session(self) to get at "session", and be done.

See more information on query-enabled properties.