Django Add field values as per month and year wise and consolidate

Using Django Using the ORM
1

I am trying to consolidate the field values in Django 3.2 and add them and group them month & year wise, but I am unable to get it work, below is my code.

models.py

class Event(models.Model):
     name = models.CharField(max_length=255, null=True)
     MONTH = (
        ('january', 'january'),
        ('february', 'february'),
        ('march', 'march'),
        ('april', 'april'),
        ('may', 'may'),
        ('june', 'june'),
        ('july', 'july'),
        ('august', 'august'),
        ('september', 'september'),
        ('october', 'october'),
        ('november', 'november'),
        ('december', 'december'),
    )
    YEAR = (
        ('2023', '2023'),
        ('2024', '2024'),
        ('2025', '2025'),
        ('2026', '2026'),
        ('2027', '2027'),
        ('2028', '2028'),
        ('2029', '2029'),
        ('2030', '2030'),
    )
    month = models.CharField(max_length=255, null=True, choices=MONTH)
    year = models.CharField(max_length=255, null=True, choices=YEAR)    
    event_price = models.IntegerField(null=True)

    def __str__(self):
        return self.name

views.py

from django.db.models import Sum

def viewEvent(request):
    event = Event.objects.all()
    total_event_price = Event.objects.values(sum('event_price))
    context = {'event': event, 'total_event_price: total'}
    return render(request, 'view_event.html', context)

view_event.html

 <div class="card card-body">
    <table class="table table-sm">
<tr>
  <th>Year</th>
  <th>January</th>
  <th>February</th>
  <th>March</th>
  <th>April</th>
    <th>May</th>
    <th>June</th>
    <th>July</th>
    <th>August</th>
    <th>September</th>
    <th>October</th>
    <th>November</th>
    <th>December</th>
    <th>Total</th>

</tr>
{% for items in event %}
<tr>
    <td>{{ items.year }}</td>
  <td>{{ items.month }}</td>
  <td>{{ items.total }}</td>

</tr>

I want the entries added as per month wise and another total which adds all month wise as per the year, which is total field. how do I achieve ?

2

If you want to get the sum of these groups (by month, by year, and by month & year), you need to have Django create a “group by” query. This is done using the order_by and values clauses in a query.

Your query to get the groupings by year would look something like this:
Event.objects.order_by('year').values('year').annotate(total_price=Sum('event_price'))

(You can then do the other combinations in a similar manner.)

3

Thank you very much @KenWhitesell for replying. You have done a great help to me.

I want to make this dynamic using the filters and form. So, far I am able to achieve this, but I need further help.

I am able to consolidate data in the column and get a total out of it in “manual_total” [when I pre-define the date field]. But when I filter using
the Django Filter Date range field using Form and filters, I am not getting total as per the columns being fetched. How do I achieve it?

When I filter as per date in the form, the total should be as per the records showing post filter. For example, I have 2 records from March 1st to March 31st and 1 record for April 1st. If I filter March 1st to 31st, it shows only total of 2 records, but total it shows including the 3rd record.

models.py

class Event(models.Model):
    name = models.CharField(max_length=255, null=True)
    event_date = models.DateField(default=None, null=True)
    event_price = models.IntegerField(null=True)

    def __str__(self):
        return self.name

views.py

from django.db.models import Sum

def viewEvent(request):
    event = Event.objects.all()
    manual_total = Event.objects.filter(event_date__range=["2024-03-01", "2024-03-30"]).aggregate(TOTAL=Sum('event_price'))['TOTAL']
	eventFilter = EventFilter(request.GET, queryset=event)
	event = eventFilter.qs
    total = Event.objects.aggregate(TOTAL=Sum('event_price'))['TOTAL']
    context = {'event': event, 'manual_total: manual_total', 'eventFilter': eventFilter}
    return render(request, 'view_event.html', context)

forms.py

class EventForm(forms.ModelForm):
    class Meta:
        model = Event
        fields = '__all__'
        widgets = {
            'event_date': DateInput(),

filters.py

class EventFilter(django_filters.FilterSet):
    start_date = DateFilter(field_name="event_date", lookup_expr='gte', widget=DateInput(attrs={'type': 'date'}))
    end_date = DateFilter(field_name="event_date", lookup_expr='lte', widget=DateInput(attrs={'type': 'date'}))
    total = Event.objects.aggregate(TOTAL=Sum('event_price'))['TOTAL']

    class Meta:
        model = Event
        fields = 'event_date', 'event_price'

view_event.html


{% extends 'base.html' %}
{% load static %}
{% block content %}

<br>

<div style="width:1400px; margin:0 auto;" class="row">
    <div class="col-md-8">
        <div class="card card-body">
            <h5>&nbsp; Event Report :</h5>
            <div class="row">
    <div class="col">
        <div class="card card-body">
            <form method="get">
                Event Date (Start): {{ eventFilter.form.start_date }}
                Event Date (End):{{ eventFilter.form.end_date }}

                <button class="btn btn-primary" type="submit">Filter</button>
            </form>
        </div>
    </div>
</div>
           <div class="card card-body">
      <table class="table table-sm">
        <tr>
          <th>Event Name</th>
          <th>Event Date</th>
          <th>Event Price</th>
        </tr>
        {% for items in event %}
        <tr>
          <td>{{ items.name }}</td>
          <td>{{ items.event_date }}</td>
            <td>{{ items.event_price }}</td>
          {% endfor %}
        </tr>
      </table>
              <table class="table table-sm">
        <tr>
          <th></th>
            <th></th>
            <th></th>
            <th></th>
          <th>TOTAL: {{ total }}</th>

        </tr>
              </table>
    </div>

        </div>

    </div>

</div>


{% endblock %}
4

In this particular case, you have:

Which means you’re calculating your TOTAL on everything in Event, and not on the filtered queryset.

In general, you don’t want to do the aggregation within the filter.

Allow the filter to return the queryset, then perform your annotations or aggregations in the view.

5

Yes, you are absolutely right @KenWhitesell.

But how do I make it in the view ?

I tried the below code, but it didnt work:

total = Event.objects.aggregate(TOTAL=Sum('event_price'))['TOTAL']
eventFilter = EventFilter(request.GET, queryset=total)

I also tried:

total = Event.objects.filter(EventFilter=('start_date', 'end_date').annotate(TOTAL=Sum('event_price')))

Both did not work. Can you pls help me ?

6

I’m sorry, I don’t use the Django_filters package - there’s not a lot of assistance I can provide with it.

7

Thanks @KenWhitesell.

If not Django_filters, what other ways can I make it work. Is my queryset in the view correct ?

How do I join the queryset and aggregate join, I need help in syntax?

8

This is not going to work because EventFilter is not a field in Event.

If you forget about django_filters for the moment, how would you write a filter for this queryset?