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()
  3. class Course(Model):
  4. name = CharField()
  5. class StudentCourse(Model):
  6. student = ForeignKeyField(Student)
  7. 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'))
  6. for course in courses:
  7. 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
  3. db = SqliteDatabase('school.db')
  4. class BaseModel(Model):
  5. class Meta:
  6. database = db
  7. class Student(BaseModel):
  8. name = CharField()
  9. class Course(BaseModel):
  10. name = CharField()
  11. students = ManyToManyField(Student, backref='courses')
  12. StudentCourse = Course.students.get_through_model()
  13. db.create_tables([
  14. Student,
  15. Course,
  16. StudentCourse])
  17. # Get all classes that "huey" is enrolled in:
  18. huey = Student.get(Student.name == 'Huey')
  19. for course in huey.courses.order_by(Course.name):
  20. print(course.name)
  21. # Get all students in "English 101":
  22. engl_101 = Course.get(Course.name == 'English 101')
  23. for student in engl_101.students:
  24. print(student.name)
  25. # When adding objects to a many-to-many relationship, we can pass
  26. # in either a single model instance, a list of models, or even a
  27. # query of models:
  28. huey.courses.add(Course.select().where(Course.name.contains('English')))
  29. engl_101.students.add(Student.get(Student.name == 'Mickey'))
  30. engl_101.students.add([
  31. Student.get(Student.name == 'Charlie'),
  32. Student.get(Student.name == 'Zaizee')])
  33. # The same rules apply for removing items from a many-to-many:
  34. huey.courses.remove(Course.select().where(Course.name.startswith('CS')))
  35. engl_101.students.remove(huey)
  36. # Calling .clear() will remove all associated objects:
  37. cs_150.students.clear()

For more examples, see: