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?
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 Project
s once, and once for the prefetched object.