Dear List, Hoping for help with following unanswered stackoverflow question. If the approach is not the best, that feedback is good as well. In the case below is it better to just not use relationships and just use the method with the query?
Thanks -------------------------------------------------------------------- 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 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), better alternatives, improvements on the query appreciated. 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') -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/KKB3-3r5kSAJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.