Skip to content

How to filter after two joins in flask SQL alchemy

I have the following 3 tables:

class Sample(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    all_prosampairs = db.relationship('Prosampair', order_by="Prosampair.order", backref='modifies_sample', lazy=True, cascade="all, delete", passive_deletes=True)

class Process(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    all_prosampairs = db.relationship('Prosampair', backref='in_process', lazy=True, cascade="all, delete", passive_deletes=True)

class Prosampair(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order = db.Column(db.Integer, nullable=False)
    process_id = db.Column(db.Integer, db.ForeignKey('', ondelete="CASCADE"), nullable=False)
    sample_id = db.Column(db.Integer, db.ForeignKey('', ondelete="CASCADE"), nullable=False)

Each sample has a chain of processes it goes through and I need to get a table which includes both and the latest date it has encountered a process, so that I can filter by this date.

Something like this

What I tried so far was looking at this and similar solutions:

But I can’t quite comprehend how to deal with this third association table that I have yet.

 subqjoin1 = db.session.query(Sample).join(Prosampair).join(Process).subquery()
 subqjoin2 = db.session.query(subqjoin1, db.func.max('latest_process')).group_by(
 result = db.session.query(subqjoin2).filter( subqjoin2.c.latest_process <= value ).all()

I am getting variations of

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column:

and I am afraid I don’t quite grasp how to access the parameters that I want to. Two subqueries are probably excessive, but I struggle to figure out a better way



Make sure to reference columns in the subquery off of .c, for example subq.c.latest_process.

with Session(engine) as session:
    latest_subq = session.query("sample_id"),
        Prosampair, == Prosampair.sample_id
        Process, Prosampair.process_id ==

    q = session.query(Sample, latest_subq.c.latest_process).join(latest_subq, == latest_subq.c.sample_id).order_by(latest_subq.c.latest_process)

    for sample, latest_process in q.all():
        print (, latest_process)

User contributions licensed under: CC BY-SA
3 People found this is helpful