CODEWITHSUNDEEP
djangodjango-viewsdjango-orm
Bring Coffee Bring Beer
Bring Coffee Bring Beer

Reputation: 1121

Django query - how to filter sum by date?

I am struggling with a queryset in a Django view. Basically, I have three models: User, ActivityLog, & ActivityCategory.

User is the built-in.

ActivityLog looks like this:

class ActivityLog(models.Model):
    activity_datetime = models.DateTimeField(default=timezone.now)
    user = models.ForeignKey(User, on_delete=models.CASCADE, null=True, related_name='activity_user')
    activity_category = models.ForeignKey(ActivityCategory, on_delete=models.CASCADE, null=True, related_name='activity_cat')
    activity_description = models.CharField(max_length=100, default="Misc Activity")

Activity Category:

class ActivityCategory(models.Model):
    activity_name = models.CharField(max_length=40)
    activity_description = models.CharField(max_length=150)
    pts = models.IntegerField()

My goal is to return an aggregate by user of all the points they have accumulated by participating in activities in the log. Each log references an ActivityType, different types are worth different points.

I accomplished this with the following query in the view:

class UserScoresAPIView(generics.ListAPIView):
    queryset = User.objects.all()
    serializer_class = UserScoresSerializer

    def get_queryset(self):
        queryset = User.objects.annotate(total_pts=Coalesce(Sum('activity_user__activity_category__pts'), 0)).order_by('-total_pts')
        return queryset

So now I need to add to this query and restrict it based on date of the activity. I want to basically add a filter:

.filter('activity_user__activity_datetime__gte=datetime.date(2020,10,1)')

How can I add this into my current query to accomplish this? I tried to do so here:

queryset = User.objects.annotate(total_pts=Coalesce(Sum('activity_user__activity_category__pts').filter('activity_user__activity_datetime__gte=datetime.date(2020,10,1)') , 0)).order_by('-total_pts')

But that would happen after the Sum and wouldn't be helpful (or work...) so I tried to chain it where it is pulling the User objects ​

User.objects.filter('activity_user__activity_datetime__gte=datetime.date(2020,10,1)').annotate(total_pts=Coalesce(Sum('activity_user__activity_category__pts'), 0)).order_by('-total_pts')

But now I am receiving an error when trying to parse my query:

ValueError: too many values to unpack (expected 2)

I am confused at where to go next and appreciate any guidance.

Thank you.

BCBB

Upvotes: 2

Views: 478

Answers (1)

Bring Coffee Bring Beer
Bring Coffee Bring Beer

Reputation: 1121

Aaaaaaand I got so focused on how to chain these together that I thought I was doing it wrong but in reality, I am just not sure what possessed me to enclose the filter in quotes...

Arrrrg. It's working now as listed last without the quotes...

User.objects.filter(activity_user__activity_datetime__gte=datetime.date(2020,10,1)).annotate(total_pts=Coalesce(Sum('activity_user__activity_category__pts'), 0)).order_by('-total_pts')

Upvotes: 1

Related Questions