Django ORM filter by group with where clause-CodePudding

I have a model of students and opinions. Each student can change their opinion over time. What I ultimately want to do is create a plot showing the number of students with each opinion on a given day, but as a first step I want to count the number of students with each opinion on a given day.

My model is as follows (abbreviated for brevity):

class Student(models.Model):
    first_name = models.CharField(max_length=30, null=True, blank=True)
    surname = models.CharField(max_length=30, null=True, blank=True)


class Opinion(models.Model):
    student = models.ForeignKey('Student', on_delete=models.CASCADE,null=True)

    opdate = models.DateField(null=True, blank=True)
    sentiment_choice = [
    ('Positive', 'Positive'),
    ('Negative', 'Negative'),
    ]   
    sentiment = models.CharField(
        max_length=40,
        choices=sentiment_choice,
        default="Positive",
        null=True, blank=True
    )

My approach is to loop over all the dates in a range, filter the opinion table to get all the data upto that date, find the latest opinion per student, count these and load the results into an array.

I know how to filter the opinion table as follows (where start_date is my iterator):

Opinion.objects.filter(opdate__lte=start_date)

I also know how to pickup the latest opinion for each student:

 Opinion.objects.values('student').annotate(latest_date=Max('opdate'))

How would I combine this so that I can get the latest opinion for each student that is prior to my iterator?

I'm working on Django 3.2.12 with an SQL Lite DB

CodePudding user response：

You can use a Subquery expression ^[Django-doc] with:

from django.db.models import OuterRef, Subquery

Student.objects.annotate(
    last_sentiment=Subquery(
        Opinion.objects.filter(
            student_id=OuterRef('pk')
        ).order_by('-opdate').values('sentiment')[:1]
    )
)

The Students will have an extra attribute .last_sentiment that will contain the sentiment of the last related Opinion record, or NULL/None if there is no related Opinion.