pythondjangoormannotatedjango-prefetch-related

How to exclude instances with empty querysets in prefetch_related?


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.