Django REST framework One-to-many relationship and database queries

user_7413066 0
Viewed 408

I am new to django and I am developing my first project with Django REST framework.

Models:

class Sticker(Model):
    image = models.ImageField(upload_to=sticker_image_directory_path, null=False)
    title = models.CharField(max_length=150)

    def __str__(self):
        return self.title

class StickerTag(Model):
    name = models.CharField(max_length=100)
    sticker = models.ForeignKey(Sticker, on_delete=models.RESTRICT, related_name='tags')

    def __str__(self):
        return self.name

Serializer:

class StickerSerializer(serializers.ModelSerializer):
    tags = serializers.StringRelatedField(many=True)
    
    class Meta:
        model = models.Sticker
        fields = ['id', 'image', 'title', 'tags', 'countries']

View:

class StickerView(mixins.ListModelMixin,
                  viewsets.GenericViewSet):
    serializer_class = serializers.StickerSerializer
    queryset = models.Sticker.objects.all()

I was debugging call to http://127.0.0.1:8000/stickers/ with django-debug-toolbar. I was surprised to see that for each sticker instance Django makes a query to stickertag table like that:

SELECT `stickerapp_stickertag`.`id`,
       `stickerapp_stickertag`.`name`,
       `stickerapp_stickertag`.`sticker_id`
  FROM `stickerapp_stickertag`
 WHERE `stickerapp_stickertag`.`sticker_id` = 1

Means it is grabbing sticker tags for each sticker one by one. So if there are 10 stickers it will make 10 DB calls with that above query. But I think that those are too many queries and can be reduced by using "IN" clause of MYSQL for example:

SELECT * FROM stickerapp_stickertag where sticker_id in (1,2,3,4,5);

I don't know how to do that in Django REST framework. Kindly guide!

django-rest-framework mysql django python
1 Answers

You can .prefetch_related(…) [Django-doc] the relevant Tag objects:

class StickerView(mixins.ListModelMixin, viewsets.GenericViewSet):
    serializer_class = serializers.StickerSerializer
    queryset = models.Sticker.objects.prefetch_related('tags')

This will make two queries, first a query to obtain all Sticker objects, and then a second query that will for each Sticker fetch the relevant tags. This is done in a single query for all Stickers.

user_675790
Related