Django ORM filter model where all relation have value equals to

Question

Lets say that I have two models:

class Worker(models.Model):
    name = models.CharField(max_length=100)
    
    
class Task(models.Model):
    worker = models.ForeignKey(Worker)
    name = models.CharField(max_length=100)

I would like to retrieve all workers where ALL tasks are called "dig". But I dont want workers that have only one Task called "dig".

I've tried using filter and exclude with Q, like this:

Worker.objects.filter(task__name='dig').exclude(~Q(task__name='dig'))

But it didn't work, it don't remove those that have only one task like that.

I could iterate over worker and tasks to find it, but is there any way to make this query using only orm?

Answer 1

Annotate your queryset with the count of matching tasks and a total count, then filter where the total is greater than one and the number matching is equal to the total

from django.db.models import Count, F, Q
Worker.objects.annotate(
    num_tasks=Count('task'),
    num_digs=Count('task', filter=Q(task__name='dig'))
).filter(
    num_tasks__gt=1,
    num_tasks=F('num_digs')
)