Implementing Many to Many

Peewee provides a field for representing many-to-many relationships, much like Django does. This feature was added due to many requests from users, but I strongly advocate against using it, since it conflates the idea of a field with a junction table and hidden joins. It’s just a nasty hack to provide convenient accessors.

To implement many-to-many correctly with peewee, you will therefore create the intermediary table yourself and query through it:

  1. class Student(Model):
  2.     name = CharField()
  4. class Course(Model):
  5.     name = CharField()
  7. class StudentCourse(Model):
  8.     student = ForeignKeyField(Student)
  9.     course = ForeignKeyField(Course)

To query, let’s say we want to find students who are enrolled in math class:

  1. query = (Student
  2.          .select()
  3.          .join(StudentCourse)
  4.          .join(Course)
  5.          .where(Course.name == 'math'))
  6. for student in query:
  7.     print(student.name)

To query what classes a given student is enrolled in:

  1. courses = (Course
  2.            .select()
  3.            .join(StudentCourse)
  4.            .join(Student)
  5.            .where(Student.name == 'da vinci'))
  7. for course in courses:
  8.     print(course.name)

To efficiently iterate over a many-to-many relation, i.e., list all students and their respective courses, we will query the through model StudentCourse and precompute the Student and Course:

  1. query = (StudentCourse
  2.          .select(StudentCourse, Student, Course)
  3.          .join(Course)
  4.          .switch(StudentCourse)
  5.          .join(Student)
  6.          .order_by(Student.name))

To print a list of students and their courses you might do the following:

  1. for student_course in query:
  2.     print(student_course.student.name, '->', student_course.course.name)

Since we selected all fields from Student and Course in the select clause of the query, these foreign key traversals are “free” and we’ve done the whole iteration with just 1 query.

ManyToManyField

The ManyToManyField provides a field-like API over many-to-many fields. For all but the simplest many-to-many situations, you’re better off using the standard peewee APIs. But, if your models are very simple and your querying needs are not very complex, you can get a big boost by using ManyToManyField. Check out the Fields extension module for details.

Modeling students and courses using ManyToManyField:

  1. from peewee import *
  2. from playhouse.fields import ManyToManyField
  4. db = SqliteDatabase('school.db')
  6. class BaseModel(Model):
  7.     class Meta:
  8.         database = db
  10. class Student(BaseModel):
  11.     name = CharField()
  13. class Course(BaseModel):
  14.     name = CharField()
  15.     students = ManyToManyField(Student, backref='courses')
  17. StudentCourse = Course.students.get_through_model()
  19. db.create_tables([
  20.     Student,
  21.     Course,
  22.     StudentCourse])
  24. # Get all classes that "huey" is enrolled in:
  25. huey = Student.get(Student.name == 'Huey')
  26. for course in huey.courses.order_by(Course.name):
  27.     print(course.name)
  29. # Get all students in "English 101":
  30. engl_101 = Course.get(Course.name == 'English 101')
  31. for student in engl_101.students:
  32.     print(student.name)
  34. # When adding objects to a many-to-many relationship, we can pass
  35. # in either a single model instance, a list of models, or even a
  36. # query of models:
  37. huey.courses.add(Course.select().where(Course.name.contains('English')))
  39. engl_101.students.add(Student.get(Student.name == 'Mickey'))
  40. engl_101.students.add([
  41.     Student.get(Student.name == 'Charlie'),
  42.     Student.get(Student.name == 'Zaizee')])
  44. # The same rules apply for removing items from a many-to-many:
  45. huey.courses.remove(Course.select().where(Course.name.startswith('CS')))
  47. engl_101.students.remove(huey)
  49. # Calling .clear() will remove all associated objects:
  50. cs_150.students.clear()

For more examples, see: