sqlalchemy - 如何查询多个多态继承表上的关系？

Question

假设您有以下简化示例架构，它使用 SQLAlchemy 连接表多态继承。 Engineer和 Analyst型号有 Role关系。 Intern模型没有。

class Role(db.Model):

    __tablename__ = 'role'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(16), index=True)

class EmployeeBase(db.Model):

    __tablename__ = 'employee_base'

    id = db.Column(db.Integer, primary_key=True)

    some_attr = db.Column(db.String(16))
    another_attr = db.Column(db.String(16))

    type = db.Column(db.String(50), index=True)

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

class Engineer(EmployeeBase):

    __tablename__ = 'engineer'

    id = db.Column(db.Integer, db.ForeignKey('employee_base.id'), primary_key=True)

    role_id = db.Column(db.Integer, db.ForeignKey('role.id'), index=True)
    role = db.relationship('Role', backref='engineers')

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

class Analyst(EmployeeBase):

    __tablename__ = 'analyst'

    id = db.Column(db.Integer, db.ForeignKey('employee_base.id'), primary_key=True)

    role_id = db.Column(db.Integer, db.ForeignKey('role.id'), index=True)
    role = db.relationship('Role', backref='analysts')

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

class Intern(EmployeeBase):

    __tablename__ = 'intern'

    id = db.Column(db.Integer, db.ForeignKey('employee_base.id'), primary_key=True)

    term_ends = db.Column(db.DateTime, index=True, nullable=False)

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

如果我想找 Employees与 Role name名称中有“石油”，我该怎么做？

我尝试了很多很多方法。我最接近的是这个，它只返回 Analyst火柴:

employee_role_join = with_polymorphic(EmployeeBase, 
    [Engineer, Analyst])
results = db.session.query(employee_role_join).join(Role).filter(Role.name.ilike('%petroleum%'))

如果我尝试做这样的事情，我会收到 AttributeError ，因为我正在搜索加入 Role 的属性 table :

employee_role_join = with_polymorphic(EmployeeBase, 
    [Engineer, Analyst])
results = db.session.query(employee_role_join).filter(or_(
    Engineer.role.name.ilike('%petroleum%'),
    Analyst.role.name.ilike('%petroleum%')))

Answer 1

您可以尝试明确指定 join ON 子句，因为您的第一个查询的问题似乎是 Role仅在 analyst.role_id 上加入柱子:

employee_role_join = with_polymorphic(EmployeeBase, [Engineer, Analyst])
results = session.query(employee_role_join).join(Role).filter(Role.name.ilike('%petroleum%'))
print(str(results))

SELECT employee_base.id AS employee_base_id,
    employee_base.some_attr AS employee_base_some_attr,
    employee_base.another_attr AS employee_base_another_attr,
    employee_base.type AS employee_base_type,
    engineer.id AS engineer_id,
    engineer.role_id AS engineer_role_id,
    analyst.id AS analyst_id,
    analyst.role_id AS analyst_role_id
FROM employee_base
LEFT OUTER JOIN engineer ON employee_base.id = engineer.id
LEFT OUTER JOIN analyst ON employee_base.id = analyst.id
JOIN role ON role.id = analyst.role_id 
WHERE lower(role.name) LIKE lower(?)

employee_role_join是 AliasedClass暴露两者 Analyst和 Engineer ，然后我们可以使用它来创建一个 join-ON 子句，如下所示:

results = session.query(employee_role_join)\
        .join(Role, or_(  \
            employee_role_join.Engineer.role_id==Role.id,  \
            employee_role_join.Analyst.role_id==Role.id  \
        ))\
        .filter(Role.name.ilike('%petroleum%'))

将结果 SQL 更改为 JOIN role ON engineer.role_id = role.id OR analyst.role_id = role.id