postgresql - How do you test for unmatched tables when using outer join (Many to Many)

Question

In sqlalchemy, how to you test for unmatched tables when using an outerjoin on a many-to-many relationship.

I'm working with 5 tables/objects.

• Program: Art class, spanish class, etc
• Session: Child of Program, each program has several sessions, session stores date/time for each class
• Student: Students can enroll in Programs
• Enrollment: Associates students with Programs
• Attendance: Associates students with Sessions. Created lazily (not until needed)

In the below example I'm trying to find student absences. I perform an outerjoin on the Attendance table because the Attendance object doesn't always exist. I want to return rows where Attendance.absent == True or rows where Attendance is not matched (ie not yet created).

class Session(BaseModel):
    id = db.Column(db.Integer, primary_key=True)
    # list of Attendance objects for students that attended this session
    attendance = db.relationship(Attendance, backref="session") 

    @classmethod
    def get_absences(cls, student):
        # I'm trying to return a list of sessions where this student was absent
        # The query joins the Program table so it can join
        # the enrollment table, so it can filter for enrollments
        # for this student and then return sessions where 
        # the Attendance object has not been created or Attendance.absent == True
        return (
            cls.query
                .join(Program)
                .join(Enrollment)
                .filter(Enrollment.student_id == student.id)
                .outerjoin(Attendance)
                 # Attendance == None is not correct
                .filter(or_(Attendance == None, Attendance.absent == True))
        )


class Attendance(BaseModel):
    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.Integer, db.ForeignKey('session.id') )
    student_id = db.Column(db.Integer, db.ForeignKey('student.id'))

So Session.get_absences() obviously doesn't work because Attendance == None isn't overloaded and doesn't generate sql. How do I return sessions where the Attendance object hasn't been created for a given Student / Session pair?

Answer 1

Assuming a standard relational model with referential integrity enforced by FK constraints.

Basically, you need a query like this:

SELECT st.id, se.id, a.absent
FROM   student         st
JOIN   enrollmant      e  ON e.student_id = st.id
-- JOIN program        p  ON p.id = e.program_id  -- omit middleman
JOIN   session         se ON se.program_id = e.program_id -- p.id -- omit middleman
LEFT   JOIN attendance a  ON a.student_id = st.id
                         AND a.session_id = se.id
WHERE  st.id = 123                                -- given student ID
AND   (a.student_id IS NULL OR a.absent);         -- missing or "absent"

Returns a list of student ID and session ID where the student was either absent or no entry was found.

The full list of candidates is generated from joining to enrollment, (program), and session. The final LEFT [OUTER] JOIN to attendance determines the missing ones.

I commented out the join to program, as this is just a middleman we don't need for the query.

I added absent to the result to tell the difference between missing and "absent" entries.

See:

• Select rows which are not present in other table