所有列上的 SQLAlchemy 继承过滤器

所以我想对所有执行过滤器Columns我的数据库模型使用表继承。我绝对不确定这是否真的可行。

首先，让我们使用相同的继承示例SQLAlchemy 文档 http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html只是稍微修改了一下。我在这里省略了导入。

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',
    }

现在假设我想查询所有Employee其中某些字段与查询匹配。方法get_all上面显示的只会在类已知的列中查询Employee.

有没有办法在整个继承链的所有列中进行查询？

它非常丑陋，但一种方法是找到从 Employee 继承的所有子类，然后左连接这些表并将它们的列添加到查询中。

获取子类的方法：https://stackoverflow.com/a/5883218/443900 https://stackoverflow.com/a/5883218/443900

还没有测试过这个，但是类似的东西应该可以工作。

@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