Django – I need the total sum displayed after a filtered search

Issue

I have column in a table that has decimal values and linked to that table is a filter which is searching by date and site code. I want the total sum of the column to be displayed after I perform the search.

I have an aggregation setup on that column, which is working but it is not dynamic and when I perform a search it just keeps the value of the total to be the unfiltered result.
The desired response would be for the total to change with each search result.

Models.py

class expense(models.Model):
    ExcludeTax = models.DecimalField('Exclude Tax', max_length=20, blank=True, null=True, max_digits=10, decimal_places=2)
    IncludeTax = models.DecimalField('Include Tax', max_length=20, blank=True, null=True, max_digits=10, decimal_places=2)
    user = models.ManyToManyField(dbmobile, blank=True)
    Date = models.DateField(default=now)

Views.py

def expense_table(request):
    item = expense.objects.all().order_by('IncludeTax')
    add = expense.objects.all().aggregate(Sum('IncludeTax'))['IncludeTax__sum']
    search_list = expense.objects.all().order_by('user__Site_Code', 'Date')
    search_filter = expenseFilter(request.GET, queryset=search_list)
    return render(request, 'expense/MExpense.html', {'item': item, 'filter': search_filter, 'add': add})

filters.py

class expenseFilter(django_filters.FilterSet):
    Date = django_filters.DateFromToRangeFilter(widget=RangeWidget(attrs={'type': 'date'}))

    class Meta:
        model = expense
        fields = ['Date', 'user__Site_Code']

html

    <tfoot>
    <tr>
    <td><b>Total</b></td>
    <td><b>{{add}}</b></td>
    </tfoot>

Solution

Your total is calculated by the following line:

add = expense.objects.all().aggregate(Sum('IncludeTax'))['IncludeTax__sum']

It can be seen that you are not filtering the above queryset, hence the aggregate is performed on all the rows. To get the filtered queryset one can simply access it from the .qs property of the filterset instance, hence you can change your view as follows:

def expense_table(request):
    item = expense.objects.all().order_by('IncludeTax')
    search_list = expense.objects.all().order_by('user__Site_Code', 'Date')
    search_filter = expenseFilter(request.GET, queryset=search_list)
    add = search_filter.qs.aggregate(Sum('IncludeTax'))['IncludeTax__sum']
    return render(request, 'expense/MExpense.html', {'item': item, 'filter': search_filter, 'add': add})

Answered By – Abdul Aziz Barkat

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published