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

Reputation: 3289

Filtering Objects in Database by Keyword

I am using Django 1.8.3 and Python 3.4.3

I will try to explain this so it makes sense, so please bear with me. I've imported a csv file into my database with roughly 50 rows and 10 columns of data, which consists of data pulled from Google Analytics and our email marketing campaign data. One of the columns is 'day_of_week'. I need to get a count of the database rows that have the keyword of the day I need...the only way I can figure out how to do it is with the code below, but man, it sure seems it could be more dynamic and cleaner.

Is there a way to filter it in a way I can use a tag in the template, or something cleaner than this? Thank you for your help.

class EmailListView(ListView):
    model = Email
    template_name = 'dashboard/pages/email.html'

    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 = ['FMG - Business', 'FMG - Residential', 'AE', 'IBA']

        total_campaigns_monday = {}
        total_campaigns_tuesday = {},
        total_campaigns_wednesday = {},
        total_campaigns_thursday = {},
        total_campaigns_friday = {},
        total_campaigns_saturday = {},
        total_campaigns_sunday = {},
        total_recipients = {}
        total_unsubscribes = {}
        total_bounces = {}
        total_open = {}
        total_clicks = {}

        for campaigns in days:
            total_campaigns_monday = Email.objects.filter(day_of_week='Monday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Tuesday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Wednesday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Thursday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Friday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Saturday').count()
            total_campaigns_monday = Email.objects.filter(day_of_week='Sunday').count()

And a snippet of my template ( notice the first is different than the others.)

{% if email_list %}
<tr>
   <td>Monday</td>
   <td>{{ total_campaigns_monday }}</td>
   <td>{{ total_recipients.Monday }}</td>
   <td>{{ total_unsubscribes.Monday  }}</td>
   <td>{{ total_bounces.Monday  }}</td>
   <td>{{ total_open.Monday  }}</td>
   <td>{{ total_clicks.Monday  }}</td>
   <td>{% average total_open.Monday total_recipients.Monday %}</td>
   <td>{% average total_clicks.Monday total_open.Monday %}</td>
   </tr>
{% endif %}

Upvotes: 1

Views: 100

Answers (3)

myersjustinc
myersjustinc

Reputation: 714

You can do this in one query with aggregation methods:

from django.db.models import Count
days_and_counts = Email.objects.values('day_of_week').annotate(count=Count('day_of_week'), distinct=True)

will give you something like this in days_and_counts (output from my randomly populated data set):

[
    {'day_of_week': 'Friday', 'count': 7},
    {'day_of_week': 'Monday', 'count': 5},
    {'day_of_week': 'Saturday', 'count': 2},
    {'day_of_week': 'Sunday', 'count': 12},
    {'day_of_week': 'Thursday', 'count': 11},
    {'day_of_week': 'Tuesday', 'count': 6},
    {'day_of_week': 'Wednesday', 'count': 7}
]

From there, you can build a dict like f43d65 suggested:

total_campaigns = {x['day_of_week']: x['count'] for x in days_and_counts}

which will give you something like this in total_campaigns:

{
    'Wednesday': 7,
    'Thursday': 11,
    'Tuesday': 6,
    'Monday': 5,
    'Saturday': 2,
    'Sunday': 12,
    'Friday': 7
}

Upvotes: 1

Daniel Petrikin
Daniel Petrikin

Reputation: 198

Yes you can be cleaner:

        days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

        total_campaigns = {
            day:Email.objects.filter(day_of_week=day).count()
            for day in days
        }

I recommend checking out dictionary comprehensions

Upvotes: 1

f43d65
f43d65

Reputation: 2302

I hope I understood your queston.

Replace total_campaigns_<day_of_week> statements and for loop with

total_campaigns = {}
for day in days:
    total_campaigns[day] = Email.objects.filter(day_of_week=day).count()

In templates you can use {{ total_campaigns.Monday }} or {{ total_campaigns.Sunday }} or something similar.

Upvotes: 2

Related Questions