pythondjangodjango-rest-frameworkdjango-querysetdjango-mptt

Django- Duplicated queries in nested models querying with ManyToManyField


How do I get rid of the duplicated queries as in the screenshot?

enter image description here


I have two models as following,

class Genre(MPTTModel):
    name = models.CharField(max_length=50, unique=True)
    parent = TreeForeignKey('self', on_delete=models.CASCADE, null=True, 
                             blank=True, related_name='children')

    def __str__(self):
        return self.name


class Game(models.Model):
    name = models.CharField(max_length=50)
    genre = models.ManyToManyField(Genre, blank=True, related_name='games')

    def __str__(self):
        return self.name

and have a serializer and views,

class GameSerializer(serializers.ModelSerializer):

    class Meta:
        model = Game
        exclude = ['genre', ]


class GenreGameSerializer(serializers.ModelSerializer):
    children = RecursiveField(many=True)
    games = GameSerializer(many=True,)

    class Meta:
        model = Genre
        fields = ['id', 'name', 'children', 'games']


class GamesByGenreAPI(APIView):
    queryset = Genre.objects.root_nodes()
    serializer_class = GenreGameSerializer

    def get(self, request, *args, **kwargs):
        ser = GenreGameSerializer(data=Genre.objects.root_nodes()
                                      .prefetch_related('children__children', 'games'), many=True)
        if ser.is_valid():
            pass
        return Response(ser.data)

so basically the model populated when serialized looks like this enter image description here

The result is what I am expecting but there are n duplicated queries for each of the genre. How can I fix it? Thanks..

here is a paste https://pastebin.com/xfRdBaF4 with all code, if you want to reproduce the issue.
Also add path('games/', GamesByGenreAPI.as_view()), in urls.py which is omitted in paste.

Update

tried logging queries to check if its issue with debug toolbar, but it is NOT, the queries are duplicated.. here is the screenshot. enter image description here


Solution

  • From debug toolbar output I will asume that you have two level of nesting in your Genre model (root, Level 1). I do not know if the Level 1 has any children, i.e. there are Level 2 genres, since I can't view the query results (but this is not relevant for the current problem).

    The root level Genres are (1, 4, 7), the Level 1 are (2, 3, 5, 6, 8, 9). The prefetch worked for these lookups prefetch_related("children__children") as the queries are grouped in two separate queries, as it should be.

    The next query for games related to root level genres (prefetch_related("games")) are also prefetched. It is the fourth query in the debug toolbar output.

    The next queries as you can see are getting the games for each of Level 1 genres in a separate query, which I presume are triggered from the serialiser fields, since there are no lookups specified in the view, that could prefetch those records. Adding another prefetch lookup targeted at those records should solve the problem.

    ser = GenreGameSerializer(data=Genre.objects.root_nodes()
                                        .prefetch_related(
                                            'children__children', 
                                            'games'
                                            # prefetching games for Level 1 genres 
                                            'children__games'),       
                              many=True)
    

    Note, that if there are more nested genres, the same logic should be applied for each nesting level. For example, if there are Level 2 genres, then you should prefetch the related games for those genres with:

    ser = GenreGameSerializer(data=Genre.objects.root_nodes()
                                        .prefetch_related(
                                            'children__children', 
                                            'games'
                                            'children__games',
                                            'children__children__games'), 
                              many=True)