How to query a relationship on multiple polymorphic-inheritance tables? : <p>Let's say you have the following simplified example schema, which uses SQLAlchemy joined table polymorphic inheritance. <code>Engineer</code> and <code>Analyst</code> models have a <code>Role</code> relationship. The <code>Intern</code> model does not.</p>

<pre><code>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',
    }
</code></pre>

<p><strong>If I want to find <code>Employees</code> with a <code>Role</code> <code>name</code> having "petroleum" somewhere in the name, how would I do that?</strong></p>

<p>I've tried many, many approaches. The closest I've come is this, which only returns <code>Analyst</code> matches:</p>

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

<p>If I try to do something like this, I get an <code>AttributeError</code>, because I'm searching on an attribute of the joined <code>Role</code> table:</p>

<pre><code>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%')))
</code></pre>
