CODEWITHSUNDEEP
djangodjango-queryset
Grant Powell
Grant Powell

Reputation: 100

Django Query Optimization count a related field of a related field

I'm writing a Django App for my fraternity to vote on rushees, I'm trying to optimize one of my queries that counts the votes and prints out the count along with information from their applications. Django-debug toolbar is telling me I have many duplicate queries

(the code below has been truncated and edited for clarity)

models.py

 votechoices = ((1, "Yes"),(2, "No"),(3, "Abstain"))

 class Vote(models.Model):
      brother = models.ForeignKey(Brother)
      rushee = models.ForeignKey(Rushee)
      choice = models.IntegerField(choices=votechoices, default=3)

 class Rushee(models.Model):
      first_name = models.CharField(max_length=40)
      last_name = models.CharField(max_length=40)
      #ETC, ETC

 class Application(models.Model):
      rushee = models.ForeignKey(Rushee)
      address = models.CharField(max_length=200)
      cellphone = models.CharField(max_length=30)
      #ETC, ETC

views.py

def getvotecount(request):

     # get all the applications ( we only vote on people who have an application)
     applicationobjs = Application.objects.select_related('rushee').all()

     # iterate through the applications and count the votes
     for app in applicationobjs.iterator():

     #>>>> This Query below is a seperate query everytime! So that means that If we have 200 rushees there are 200 queries!
          counts = Vote.objects.filter(rushee=app.rushee).values('choice').annotate(count=Count('choice'))

          votesfor = sum([x['count'] for x in counts if x['choice'] == 1])
          votesagainst = sum([x['count'] for x in counts if x['choice'] == 2])

          result = [app.rushee.first_name + ' ' + app.rushee.last_name,
                    app.address, app.cellphone,
                    str(votesfor), str(votesagainst),]

    # Uninteresting stuff below that will group together and return the results

I'm trying to optimize the query in the view marked by (>>>>) so that I can return the counts of votes for each rushee without running a seperate query everytime!

additional info: sqlite backend, there are many more rushees than there are applications, and we only vote on the rushees that do have applications

Upvotes: 4

Views: 148

Answers (1)

solarissmoke
solarissmoke

Reputation: 31404

You can use conditional expressions to do this all in one query:

from django.db.models import Case, IntegerField, Sum, When

rushees = Rushee.objects.annotate(
    votes_for=Sum(
        Case(
            When(vote=1, then=1),
            default=0,
            output_field=IntegerField(),
        )
    ),
    votes_against=Sum(
        Case(
            When(vote=2, then=1),
            default=0,
            output_field=IntegerField(),
        )
    )
)

The rushees in the resulting queryset will have a votes_for and votes_against property with the counts for each. This assumes that there are no votes recorded against rushees who don't have applications - but if there were then you could easily filter those out.

Upvotes: 2

Related Questions