Posts tagged with Django ORM

Querying ManyToMany fields in Django...

Published at March 23, 2011 | Tagged with: , , , ,

... or how to select items that have one or more categories matching with other item categories

The problem: having a Many To Many relation(ManyToManyField) is really useful when you have to link one object to many other. For example one artist may play in several styles, or a teacher can teach more than one subjects. I`ll use the latter example for the post. If you want to get all teachers that teach (for example) "music" you just have to do something like:

Teacher.objects.filter(subjects = music_obj)
The real problem comes when you want to select all teachers that have subject matching one or more of another teacher subjects. For example "teacher_a" teaches several subject and we want to find all his colleges that teach at least one of his. Solution: to achieve this we have to use the Q object(imported from django.db.models) in a way similar to the making of "OR" query. 
from django.db.models import Q
x = Q()
for subject in teacher_a.subjects.all():
    x = x | Q(subjects = subject) 
colegues = Teacher.objects.filter(x).distinct()

Lets see what happens line by line:

1) import the Q class
2) create empty Q object
3) iterate over every subject taught by the specified teacher
4) on every iteration we add an "OR"-clause to the object corresponding to the current subject
5) we use the generated query as a filter

Have in mind that after using the Q object you have to sort your result(manually add order_by clause), because when you use Q the default ordering is not considered.

Final words: The speed of this method is not tested but to be honest I suspect that this can bring performance problems on big tables. In that case maybe you can speed the things up with custom SQL query. So the choice is yours - database independence or speed. Both ways have their pros and cons and its case specific which one you will choose.
If you have found a better way or want to add something feel free to comment.

Tags