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)
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
)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.