On Jul 8, 2010, at 2:42 PM, Chris Withers wrote: > Hi All, > > Say we have the following model: > > class Price(Base): > __tablename__ = 'price' > id = Column(Integer, primary_key=True) > value = Column(Numeric(precision=36, scale=12)) > > class Instrument(Base): > __tablename__ = 'instrument' > id = Column(Integer, primary_key=True) > ticker = Column(String(50)) > > class Allocation(Base): > __tablename__ = 'data' > id = Column(Integer, primary_key=True) > trade_id = Column(Integer, index=True) > instrument_id = Column(ForeignKey(Instrument.id)) > instrument = relationship(Instrument) > quantity = Column(Integer) > price_id = Column(ForeignKey(Price.id)) > price = relationship(Price) > > I now want to map the following class to the 'data' table: > > class Trade(object): > __slots__ = ( > 'id', # Allocation.trade_id > 'intrument_id', # Allocation.instrument_id > 'quantity', # func.sum(Allocation.quantity) > 'average_price',# see next line... > # (func.sum(Allocation.price)/func.sum(Allocation.quantity) > # Trades are mapped from Allocations by group_by(Allocation.trade_id) > ) > > ...if that sort of makes sense? > > I'm not quote sure how to map this, although I guess something along the > lines of > http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects > would be in order?
maybe. I'd probably use a view, actually, but mapping to a select is the same idea. > > However, what that example doesn't show, and what I'm keen to make work, is > if someone changes attributes of the mapped Trade, what I'd like to have > happen depends on the attribute: > > id,instrument_id, - all underlying rows are updated > quantity,average_price - an error is raised > > Are there any examples of this? > Any recommendations? ;-) right. that pattern seems a little crazy to me though I haven't been presented with your problemspace to fully appreciate it. You'd have to conjure up the magic from scratch on this one, intercepting set events and/or checking historical stuff inside of before_flush() like we've done before. You'd probably be calling expire() on any Trade objects encountered in the "dirty" list. this model doesn't give you the best query capability. if you wanted all trades with quantity < 100, its the awkward "select * from (select .. from data group by trade_id) where quantity < 100", instead of being able to just put a "having" inside your subquery. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
