CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-views
JarochoEngineer
JarochoEngineer

Reputation: 1787

Reduction of queries in ManyToMany field with prefetch_related

I want to reduce further the number of queries. I used prefetch_related decreasing the number of queries. I was wondering if it is possible to reduce to one query. Please let me show the code involved:

I have a view with prefetch_related:

class BenefitList(generics.ListAPIView):
    serializer_class = BenefitGetSerializer

    def get_queryset(self):
        queryset = Benefit.objects.all()
        queryset = queryset.filter(deleted=False)

        qs=  queryset.prefetch_related('nearest_first_nations__reserve_id')
    
        return qs

I have the models used by the serializers. In here, it is important to notice the hybrid property name which I want to display along with reserve_id and reserve_distance:

benefit.py:

class IndianReserveBandDistance(models.Model):
    reserve_id = models.ForeignKey(IndianReserveBandName,
                                   on_delete=models.SET_NULL,
                                   db_column="reserve_id",
                                   null=True)
    reserve_distance = models.DecimalField(max_digits=16, decimal_places=4, blank=False, null=False)

    @property
    def name(self):
        return self.reserve_id.name
        
class Benefit(models.Model):
    banefit_name = models.TextField(blank=True, null=True)
    nearest_first_nations = models.ManyToManyField(IndianReserveBandDistance,
                                                   db_column="nearest_first_nations",
                                                   blank=True,
                                                   null=True)

Name field is obtained in the model IndianReserveBandName.

indian_reserve_band_name.py:

class IndianReserveBandName(models.Model):
    ID_FIELD = 'CLAB_ID'
    NAME_FIELD = 'BAND_NAME'

    name = models.CharField(max_length=127)
    band_number = models.IntegerField(null=True)

Then, the main serializer using BenefitIndianReserveBandSerializer to obtain the fields reserve_id, reserve_distance and name:

get.py: class BenefitGetSerializer(serializers.ModelSerializer): nearest_first_nations = BenefitIndianReserveBandSerializer(many=True)

The serializer to obtain the mentioned fields: distance.py:

class BenefitIndianReserveBandSerializer(serializers.ModelSerializer):
    class Meta:
        model = IndianReserveBandDistance
        fields = ('reserve_id', 'reserve_distance', 'name')

The above is resulting in two queries which I would like to be one:

SELECT ("benefit_nearest_first_nations"."benefit_id") AS "_prefetch_related_val_benefit_id",
       "indianreservebanddistance"."id",
       "indianreservebanddistance"."reserve_id",
       "indianreservebanddistance"."reserve_distance"
  FROM "indianreservebanddistance"
 INNER JOIN "benefit_nearest_first_nations"
    ON ("indianreservebanddistance"."id" = "benefit_nearest_first_nations"."indianreservebanddistance_id")
 WHERE "benefit_nearest_first_nations"."benefit_id" IN (1, 2)
 
 
 SELECT "indianreservebandname"."id",
       "indianreservebandname"."name"
  FROM "indianreservebandname"
 WHERE "indianreservebandname"."id" IN (678, 140, 627, 660, 214, 607)
 ORDER BY "indianreservebandname"."id" ASC

I am expecting the following query:

SELECT ("benefit_nearest_first_nations"."benefit_id") AS "_prefetch_related_val_benefit_id",
       "indianreservebanddistance"."id",
       "indianreservebanddistance"."reserve_id",
       "indianreservebanddistance"."reserve_distance",
       "indianreservebandname"."name"
  FROM "indianreservebanddistance"
 INNER JOIN "benefit_nearest_first_nations"
    ON ("indianreservebanddistance"."id" = "benefit_nearest_first_nations"."indianreservebanddistance_id")
 inner JOIN  "indianreservebandname"
    on ("indianreservebandname"."id" = "indianreservebanddistance"."reserve_id")
 WHERE "benefit_nearest_first_nations"."benefit_id" IN (1, 2)

Would you know if it is possible to get just one query? Am I missing something which is stopping Django to create just one query?

Thanks a lot

Upvotes: 1

Views: 69

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477513

Am I missing something which is stopping Django to create just one query?

Yes. The behavior of using two queries is on deliberately. It prevents introducing data duplication, where the same values for the same columns are repeated a lot. This can blow up memory usage (both at the database side and the Django/Python side), and render the system unresponsive. In fact, it can even result in the out of memory (OOM) manager killing the web application, the database, or another application.

Upvotes: 0

Related Questions