Django [SOLVED]: Django SQL query duplicated 2 times

Django [SOLVED]: Django SQL query duplicated 2 times

Home Forums Frameworks Django Tutorials Django [SOLVED]: Django SQL query duplicated 2 times

Tagged: ,

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #246534

    Cloudy Point
    Keymaster

    QuestionQuestion

    Model.py:

    class Match(models.Model):
        home_team = models.CharField(max_length=200)
        away_team = models.CharField(max_length=200)
    class Stat(models.Model):
        match = models.ForeignKey(Match)
        team = models.CharField(max_length=100)
        goals = models.IntegerField(default=0)
        assists = models.IntegerField(default=0)
    

    views.py

    context_dict = {}
    match = Match.objects.get(pk=1)
    home_stat = Stat.objects.get(match=match, team=match.home_team)
    away_stat = Stat.objects.get(match=match, team=match.away_team)
    
    context_dict['home_stat'] = home_stat
    context_dict['away_stat'] = away_stat
    
    return render(request, 'index.html', context_dict)
    

    template

    goals: {{ home_stat.goals }} : {{ away_stat.goals }}
    assists: {{ home_stat.assists }} : {{ away_stat.assists }}
    

    django-debug-toolbar shows two duplicated queries:
    https://sfault-image.b0.upaiyun.com/220/984/2209840587-5a3e5ccccec87_articlex

    SELECT "myapp_stat"."id", "myapp_stat"."match_id", "myapp_stat"."team", "myapp_stat"."goals", "myapp_stat"."assists" FROM "myapp_stat" WHERE ("myapp_stat"."match_id" = '1' AND "myapp_stat"."team" = '''TeamA''')
      Duplicated 2 times. 
    F:myprojectmyapp/views.py in index(11)
      home_stat = Stat.objects.get(match=match, team=match.home_team)
    
    SELECT "myapp_stat"."id", "myapp_stat"."match_id", "myapp_stat"."team", "myapp_stat"."goals", "myapp_stat"."assists" FROM "myapp_stat" WHERE ("myapp_stat"."match_id" = '1' AND "myapp_stat"."team" = '''TeamB''')
      Duplicated 2 times. 
    
    F:myprojectmyapp/views.py in index(12)
    away_stat = Stat.objects.get(match=match, team=match.away_team)
    

    how to fix this?

    #246535

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    1. As you have ForeignKey in Stat to Match you can simplify your query using related name stat_set which is provided by default by Django.

      match.stat_set.filter(...)
      
    2. You can filter stats by team__in lookup. This means that you need one sql query to fetch both stats.

      both_stats = match.stat_set.filter(team__in=[match.home_team, match.away_team])     
      
    3. What is left is to get them as separate objects.

      for stat in both_stats:
          if stat.team == match.home_team:
              context_dict['home_stat'] = stat
          elif stat.team == match.away_team:
              context_dict['away_stat'] = stat
      
    4. Overall this decreases the number of sql queries from 2 to 1 but increases python work.

    Source: https://stackoverflow.com/questions/47970106/django-sql-query-duplicated-2-times
    Author: bellum
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.