CODEWITHSUNDEEP
djangodjango-orm
Arne Claassen
Arne Claassen

Reputation: 14414

AND query against foreign key table in django ORM

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

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477883

This is not as straighfroward as it might look. Furthermore JOINing 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 Videos 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 Tags 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

Related Questions