Reducing Django Database Queries

Viewed 7012

I have very large dataset and growing, and I need to create many filters but it is going to quickly get out of control and was hoping someone can help me combine some of the queries into a single call. Below is the start of my view.

Call #1 - for loop to display table of all results

traffic = Traffic.objects.all()

Call #2 - Combined aggregate sum query

totals = Traffic.objects.aggregate(Sum('sessions'), Sum('new_users'), Sum('reminder'), Sum('campaigns'), Sum('new_sales'), Sum('sales_renewals'))
    total_sessions = totals.get('sessions__sum')
    total_new_users = totals.get('new_users__sum')
    total_reminder = totals.get('reminder__sum')
    total_campaigns = totals.get('campaigns__sum')
    total_new_sales = totals.get('new_sales__sum')
    total_sales_renewals = totals.get('sales_renewals__sum')

Call #3, #4, #5, #6 and so on... - To filter the database by month and day of week

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=2).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=3).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=4).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=5).aggregate(Sum('sessions'))

total_sessions_2014_m = Traffic.objects.filter(created__year='2014', created__week_day=6).aggregate(Sum('sessions'))

The problem is , I need to create several dozen more filters because I have 3 years of data with multiple data points per column that we need totals the sum for.

Questions:

  1. Can I combine call #1 into call #2
  2. Can I use Call #2 to query the sums for call#3 so I don't have to call all objects from the database to filter it and then do this a couple more dozen times?

As you can see, this is going to get out of control very quickly. Any help would be hugely appreciated. Thank you.

Updated to add Traffic Model

class Timestamp(models.Model):
    created = models.DateField()

    class Meta:
        abstract = True


class Traffic(Timestamp):
    sessions = models.IntegerField(blank=True, null=True)
    new_users = models.IntegerField(blank=True, null=True)
    reminder = models.IntegerField(blank=True, null=True)
    campaigns = models.IntegerField(blank=True, null=True)
    new_sales = models.IntegerField(blank=True, null=True)
    sales_renewals = models.IntegerField(blank=True, null=True)

    # Meta and String
    class Meta:
        verbose_name = 'Traffic'
        verbose_name_plural = 'Traffic Data'

    def __str__(self):
        return "%s" % self.created
3 Answers
Related