How to exclude instances with empty querysets in prefetch_related?

March 27, 2024

I have two models – Project and Contract.
They have one-to-many relationship.

class Contract(models.Model):
    project = models.ForeignKey(Project)

I get a qs of Project instances with relevant Contract instance(s).

projects = Project.objects.filter(active=True).prefetch_related(
        Prefetch('contract_set', queryset=Contract.objects.filter(**filters), to_attr='contracts')
    )

But now I need to get Project instances with exact Contract instance by putting Contract guid to filters.

filters: Dict = {
  'guid': some_guid,
}

But I receive all Project instances where only one has not empty contracts attr and others Project instances have empty contracts attr.

I found such issues:
How to exclude rows with empty prefetch_related field
Filter prefetch_related empty in django
but they do not help me.

I tried such options:
a) using OuterRef and Exist:

projects = Project.objects.filter(**filters_projects).annotate(
    has_contracts=Exists(
        Contract.objects.filter(project_id=OuterRef('pk'), **filters_contracts)
    )
)

projects = projects.filter(has_contracts=True)

b) using annotate:

projects = (
        Project.objects.filter(**filters_projects)
        .annotate(num_contracts=Count('contract'))
        .exclude(num_contracts=0)
        .prefetch_related(
            Prefetch('contract_set', queryset=Contract.objects.filter(**filters_contracts), to_attr='contracts')
        )
    )

They do not work for me…

How can I implement the required functionality?

>Solution :

You will have to filter both in the Prefetch object and the Project, so:

projects = (
    Project.objects.filter(
        Exists(
            Contract.objects.filter(
                project_id=OuterRef('pk'), **filters_contracts
            )
        ),
        **filters_projects
    )
    .prefetch_related(
        Prefetch(
            'contract_set',
            queryset=Contract.objects.filter(**filters_contracts),
            to_attr='contracts',
        )
    )
    .distinct()
)

So you use the **filters_contracts twice: once to only retrieve these Projects once, and once for the prefetched object.