CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Nick
Nick

Reputation: 91

Prefetch related with a filter that contains a field of the original model

If I have Model A with a ForeignKey to Model B, how can I prefetch Model A from Model B using some criteria in Model B?

This is something what I would want (OuterRef obviously only works in a SubQuery, but this is basically the functionality I need):

class ModelA(models.Model):
    somecriteria = models.CharField()


class ModelB(models.Model):
    somerelation = models.ForeignKey(ModelA)
    someattribute = models.CharField()


qs = ModelA.objects.all()
qs = qs.prefetch_related(
    Prefetch(
       'modelb_set', 
       queryset=ModelB.objects.filter(someattribute=OuterRef('somecriteria')), 
       to_attr='some_attr'
    ),
)

The query is supposed to do the following (in less queries):

for obj in qs:
    # Change this to new_qs = obj.some_attr if prefetch is working.
    newqs = obj.modelb_set.filter(someattribute=obj.somecriteria)
    if newqs.exists(): 
        # Do something

Upvotes: 9

Views: 1637

Answers (1)

Javier Buzzi
Javier Buzzi

Reputation: 6828

modelb_qs = models.Subquery(
    ModelB.objects.filter(
        someattribute=models.OuterRef("somerelation__somecriteria")
    ).values("id")
)
qs = ModelA.objects.prefetch_related(
    models.Prefetch("modelb_set", queryset=ModelB.objects.filter(id__in=modelb_qs))
)

for obj in qs:
    if len(obj.modelb_set.all()): 
        # Do something

This will ONLY make 2 queries, if you stick a filter() inside the loop, you're going to make N(O^2) queries... not good.

I wrote a test to prove this only make 2 queries, you can find it here:

https://gist.github.com/kingbuzzman/fd2b635f2cf011f1330a6be088ce3664#file-modelabquery-py-L125

Upvotes: 2

Related Questions