CODEWITHSUNDEEP
pythondjangoormdjango-select-related
Pietro
Pietro

Reputation: 1836

Django select_related join model attributes with a single query

I'm trying to find a optimal solution with a single query to retrieve attributes on a join model.

I have the following models relationships:

class Player(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    season_team = models.ForeignKey(SeasonTeam, related_name='players', blank=True, null=True)
    leagues = models.ManyToManyField(League, related_name='players', through='PlayerLeague')


class League(models.Model):
    name = models.CharField()


class PlayerLeague(models.Model):
    player = models.ForeignKey(Player)
    league = models.ForeignKey(League)
    stamina = models.PositiveSmallIntegerField(_('Stamina'), default=100)


class Team(models.Model):
    name = models.CharField(max_length=30, validators=[
        RegexValidator(regex='^[a-zA-Z0-9\s]+$', message=_('name must contain only chars and numbers'), ), ])
    players = models.ManyToManyField(Player, blank=True, related_name='teams')
    league = models.ForeignKey(League, related_name='teams')

This is the view where I want all players loaded in get_context_data to preload stamina attribute

class FormationUpdateView(AjaxResponseMixin, FormationRequirementsMixin, UpdateView):
    model = Formation
    template_desktop = 'hockey/formation/form.html'
    template_mobile = 'hockey/formation/mobile/form.html'
    form_class = FormationForm

    def get_initial(self):
        self.initial = {'active': True}
        return self.initial.copy()

    def get_context_data(self, **kwargs):
        context = super(FormationUpdateView, self).get_context_data(**kwargs)
        team = Team.objects.select_related('league').get(formations=self.kwargs['pk'])
        context['players'] = Player.objects.filter(teams=team).all()
        context['league'] = team.league
        return context

    def get_queryset(self):
        return Formation.objects.select_related()

    def get_template_names(self):
        if 'Mobile' in self.request.META['HTTP_USER_AGENT']:
            return self.template_mobile
        return self.template_desktop

My first solution was by adding a stamina method to Player class:

def stamina(self, league):
    p_l = self.leagues.through.objects.filter(league=league)[0]
    return p_l.stamina

I don't like this solution because is going to query for each player its stamina value.

Thank you for any help

Upvotes: 1

Views: 1165

Answers (1)

Felix D.
Felix D.

Reputation: 2220

Your models are a little mind-twisting, but something like that should minimize the number of requests

from django.db.models import Q, F, Prefetch

... 

def get_context_data(self, **kwargs)
     context = super(FormationUpdateView, self).get_context_data(**kwargs)
     team = (Team.objects
     .select_related('league')
     .prefetch_related(
         Prefetch(
             'players',
             queryset=(Player.objects.filter(leagues=F('leagues'))
                       .prefetch_related('playerleagues'))
         )
     ).get(formations=self.kwargs['pk']))
     context['players'] = team.players.all()
     context['league'] = team.league
     return context

To get the stamina of a player without hitting the database again.

players[0].playerleagues.all()[0].stamina

Upvotes: 2

Related Questions