title: sqlalchemy - how to convert query with subquery into relationship
body: <p>In the code below I want to replace <code>all_holdings</code> in Account with a property called <code>holdings</code> that returns the <code>desired_holdings</code> (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.</p>
  
  <p>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.</p>
  
  <pre><code>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")
  </code></pre>
  
  <p>The results when run are:</p>
  
  <pre><code>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')
  </code></pre>