AND query against foreign key table in django ORM

Question

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.

willeM_ Van Onsem · Accepted Answer · 2018-06-12 19:34:02Z

1

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

answered Jun 12, 2018 at 19:34

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arne Claassen Over a year ago

I hadn't tried going the annotate route because I didn't realize you could use filter to generate group by/having SQL. Thanks. This is better than multiple joins

Collectives™ on Stack Overflow

AND query against foreign key table in django ORM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related