Django ORM filter model where all relation have value equals to-CodePudding

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?

CodePudding user response：

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')
)