peewee: filter select query results from many to many relationship
Question: I have the following code

    
    
    #!/usr/bin/env python
    
    """doc"""
    
    import peewee
    
    db = peewee.SqliteDatabase(":memory:")
    
    
    class BaseModel(peewee.Model):  # pylint: disable=W0232
        """base model"""
    
        class Meta:  # pylint: disable=C0111,W0232,R0903
            database = db
    
    
    class Student(BaseModel):
        """doc"""
        name = peewee.CharField()
    
    
    class Course(BaseModel):
        """doc"""
        name = peewee.CharField()
    
    
    class StudentCourse(BaseModel):
        """doc"""
        student = peewee.ForeignKeyField(Student)
        course = peewee.ForeignKeyField(Course)
    
    Student.create_table()
    Course.create_table()
    StudentCourse.create_table()
    
    s1 = Student(name="Student1")
    s1.save()
    s2 = Student(name="Student2")
    s2.save()
    s3 = Student(name="Student3")
    s3.save()
    s4 = Student(name="Student4")
    s4.save()
    c1 = Course(name="course1")
    c1.save()
    c2 = Course(name="course2")
    c2.save()
    c3 = Course(name="course3")
    c3.save()
    sc21 = StudentCourse(student=s2, course=c1)
    sc21.save()
    sc22 = StudentCourse(student=s2, course=c2)
    sc22.save()
    sc23 = StudentCourse(student=s2, course=c3)
    sc23.save()
    sc31 = StudentCourse(student=s3, course=c1)
    sc31.save()
    sc32 = StudentCourse(student=s3, course=c2)
    sc32.save()
    sc41 = StudentCourse(student=s4, course=c1)
    sc41.save()
    
    query = (
        StudentCourse
        .select(StudentCourse, Student, Course)
        .join(Course)
        .switch(StudentCourse)
        .join(Student)
        .order_by(Student.name))
    
    last = None
    for student_course in query:
        student = student_course.student
        if student != last:
            last = student
            print "Student: %s" % student.name
        print "    - %s" % student_course.course.name
    

that produces this output:

    
    
    Student: Student2
        - course1
        - course2
        - course3
    Student: Student3
        - course1
        - course2
    Student: Student4
        - course1
    

This is basically the example from [peewee
docs](http://peewee.readthedocs.org/en/latest/peewee/models.html#implementing-
many-to-many)

I need to filter the results and pick only those students who have all the
courses from a given subset:

    
    
    courses = ["course1", "course2"]
    

I have tried to use the **IN** operator and modified the select query like
this:

    
    
        query = (
        StudentCourse
        .select(StudentCourse, Student, Course)
        .join(Course)
        .switch(StudentCourse)
        .join(Student)
        .where(Course.name << courses)
        .order_by(Student.name))
    

But it produces a wrong result, which means I'm doing it wrong:

    
    
    Student: Student2
        - course1
        - course2
    Student: Student3
        - course1
        - course2
    Student: Student4
        - course1
    

There are two wrong things here:

  1. **course3** for **Student2** is not printed, I need it. I assume this is because **course3** isn't in **courses** list.
  2. **Student4** gets selected, but it shouldn't, because **Student4** doesn't have both **course1** and **course2** , yet **course1** is **IN** the **courses** list.

Is it possible to achieve what I want in just one query or I have to iterate
over students, make a **SELECT** query for each of them and match the list of
their courses against my **courses** filter?

My goal is to get this output:

    
    
    Student: Student2
        - course1
        - course2
        - course3
    Student: Student3
        - course1
        - course2
    


Answer: This should get you pointed in the right direction: <https://www.simple-
talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-
division/>

