How to query SQLAlchemy with an association table, an in clause and a sub query?-CodePudding

I have a course table and a student table defined as

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200))
    tutor = db.Column(db.Integer, db.ForeignKey("tutor.id"))
    students = db.relationship("Student", secondary=association_table)


class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(50))
    courses = db.relationship("Course", secondary=association_table)

and for a many to many relationship between them, I have created an associated table

association_table = db.Table(
    "association",
    db.Model.metadata,
    db.Column("course_id", db.ForeignKey("course.id")),
    db.Column("student_id", db.ForeignKey("student.id")))

I would like to query and get results of all courses taken by a particular student. The SQL query would be like

SELECT * FROM course WHERE course.id IN (SELECT course_id FROM association WHERE student_id = 2)

How do I do this using sqlalchemy?

One of my many failed attempts to do this is

query_ = self.session.query(Course)
query2 = self.session.query(association_table.course_id).all().filter_by(student_id=view_kwargs.get('student_id'))
query_ = query_.filter(Course.id.in_(query2)).all()

At present, I'm getting this when I'm calling this using API from Postman.

AttributeError: 'Table' object has no attribute 'course_id'

CodePudding user response：

The IN clause with sub-select is not the most elegant (and at least in the past, also not the most optimal way to execute the query).

In order to use JOIN clause with absolutely the same result please try the query below which is very succinct:

q = session.query(Course).filter(Course.students.any(Student.id == 2))

and will produce following SQL:

SELECT course.id,
       course.name
FROM course
WHERE EXISTS
    (SELECT 1
     FROM association,
          student
     WHERE course.id = association.course_id
       AND student.id = association.student_id
       AND student.id = ?)

However, to avoid joining on the student table alltogether, the below more explicit query will result in the same outcome:

q = (
    session
    .query(Course)
    .join(
        association_table, 
        (Course.id == association_table.c.course_id) & (association_table.c.student_id == 2),
    )
)

which will result in the SQL similar to below:

SELECT  course.id,
        course.name
FROM    course
   JOIN association
     ON course.id = association.course_id
    AND association.student_id = ?

CodePudding user response：

Update: I have found a solution that works and which is quite simple actually. It goes like:

session.query(Course).join(association_table).filter_by(student_id=view_kwargs["student_id"])