Posts tagged Django ORM
… 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.