Reputation: 14414
Given:
class Video(models.Model):
tags = models.ManyToManyField(Tag)
class Tag(models.Model):
name = models.CharField(max_length=20)
I know I can use Video.objects.filter(tags__name__in=['foo','bar'])
to find all Videos
that have either foo
OR bar
tags, but in order to find those that have foo
AND bar
, I'd have to join the foreign key twice (if I was handwriting the SQL). Is there a way to accomplish this in Django?
I've already tried .filter(Q(tag__name='foo') & Q(tag__name='bar'))
but that just creates the impossible to satisfy query where a single Tag
has both foo
and bar
as its name.
Upvotes: 1
Views: 86
Reputation: 477883
This is not as straighfroward as it might look. Furthermore JOIN
ing two times with the same table is typically not a good idea at all: imagine that your list contains ten elements. Are you going to JOIN
ten times? This easily would become infeasible.
What we can do however, is count the overlap. So if we are given a list of elements, we first make sure those elements are unique:
tag_list = ['foo', 'bar']
tag_set = set(tag_list)
Next we count the number of tags of the Video
that are actually in the set, and we then check if that number is the same as the number of elements in our set, like:
from django.db.models import Q
Video.objects.filter(
Q(tag__name__in=tag_set) | Q(tag__isnull=True)
).annotate(
overlap=Count('tag')
).filter(
overlap=len(tag_set)
)
Note that the Q(tag__isnull-True)
is used to enable Video
s without tags. This might look unnecessary, but if the tag_list
is empty, we thus want to obtain all videos (since those have zero tags in common).
We also make the assumption that the names of the Tag
s are unique, otherwise some tags might be counted twice.
Behind the curtains, we will perform a query like:
SELECT `video`.*, COUNT(`video_tag`.`tag_id`) AS overlap
FROM `video`
LEFT JOIN `video_tag` ON `video_tag`.`video_id` = `video`.`id`
LEFT JOIN `tag` ON `tag`.`id` = `video_tag`.`tag_id`
WHERE `tag`.`name` IN ('foo', 'bar')
GROUP BY `video`.`id`
HAVING overlap = 2
Upvotes: 1