Django- Duplicated queries in nested models querying with ManyToManyField

Here is my approach on how to overcome the multiple queries being made.

from collections import defaultdict

from rest_framework.serializers import SerializerMethodField


class GameSerializer(serializers.ModelSerializer):

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


class GenreGameSerializer(serializers.ModelSerializer):
    children = SerializerMethodField(source='get_children')
    games = GameSerializer(many=True)

    class Meta:
        model = Genre
        fields = ['id', 'name', 'games']
    
    def get_children(self, obj):
        # get genre childrens from context and pass it to same serializer
        # no extra queries are done, since we alredy have the instances
        children = self.context['children'].get(obj.id, [])
        serializer = GenreGameSerializer(children, many=True, context=self.context)
        return serializer.data


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

    def get(self, request, *args, **kwargs):
        # gather genres from queryset class attribute and prefetch games
        genres = self.get_queryset().prefetch_related('games')

        # gather all descendants of root nodes and prefetch games
        genre_descendants = genres.get_descendants().prefetch_related('games')
        
        # create a dictionary with key parent and value list of children
        # this will not require extra queries
        children_dict = defaultdict(list)
        for descendant in descendants:
            children_dict[descendant.parent_id].append(descendant)
        
        # add the dictionary as context for serializer
        context = self.get_serializer_context()
        context['children'] = children_dict
        
        # send the context to serializer    
        ser = GenreGameSerializer(data=genres, context=context, many=True)
        return Response(ser.data)

The GamesByGenreAPI class can be written nicer, by overriding self.get_queryset() and self.get_serializer_context() but I tried to keep it in one method for better understanding.

The prefetch_related only works on the root level tree, because it is only specified in that query. Subsequent child's obtained by a new query generated with RecursiveField do not have these related objects pre-fetched. Probably you can overwrite the queries in the RecursiveField, but I think it runs a separate new query on each attribute of childrens found.

Well, if you wants to reduce the queries to only 3, you would need get all the data from the models and manually build the output array in a recursive way.

This is a very dirty code, you lose the "Django's magic", and as in the comment, I think it is a waste of time in most scenarios.

class GamesByGenreAPI(APIView):

    def get(self, request, *args, **kwargs):
        games = {}
        for g in Game.objects.all():
            games[g.pk] = {
                'id': g.id,
                'name': g.name
            }

        TreeGame = Game.genre.through
        tree_game = {}
        for tg in TreeGame.objects.all():
            if tg.genre_id not in tree_game:
                tree_game[tg.genre_id] = []
            tree_game[tg.genre_id].append(tg.game_id)

        childrens = {}
        roots = []
        for g in Genre.objects.all():
            if g.level == 0:
                roots.append(g)
            else:
                if g.parent_id not in childrens:
                    childrens[g.parent_id] = []
                childrens[g.parent_id].append(g)

        def _get_data_from_tree_branch(game):
            branch_data = {
                'id': game.pk,
                'name': game.name
            }
            if game.pk in childrens:
                # Move up if you need a children array in every response
                branch_data['children'] = []
                for c in childrens[game.pk]:
                    branch_data['children'].append(
                        _get_data_from_tree_branch(c)
                    )
            if game.pk in tree_game:
                # Move up if you need a games array in every response
                branch_data['games'] = []
                for rel in tree_game[game.pk]:
                    branch_data['games'].append(games[rel])

            return branch_data
    
        data = []
        for g in roots:
            data.append(_get_data_from_tree_branch(g))
        return Response(data)

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)

You missed children__games relation in prefetch_related(). It will work if you replace

prefetch_related('children__children', 'games')

with

prefetch_related('children__children', 'children__games', 'games')

Here is request list at Django debug panel