SQLAlchemy inheritance filter on all columns

Question

So I want to execute a filter on all Columns of my Database Model which uses table inheritance. I am by no means sure if this is actually do-able or not.

To get started let's use the same inheritance example from the SQLAlchemy Doc just slightly modified. I've omitted the imports here.

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

    @classmethod
    def get_all(cls, session, query):
        _filters = []
        for prop in class_mapper(cls).iterate_properties:
            if isinstance(prop, ColumnProperty):
                _col = prop.columns[0]
                _attr = getattr(cls, _cls.name)

                _filters.append(cast(_attr, String).match(query))

        result = session.query(cls)
        result = result.filter(or_(*_filters))
        return result.all()

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))
    foo = Column(String(10))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))
    bar = Column(String(20))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

Now let's say I would like to query all Employee where some of the fields matches a query. The method get_all shown above will only query in Columns known to the class Employee.

Is there some way to query in all columns of the entire inheritance chain?

Answer 1

It's pretty ugly, but one way would be to find all the subclasses that inherit from Employee, then left join those tables and add their columns to the query.

How to get subclasses: https://stackoverflow.com/a/5883218/443900

Have not tested this, but something like this should work.

@classmethod
def get_all(cls, session, query):
    _filters = []
    for prop in class_mapper(cls).iterate_properties:
        if isinstance(prop, ColumnProperty):
            _col = prop.columns[0]
            _attr = getattr(cls, _cls.name)

            _filters.append(cast(_attr, String).match(query))

    result = session.query(cls)
    result = result.filter(or_(*_filters))

    # get the subclasses
    subclasses = set()
    for child in cls.__subclasses__():
        if child not in subclasses:
            subclasses.add(child)
            # join the subclass
            result = result.outerjoin(child)
            # recurse to get the columns from the subclass
            result = subclass.get_all(session, result)

    # return a query, not a result to allow for the recursion. 
    # you might need to tweak this.
    return result