Optimize request in the FOR loop-CodePudding

How can I optimize the following request to eliminate loop? Codes count is several hundred, so I get several hundreds database queries, which is unacceptable.

n = 3
result = []
codes = Target.objects.filter(code__in=['ABC', 'CDE', ...])

for code in codes:
    result.append(Data.objects.select_related('target')
                              .filter(target__code=code)
                              .values('spec', 'spec_type')
                              .order_by('-spec')[:n])

Models:

class Data(models.Model):
    target = models.ForeignKey(Target)
    spec_type = models.CharField()
    spec = models.FloatField()

class Target(models.Model):
    code = models.TextField(db_index=True)

CodePudding user response：

You do not have to retrieve the codes as a QuerySet to enumerate over. We can directly work with the list of codes.

If you want to construct a QuerySet that contains all the given elements, you can make a QuerySet with union that will fetch these objects. In that case this can be done with .union(…) [Django-doc]:

codes = ['ABC', 'CDE']
n = 3
result = Data.objects.none().union(
    *[
      Data.objects.filter(target__code=code).values('spec', 'spec_type').order_by('-spec')[:n]
      for code in codes
    ],
    all=True
)

CodePudding user response：

Like @Willem Van Onsem said, you don't need to get get a queryset of your Target objects since you already seem to have the codes that you want. Just store the codes in a variable and then you can do a django query using that list.

codes = ['ABC', 'CDE', ...]

result = Data.objects.filter(target__code__in = codes)

This query should return all Data objects of which the related Target object's code is in the list codes.