[Django]-How to efficiently hit the database for a related field in REST serializer?

by

4👍

✅

I think the best you can do is first get the id of the last PlayerStatsHistory of each Player(using group_by):

latest_stats_history_pks = PlayerStatsHistory.objects.values('player').annotate(max_id=models.Max('id')).values_list('max_id', flat=True)

(it has a problem, it uses all of the players if you are using pagination you don’t need all of them, in that case, prefetch without filtering its queryset should be fine)

then only prefetch this values on your Player queryset, so:

       queryset = Player.objects.all().prefetch_related(models.Prefetch(
        'playerstatshistory_set',
        queryset=PlayerStatsHistory.objects.filter(pk__in=latest_stats_history_pks), to_attr='last_stat_list'))

so finally your get_queryset method in your view should be like:

def get_queryset(self):

    latest_stats_history_pks = PlayerStatsHistory.objects.values('player').annotate(max_id=models.Max('id')).values_list('max_id', flat=True)

    queryset = Player.objects.all().prefetch_related(models.Prefetch(
        'playerstatshistory_set',
        queryset=PlayerStatsHistory.objects.filter(pk__in=latest_stats_history_pks), to_attr='last_stat_list'))

    return queryset

and if you’re using FBV, do something like this:

from rest_framework.decorators import api_view
from rest_framework.response import Response

@api_view(['GET'])
def player_list(request, format=None):
    if request.method == 'GET':
        latest_stats_history_pks = PlayerStatsHistory.objects.values('player').annotate(max_id=models.Max('id')).values_list('max_id', flat=True)

        players = Player.objects.all().prefetch_related(models.Prefetch(
        'playerstatshistory_set',
        queryset=PlayerStatsHistory.objects.filter(pk__in=latest_stats_history_pks), to_attr='last_stat_list'))

        serializer = PlayerSerializer(players, many=True)
        return Response(serializer.data)

also in your serializer, change that field as below:

class PlayerSerializer(HyperlinkedModelSerializer):
    details = SerializerMethodField()

    def get_details(self, obj):
        return {} if not obj.last_stat_list else PlayerStatsSerializer(obj.last_stat_list[-1]).data

    class Meta:
        model = Player
        fields = ('name', 'details')
👤Ehsan Nouri

Leave a comment