Single Table Inheritance with mult-column keys

[Mike Gilligan]

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?

[Michael Bayer]

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.

[Mike Gilligan]

Thanks for the quick response Michael. Unfortunately, I had my email
settings wrong and just now read your response.

You stated you attached an example but I do not see one. Can you point
me to it?

> > --
> > You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> > To view this discussion on the web visithttps://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.
> > To post to this group, send email to sqlal...@googlegroups.com.
> > To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> > For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

here's a pastebin of it:

http://pastebin.com/z8XWsv2e

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.

To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

<multi_discrim.py>

[Mike Gilligan]

That's exactly what I needed. Thanks again.

> > For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.

>
> >> --
> >> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

> >> To view this discussion on the web visithttps://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.

[Rob]

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.

Many thanks,

Rob 

[Michael Bayer]

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.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

<product_inheritance.py>

[Rob]

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.