具有给定属性的相关模型不存在时的SQLAlchemy查询模型

Question

我有三个模型（请注意，这是在Flask SQLAlchemy中完成的，但是如果您只能为vanilla SQLAlchemy编写一个答案，那对我来说没问题。）为了清晰起见，删除了不相关的字段。你知道吗

class KPI(db.Model):
    __tablename__ = 'kpis'

    id = db.Column(db.Integer, primary_key=True)
    identifier = db.Column(db.String(length=50))


class Report(db.Model):
    __tablename__ = 'reports'

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


class ReportKPI(db.Model):
    report_id = db.Column(db.Integer, db.ForeignKey('reports.id'), primary_key=True)
    kpi_id = db.Column(db.Integer, db.ForeignKey('kpis.id'), primary_key=True)

    report = db.relationship('Report', backref=db.backref('values'))
    kpi = db.relationship('KPI')

我的目标是找到所有不测量特定KPI的Report对象（即没有ReportKPI对象的KPI关系identifier设置为特定值）。你知道吗

我的一次尝试看起来像

Report.query \
      .join(ReportKPI) \
      .join(KPI) \
      .filter(KPI.identifier != 'reflection')

但这会返回更多实际存在的Report对象（我想我会为每一个ReportKPI对象得到一个KPI对象，而不是“反射”。）

我想用炼金术实现的目标真的可能吗？如果是的话，什么是神奇的词（请似乎不起作用…）

Answer 1

EXISTS子查询表达式非常适合您的目标。编写此类查询的简写方法是：

Report.query.\
    filter(db.not_(Report.values.any(
        ReportKPI.kpi.has(identifier='reflection'))))

但这会生成两个嵌套的EXISTS表达式，不过EXISTS中的联接也可以：

Report.query.\
    filter(db.not_(
        ReportKPI.query.
            filter_by(report_id=Report.id).
            join(KPI).
            filter_by(identifier='reflection').
            exists()))

最后，带有IS NULL的左连接也是一个选项：

Report.query.\
    outerjoin(db.join(ReportKPI, KPI),
              db.and_(ReportKPI.report_id == Report.id,
                      KPI.identifier == 'reflection')).\
    filter(KPI.id.is_(None))