SQLalchemy Joined Inheritance Query Based on Mixin Column

Question

I have a declarative class structure that looks like this:

class BaseClass(Base):
      Column A
      Column B

class Mixin(object):
      Column C

class ItemA(BaseClass):
      Column D

class ItemB(Mixin, BaseClass):
      pass

class ItemC(Mixin, BaseClass):
      Column E

Is there a way I can use with_polymorphic such that I can do a query based on Column C across all of the Items, without having to explicitly know what Items I have? Something like essentially

mixin_poly = with_polymorphic(base = BaseClass, classes = MixinClass.__subclasses__())

Lets assume that at import time all of the derivatives of MixinClass would have been imported before the with_polymorphic is declared.

Edit Note that I've left out the boilerplate for joined-table inheritance, but assume it's properly set up such that doing

poly_base = with_polymorphic(base = BaseClass, classes = '*')
session.query(poly_base).filter(BaseClass.a == value, ...)

performs as expected for querying columns from BaseClass. The point is to be able to query common columns in the subclasses that inherit the Mixin class in the same manner.

zzzeek · Accepted Answer · 2014-03-08 00:57:09Z

5

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import inspect

Base = declarative_base()

class BaseClass(Base):
    __tablename__ = 'base'

    id = Column(Integer, primary_key=True)
    type = Column(String)
    a = Column(Integer)
    b = Column(Integer)
    __mapper_args__ = {"polymorphic_on": type}

class Mixin(object):
    c = Column(Integer)

class ItemA(BaseClass):
    __tablename__ = 'a'
    id = Column(ForeignKey('base.id'), primary_key=True)
    d = Column(Integer)
    __mapper_args__ = {"polymorphic_identity": 'a'}

class ItemB(Mixin, BaseClass):
    __tablename__ = 'b'
    id = Column(ForeignKey('base.id'), primary_key=True)
    __mapper_args__ = {"polymorphic_identity": 'b'}

class ItemC(Mixin, BaseClass):
    __tablename__ = 'c'
    id = Column(ForeignKey('base.id'), primary_key=True)
    e = Column(Integer)
    __mapper_args__ = {"polymorphic_identity": 'c'}

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

def magic_w_poly(base):
    insp = inspect(base)

    w_poly = []
    crit = []

    for mapper in insp.self_and_descendants:
        if "c" in mapper.c:
            w_poly.append(mapper)
            crit.append(mapper.c.c)
    w_col_c = with_polymorphic(base, w_poly)

    def comparator(value):
        return or_(
                    crit_elem == value
                    for crit_elem in crit
                )

    return w_col_c, comparator

s = Session(e)

w_col, comp = magic_w_poly(BaseClass)

print s.query(w_col).filter(comp(35))

query at the end:

SELECT base.id AS base_id, base.type AS base_type, base.a AS base_a, base.b AS base_b, b.id AS b_id, b.c AS b_c, c.id AS c_id, c.c AS c_c, c.e AS c_e 
FROM base LEFT OUTER JOIN b ON base.id = b.id LEFT OUTER JOIN c ON base.id = c.id 
WHERE b.c = :c_1 OR c.c = :c_2

answered Mar 8, 2014 at 0:57

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

aruisdante Over a year ago

Sorry, I left out the boilerplate stuff for joined table inheritance, I'll edit my original question to make that clear. The point is to not have to explicitly call out the subclasses in the query, because I don't know them at coding time, only that they will exist at import time (I.E. Base.metadata will contain them). with_polymorphic lets me do this for subclasses of BaseClass, but I haven't figured out how to let it query common column columns of those subclasses that are from the mixin rather than the base class.

zzzeek Over a year ago

Look at "magic_w_poly()" carefully. Nowhere are the specific subclasses of Mixin called out explicitly, it locates all those subclasses that have the expected column of "C". it should be easy to modify this to look for classes that have Mixin in their mro or similar. If Mixin has arbitrary columns and not just "c", then some more work would be involved to produce a namespace of column-attributes which can compare, probably use PropComparator to build that out.

aruisdante Over a year ago

Doh, I missed the second half of your code. StackOverflow's renderer really needs to make it more obvious when you can scroll. The mixin class might have any number of columns, but they're known ahead of time. I'll try this code out next time I'm working on the project and if it works out I'll accept the answer.

zzzeek Over a year ago

I include all the boilerplate because i have to actually run these examples to make sure they work ;)

Collectives™ on Stack Overflow

SQLalchemy Joined Inheritance Query Based on Mixin Column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related