I am trying to sum up two columns in a view with values() and annotate().

Currently calling "total" will return the total for each row and not the overall total.

Returning in template

5
3

instead of

8

I believe this is because I print the total in a for loop. What confuses me is that I have an almost similar code working perfectly fine in another view.

How can I get the total of the several rows together?

Update to answer Willem's question - timestamp is used to order the list of model objects when they are created.

This was not the result I initially wanted when I wrote the code. But realised I could use this view to render a report of the objects as they are being created, so I added the timestamp to order the objects starting with the most recent one.

This is not relevant for my problem. I removed it to avoid confusion. Sorry for this.

views

def function(request, userprofile_id):
venue = UserProfile.objects.filter(user=request.user).values('venue')
points_cummulated_per_user_per_venue = Itemised_Loyalty_Card.objects.filter(user=userprofile_id).filter(venue=request.user.userprofile.venue).values('venue__name','timestamp').annotate(sum_points=Sum('add_points')).annotate(less_points=Sum('use_points')).annotate(total=F('add_points')-F('use_points')).
return render(request,"main/account/venue_loyalty_card.html",{'venue':venue,'points_cummulated_per_user_per_venue':points_cummulated_per_user_per_venue})

template

{%for model in points_cummulated_per_user_per_venue %}
Total: {{model.total}}
{%endfor%}

models

class Itemised_Loyalty_Card(models.Model):
    user = models.ForeignKey(UserProfile, blank=True, null=True, on_delete=models.CASCADE)
    venue = models.ForeignKey(Venue, blank=True, null=True, on_delete=models.CASCADE)
    add_points = models.IntegerField(name = 'add_points', null = True, blank=True, default=0)
    use_points = models.IntegerField(name= 'use_points', null = True, blank=True, default=0)

class Venue(models.Model, HitCountMixin):
    id = models.AutoField(primary_key=True)
    name = models.CharField(verbose_name="Name",max_length=100, blank=True)

CodePudding user response：

Please don't use .values(…). I wrote a short article that describes several problems with this ^{[Django-antipatterns]}.

from django.db.models import F, Sum


def function(request, userprofile_id):
    profile = request.user.profile
    venue = profile.venue
    venues = Venue.objects.filter(itemised_loyalty_card__user=profile).annotate(
        total=Sum(
            F('itemised_loyalty_card__add_points')
            - F('itemised_loyalty_card__use_points')
        )
    )

    return render(
        request,
        'main/account/venue_loyalty_card.html',
        {
            'venue': venue,
            'venues': venues,
        },
    )

then in your template you enumerate over the venues and render the total:

{%for item in venues %}
    {{ item.name }}: {{ item.total }}
{% endfor %}

Note: Models in Django are written in PascalCase, not snake_case, so you might want to rename the model from Itemised_Loyalty_Card to ItemisedLoyaltyCard.