On Apr 30, 2012, at 10:54 AM, dan wrote: > 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. > >
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. This use case is described at http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#building-query-enabled-properties . > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
