Django Annotate With Sum And Min/Max At The Same Time In Search Form

Question

I have a page of courses which are search results of a course search form. In the current code below, it currently allows a user to type in the min and max views and return courses that contain that Course "views" criteria.

It incorrectly does this with the Course "views" field/column when it should be doing it on the Lesson "views" field/column

I want to annotate (or subquery?) the sum of all lesson views but make sure a user is able to find all courses with lesson views that fall under their min/max search critera? In other words, to have the user be able to filter and search via the search form by Courses with Lesson views between X min and Y max views and return all the courses that have that criteria. Or just search by courses with X min views only or Y max views only.

Valid annotate that gets the total sum of all lesson views in a course:

Course.objects.annotate(foobar=Sum("lesson__views")).order_by("foobar")

Tried doing:

if min_views_query:
     qs = Course.objects.annotate(foobar=Sum(Min("lesson__views"))).order_by("foobar")
if max_views_query:
     qs = Course.objects.annotate(foobar=Sum(Max("lesson__views"))).order_by("foobar")

Error: django.core.exceptions.FieldError: Cannot compute Min('Sum'): 'Sum' is an aggregate

Code:

Courses Forms.py:

class CourseForm(forms.Form):
    min_views = forms.IntegerField(widget=forms.NumberInput(attrs={'class':'form-control', 'autocomplete':'off','id':'min_views', 'type':'number', 'min':'0', 'placeholder': '0'}), required=False, validators=[MinValueValidator(0), MaxValueValidator(99999999999999999999999999999999999)])
    max_views = forms.IntegerField(widget=forms.NumberInput(attrs={'class':'form-control', 'autocomplete':'off', 'id':'max_views', 'type':'number', 'min':'0', 'placeholder': '1000000'}), required=False, validators=[MinValueValidator(0), MaxValueValidator(99999999999999999999999999999999999)])

    def __init__(self, *args, **kwargs):
        super(CourseForm, self).__init__(*args, **kwargs)
        self.fields['min_views'].label = "Min Views:"
        self.fields['max_views'].label = "Max Views:"


Courses Views.py:

class CourseListView(ListView):
    model = Course
    def get_queryset(self):
        qs = super().get_queryset()
        self.form = form = CourseForm(self.request.GET)
        if form.is_valid():
                min_views_query = self.request.GET.get('min_views')
                max_views_query = self.request.GET.get('max_views')
                if min_views_query:
                    qs = qs.filter(views__gte=min_views_query)
                if max_views_query:
                    qs = qs.filter(views__lte=max_views_query)
                return qs


Courses Models.py:

class Course(models.Model):
    views = models.PositiveIntegerField(default=0)

    @property
    def total_lesson_views(self):
        lessons_dictionary = Lesson.objects.filter(course=self).aggregate(Sum('views'))
        return lessons_dictionary['views__sum']

class Lesson(models.Model):
    course = models.ForeignKey(Course, on_delete=models.SET_NULL, null=True)
    views = models.PositiveIntegerField(default=0)

xbello · Accepted Answer · 2020-06-01T13:43:04.313

1

If I understand correctly, you need this:

qs = Course.objects.annotate(Min("lesson__views"), Max("lesson_views")).\
    aggregate(Sum("lesson__views__min"), Sum("lesson__views__max"))

That queryset should include two fields: lesson__views__min__sum and lesson__views__max__sum that you can use to filter.

If you already have views in your Lesson model, you can filter the Lessons from the Course model:

qs = Course.objects.filter(lesson__views__lt=top_limit, 
                           lesson__views__gt=bottom__limit).\
                          distinct()

This selects Lesson by their individual number of views, i.e. answers "Give me all Courses which includes at least one Lesson with its views between X and Y".

If you need to aggregate all Lesson views and then filter:

qs = Course.objects.annotate(Sum("lesson__views")).filter(
    lesson__views__sum__lt=top_limit,
    lesson__views__sum__gt=bottom_limit)

This answers the question "Give me all Courses which the Sum of all the views of its Lessons combined are between X and Y".

edited Jun 01 '20 at 13:43

answered Jun 01 '20 at 08:34

xbello

7,223
3
28
41

Thanks for the reply. That returns exactly what you say it does. {'lesson__views__min__sum': #, 'lesson__views__max__sum': #} which is useful to have and could help someone else. But what I really need is to have the user be able to filter and search via the search form by Courses with Lesson views between X min and Y max views and return all the courses that have that criteria. Sorry for not being as clear. I will re-word the question to include this. – atavisti Jun 01 '20 at 11:58
or maybe I could just ask a different question on stackoverflow for that and reward you for solving this one since it does technically do what the original question wanted. What do you think? – atavisti Jun 01 '20 at 12:08
Ok cool sounds good! I think it would be good to still leave what you had before but add onto it since that query could definitely help other people who want to do something similar. – atavisti Jun 01 '20 at 12:14

Django Annotate With Sum And Min/Max At The Same Time In Search Form

1 Answers1