CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
GergelyPolonkai
GergelyPolonkai

Reputation: 6421

SQLAlchemy query model when related model with given properties don’t exist

I have three models (note that this is done in Flask-SQLAlchemy, but if you can only write an answer for vanilla SQLAlchemy, that is fine with me.) Irrelevant fields are removed for clarity.

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')

My goal is to find all Report objects that don’t measure a specific KPI (ie. there is no ReportKPI object whose KPI relationship has identifier set to a specific value).

One of my tries look like

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

but this gives back more Report objects that actually exist (I guess I get one for every ReportKPI that has a KPI with anything but “reflection”.)

Is the thing I want to achieve actually possible with SQLAlchemy? If so, what is the magic word (pleas doesn’t seem to work…)

Upvotes: 0

Views: 586

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

An EXISTS subquery expression is a good fit for your goal. A shorthand way to write such a query would be:

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

but this produces 2 nested EXISTS expressions, though a join in an EXISTS would do as well:

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

Finally, a LEFT JOIN with an IS NULL is an option as well:

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

Upvotes: 2

Related Questions