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, ManyToManyField may work.

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()

Attention

Before many-to-many relationships can be added, the objects being referenced will need to be saved first. In order to create relationships in the many-to-many through table, Peewee needs to know the primary keys of the models being referenced.

For more examples, see: