python - SQLAlchemy: how to get all items that are not related to current user?

Question

I have purpose to get all CourseModel items thet are not attended by the student. The student is picked up by id and is an instance of StudentModel. Both tables are linked through a table attending as Many-to-Many relationship.

attending = db.Table('attending',
                     db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
                     db.Column('course_id', db.Integer, db.ForeignKey('courses.id')))

class StudentModel(db.Model):
    __tablename__ = "students"
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String())
    last_name = db.Column(db.String())
    courses = db.relationship('CourseModel', secondary=attending, backref=db.backref('students_on_course', lazy='dynamic'))

class CourseModel(db.Model):
    __tablename__ = "courses"
    id = db.Column(db.Integer, primary_key=True)
    course_name = db.Column(db.String(), unique=True)
    description = db.Column(db.Text())

For example, we have next data:

      CourseModel                     attending
    ----------------            ----------------------
    id   course_name            student_id   course_id
    ----------------            ----------------------
     1   Biology                    1            1
     2   Math                       1            3
     3   Philosophy
     4   Design

The result for student with id == 1 should be [(2, "Math"), (4, "Design")]

I apologize for not showing all solutions that I tried, because there are a lot of them and none of them brought me closer to my goal. My main concept is below.

available_courses = (db.session
                     .query(CourseModel)
                     .join(attending)
                     .join(StudentModel)
                     .filter(StudentModel.id == student_id)
                     .where(StudentModel.courses is None)  # can't find working condition for this place
                     )

Answer 1

You can use a subquery to gather the attended courses and then use that in a filter to get the missing courses:

from sqlalchemy.sql import select, not_
attended_courses_subq = select(attending.c.course_id).filter(attending.c.student_id == 1)
available_courses = session.query(CourseModel).filter(not_(CourseModel.id.in_(attended_courses_subq))).all()