Need help building an SQLAlchemy query + subquery

Question

This is the SQL I need SQLAlchemy to generate via its ORM.

SELECT
    *
FROM
    notes
WHERE
    notes.student_id == {student_id} 
  OR
    notes.student_id IN (
        SELECT
            *
        FROM
            peers
        WHERE
            peers.student_id == {student_id}
          AND
            peers.date_peer_saved >= notes.date_note_saved
    )

The SQL is untested. I just wrote it to demo what I need SQLAlchemy to do.

Basically, the logged in student should see a list of saved notes. The only notes the student should see, however, are those posted by themself or those posted by one of their peers - But only those peers they 'friended' after the note had been saved.

This way, a student won't see notes posted by another student before they became peers.

I'm having trouble getting this to run in SQLAlchemy's ORM, however. Any help?

Answer 1

Basically, you can use the .subquery() method to get a sub query that you can place into another conditional like that.

Something like

subq = sess.query(Peers.id).filter(and_(Peers.student==student, XXX)).subquery()

Then

notes = sess.query(Notes).filter(or_(Notes.student==student, Notes.studing.in_(subq))).all()

(also untested and may not work with this actual query, but just to give you the idea).