Need help building an SQLAlchemy query + subquery

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 SQ开发者_Go百科LAlchemy's ORM, however. Any help?

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

This is probably because there are problems with the query.

It looks like you want to see all notes for a student id where date_peer_saved is >= date_note_saved.

The problem is that the tables notes and peers are probably Many to One relationship with student. What this means is that you are trying to filter records of peers where there may be many notes and many peers to compare.

You need to join all three tables in such a way that each record of peers relates to a single record of notes. If this doesn't apply to you then you need to rethink what you really are asking for.

Perhaps you want to filter the MAX of date_peer_saved >= MAX of date_note_saved?