CODEWITHSUNDEEP
djangopython-3.xdjango-modelsdjango-templatesdjango-views
Charles Smith
Charles Smith

Reputation: 3289

Total Objects based on Value of Another Object

I am using Django 1.8.3 and Python 3.4.3

I will try and explain this the best I can. If there's a better title for this post, please suggest.

We pull data into our app from email and website traffic sources and then display the data in a table format in the views template. I am adding a table to display totals which will help us visualize our email campaigns better. One thing I would like to render is the total campaign months the current data represents. For example: If we are tracking data from January to June, our total campaign months would be 6.

I am new to Python so its been difficult to pull together a solution, especially since I need to count a string array (months) and provide and int return (6 - with the above example).

Below is my model class, models.py

class Email(models.Model):
    date = models.DateField()
    time = models.TimeField()
    month = models.CharField(max_length=255, null=True)
    subject = models.CharField(max_length=255)
    day_of_week = models.CharField(max_length=255)
    subject_type = models.CharField(max_length=255)
    content_type = models.CharField(max_length=255)
    email_list = models.CharField(max_length=255)
    recipients = models.PositiveIntegerField()
    unsubscribes = models.PositiveIntegerField()
    bounces = models.PositiveIntegerField()
    open = models.PositiveIntegerField()

So, what I am trying to accomplish is if looking at the above models - what is the month count if the sum of the recipients is not equal to 0, so for every month the recipient is not equal to 0, that month would add 1 to the month count.

Here is a snippet of my views.py file to show how we get the current count for the recipients on a monthly basis.

views.py

def get_context_data(self, **kwargs):
        context = super(EmailListView, self).get_context_data(**kwargs)
        days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
        months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October',
                  'November', 'December']
        subject_type = ['Offer', 'Sell', 'Fear', 'Learn']
        content_type = ['Offer', 'Sell', 'Fear', 'Learn']
        email_list = ['FMGB', 'FMGR', 'AE', 'IBA']

        total_campaigns = {}
        total_recipients = {}
        total_unsubscribes = {}
        total_bounces = {}
        total_open = {}
        total_clicks = {}
        total_campaigns_content = {}
        total_recipients_content = {}
        total_unsubscribes_content = {}
        total_bounces_content = {}
        total_open_content = {}
        total_clicks_content = {}
        total_campaigns_subject = {}
        total_recipients_subject = {}
        total_unsubscribes_subject = {}
        total_bounces_subject = {}
        total_open_subject = {}
        total_clicks_subject = {}

for month in months:
            # total count
            total_campaigns[month] = Email.objects.filter(month=month).count()
            # recipients
            total_recipients[month] = Email.objects.filter(month=month).aggregate(
                Sum('recipients')).get('recipients__sum', 0.00)
            # unsubscribes
            total_unsubscribes[month] = Email.objects.filter(month=month).aggregate(
                Sum('unsubscribes')).get('unsubscribes__sum', 0.00)
            # bounces
            total_bounces[month] = Email.objects.filter(month=month).aggregate(Sum('bounces')).get(
                'bounces__sum', 0.00)
            # opens
            total_open[month] = Email.objects.filter(month=month).aggregate(
                Sum('open')).get('open__sum', 0.00)
            # clicks
            total_clicks[month] = Email.objects.filter(month=month).aggregate(
                Sum('clicks')).get('clicks__sum', 0.00)
             ...

Thank you for your time.

Upvotes: 0

Views: 68

Answers (2)

thecodesmith_
thecodesmith_

Reputation: 1312

This query should get the job done:

count = Email.objects.filter(recipients__gt=0).values('month').distinct().count()

Upvotes: 2

Fabio
Fabio

Reputation: 3067

If I understood correctly what you intend to do and how your model works, you can do something like this:

count = 0
for month in months:
    temp = Email.objects.filter(month=month, recipients != 0)
    if temp:
        count = count + 1

temp is None if no objects match the filter, so if temp only runs if there is an email with at least 1 recipient, and I'm guessing you can't have negative recipients, so if one email has recipients > 0 then the sum for recipients in that month is not zero.

Upvotes: 0

Related Questions