Hi Michael, That does exactly what I was after (and I've learned a little bit more about sqalchemy!)
Thank you very much for your help. On Thursday, 23 August 2012 19:13:27 UTC+1, Michael Bayer wrote: > > > On Aug 23, 2012, at 10:04 AM, Rob wrote: > > Hi Michael, > > I have a similar (but subtly different) problem to this, trying to mix > single- and joined-table inheritance. > Essentially my model looks as follows: > > Product(Base) > PhysicalProduct(Product) > NonPhysicalProduct(Product) > > The Physical/NonPhysicalProduct use single table inheritance whilst > objects inheriting from them use joined tables... > > I have a fully working model --- and there's no question that it works!! > But I can't help feeling that I've missed something relating to the > __mapper_args__ which is then requiring explicit calls to __init__ objects > higher up the tree. (rather than bunging up this message, please see the > attached file) > > I'd be really grateful if you could take a look and hopefully point me in > the right direction. > > > > this model wouldn't "work" fully, as if you did query(Product), the > discriminator would only be the "product_type" column. p_discr and > np_discr would be ignored. The polymorphic load only checks for one key > in one map, and that map is always shared among all classes in the > hierarchy starting at Product. > > I'm going to call this pattern "cascading polymorphic ons" for now, I > guess. The attached file will get you there, but requires two techniques > that are probably outside of the scope of what a the average SQLA user > could be expected to come up with on his or her own. To get SQLA to do > these kinds of things automatically should be possible, most likely by just > having the "match polymorphic_on to class" phase during loading continue > it's process several times. The concept is not too hard but there's edges > to it which may make it a more involved enhancement, this is > http://www.sqlalchemy.org/trac/ticket/2555. > > For now here's the workaround version: > > """ > mixed single and joined table inheritance. > """ > > from sqlalchemy import * > from sqlalchemy import types > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base, declared_attr > from sqlalchemy import event > > Base = declarative_base() > > class Product(Base): > __tablename__ = 'products' > id = Column(types.Integer, primary_key=True) > discriminator = Column('product_type', types.String(50), > nullable=False) > > _discriminator = "discriminator" > > def price_history(self): > return [] > > class PhysicalProduct(Product): > p_discr = Column(types.String(50)) > > _discriminator = "p_discr" > > @declared_attr > def __mapper_args__(cls): > return {'polymorphic_identity': 'physical_product'} > > def inventory(self): > return "computed inventory" > > class NonPhysicalProduct(Product): > np_discr = Column(types.String(50)) > > _discriminator = "np_discr" > > @declared_attr > def __mapper_args__(cls): > return {'polymorphic_identity': 'nonphysical_product'} > > def somefunc(self): > return "someval" > > # set polymorphic on as a coalesce of those three > # columns. It's after the fact beacuse p_discr and np_discr > # are defined after Product, but if you move them up then > # this can be inline inside of Product.__mapper_args__. > # this would improve loads too as it appears the p_discr/np_discr columns > # aren't loaded directly when you query for Product > for mp in Product.__mapper__.self_and_descendants: > mp._set_polymorphic_on( > func.coalesce( > Product.__table__.c.p_discr, > Product.__table__.c.np_discr, > Product.__table__.c.product_type > )) > > # build our own system of assigning polymorphic identities > # to instances; use the 'init' event. > # Add a "print" for the "identity" dict to see what it's doing. > @event.listens_for(Product, "init", propagate=True) > def init(target, args, kwargs): > identity = {} > for cls, supercls in zip(type(target).__mro__, > type(target).__mro__[1:]): > if not hasattr(supercls, '_discriminator'): > break > discriminator_attr = supercls._discriminator > poly_identity = cls.__mapper__.polymorphic_identity > identity.setdefault(discriminator_attr, poly_identity) > for key in identity: > setattr(target, key, identity[key]) > > > class Newspaper(PhysicalProduct): > __tablename__ = 'newspapers' > __mapper_args__ = {'polymorphic_identity': 'newspaper'} > > id = Column(types.Integer, > ForeignKey('products.id'), > primary_key=True > ) > title = Column(types.String(50)) > > def __init__(self, title): > self.title = title > > > class NewspaperDelivery(NonPhysicalProduct): > __tablename__ = 'deliveries' > __mapper_args__ = {'polymorphic_identity': 'delivery'} > > id = Column(types.Integer, > ForeignKey('products.id'), > primary_key=True > ) > destination = Column(types.String(50)) > > def __init__(self, destination): > self.destination = destination > > > # note here how the polymorphic map works out: > print Product.__mapper__.polymorphic_map > # {'newspaper': <Mapper at 0x1014d8890; Newspaper>, > # 'delivery': <Mapper at 0x1014dec90; NewspaperDelivery>, > # 'nonphysical_product': <Mapper at 0x1014d2350; NonPhysicalProduct>, > # 'physical_product': <Mapper at 0x1014d00d0; PhysicalProduct>} > > > e = create_engine('sqlite:///:memory:', echo='debug') > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > > session = Session(e, autoflush=True, autocommit=False) > > session.add_all([ > Newspaper(title="Financial Times"), > NewspaperDelivery(destination="__somewhere__"), > PhysicalProduct(), > NonPhysicalProduct() > ]) > > session.commit() > > # the important part - that a row only known as Product can > # interpret as a specific subclass > assert [ > type(c) for c in session.query(Product).order_by(Product.id) > ] == [Newspaper, NewspaperDelivery, PhysicalProduct, NonPhysicalProduct] > > # test sub-table load. The load for "title" apparently emits a JOIN still > because > # in order to refresh the subclass of "Product" it also wants to get > # at p_discr. > np = session.query(Product).filter_by(id=1).first() > assert np.title == "Financial Times" > > session.close() > > # in this version, it emits two separate, single table SELECT statements, > # since the first query loads the full set of columns for PhysicalProduct. > np = session.query(PhysicalProduct).filter_by(id=1).first() > assert np.title == "Financial Times" > > > > > > > > > > > Many thanks, > Rob > > > > > > > On Wednesday, 17 August 2011 00:42:28 UTC+1, Michael Bayer wrote: >> >> >> On Aug 16, 2011, at 5:37 PM, Mike Gilligan wrote: >> >> I have a single table that looks similar to the following: >> >> class Equipment(Base): >> type = Column(CHAR(1), primary_key=True) >> sub_type = Column(CHAR(1), primary_key=True) >> code = Column(CHAR(5), primary_key=True) >> >> >> For historical purposes, I cannot modify this table. I would like to >> setup multi-level inheritance similar to this, however it does not work: >> >> class Equipment(Base): >> type = Column(CHAR(1), primary_key=True) >> sub_type = Column(CHAR(1), primary_key=True) >> code = Column(CHAR(5), primary_key=True) >> __mapper_args__ = {'polymorphic_on': type} >> >> >> class Vehicle(Equipment): >> __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': >> sub_type} >> >> >> class Bus(Vehicle) >> __mapper_args__ = {'polymorphic_identity': 'B'} >> >> >> class Rail(Vehicle) >> __mapper_args__ = {'polymorphic_identity': 'R'} >> >> >> I can concatenate the multiple column values into a single discriminator >> column_property but then I do not have an easy way to retrieve all >> vehicles. Any ideas? >> >> >> The inheritance querying does handle multi-level inheritance so if your >> discriminator was on a concatenation of both things would work just fine, >> i.e. if you queried for Vehicle, etc. Each object's >> "polymorphic_identity" would need to include the concatenated value, of >> course. >> >> Unfortunately we're just beginning to support inheritance discriminators >> on a column_property(), and you need to use a very specific approach to >> make this work right now. There's some tickets in trac to allow this >> functionality out of the box. Attached is an example script which >> exercises the above mapping - it uses declarative to minimize the impact of >> the workaround. >> >> > -- > 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/-/KzPgMan_6MIJ. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > To unsubscribe from this group, send email to > sqlalchemy+...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > <product_inheritance.py> > > > -- 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/-/iLn184dhMhcJ. 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.
