Annotate results from related model method onto model Queryset?-CodePudding

I'm trying to figure out the best / most efficient way to get the 'progress' of a Summary object. A Summary object has X Grade objects - a Grade object is_complete when it has a Level chosen and has 1 or more related Evidence objects.

I am trying to tie that Summary 'progress' to a Person.

The models.py look like this:

class Summary(models.Model):
    id = models.BigAutoField(primary_key=True)
    person = models.ForeignKey(
        Person, on_delete=models.PROTECT, related_name="summaries"
    )
    finalized = models.BooleanField(default=False)
 
    class Meta:
        verbose_name = "Summary"
        verbose_name_plural = "Summaries"
 
    def progress(self):
        """Return the progress of the summary."""
        grades = self.grades.all()
        finished_grades = (
            Grade.complete.all().filter(summary=self).count()
        )
        try:
            progress = (finished_grades / grades.count()) * 100
 
class Grade(models.Model):
    id = models.BigAutoField(primary_key=True)
    summary = models.ForeignKey(
        Summary, on_delete=models.PROTECT, related_name="%(class)ss"
    )
    level = models.ForeignKey(
        Level,
        on_delete=models.PROTECT,
        null=True,
        blank=True,
        related_name="%(class)ss",
    )
 
    class Meta:
        verbose_name = "Grade"
        verbose_name_plural = "Grades"
 
    @property
    def is_complete(self):
        if 0 < self.evidences.count() and self.level:
            return True
        return False
 
class Evidence(models.Model):
    id = models.BigAutoField(primary_key=True)
    grade = models.ForeignKey(
        Grade, on_delete=models.PROTECT, related_name="%(class)ss"
    )
    comment = models.TextField()

My views.py looks like this:

class PersonListView(ListView):
    model = Person
    template_name = "app/person_list.html"
    context_object_name = "person_list"
 
    def get_queryset(self):
        people = Person.objects.all().prefetch_related("summaries", "summaries__grades", "summaries__grades__evidences")
        # There should only be one non-finalized summary
        # or there will be None
        first_summary = Summary.objects.filter(
            person__id=OuterRef("id"), finalized=False
        )
        return people.annotate(
            summary_progress=Subquery(first_summary[:1].progress()),
        )

I'm trying to do this in as few queries as possible (I think with prefetch maybe 3-4 queries would be possible in total?)

In my template I'm trying to make it simple to get that so I can do something simple as I'm looping through the list of people:

<div >
    {{ student.summary_progress }}
</div>

The code above doesn't work because I'm trying to annotate that .progress() method onto the People queryset. I can't seem to figure out the best way to accomplish it.

CodePudding user response：

Would something like that work for you ?

Summary.objects.alias(total_grades=Count("grade"), finished_grades=Count("grade", filter=Q(grade__evidences__isnull=False,level__isnull=False))).annotate(progress=F("finished_grades") / F("total_grades")).

The alias method allows for calculated values (that won't be returned).
The Count object will return the number of related entries. It can use Q filters.
The annotate method allows for calculated values that will be returned.
The F object allows you to read colums (even calculated ones) for filtering or annotation

CodePudding user response：

I came up with a similar solution to (Alombaros's answer), but I believe I've fleshed it out a bit more, and I've tested that it works. Using Django Debug Toolbar, I can see that this data can be fetched with exactly one query.

from django.db.models import (
    Count,
    ExpressionWrapper,
    F,
    FloatField,
    OuterRef,
    Q,
    Subquery,
    Value,
)

class PersonListView(ListView):
    model = Person
    template_name = "app/person_list.html"
    context_object_name = "person_list"

    def get_queryset(self):
        qs = super().get_queryset()

        # Build up the queryset for the subquery
        summaries_subquery_qs = Summary.objects.filter(person_id=OuterRef("id"), finalized=False)
        summaries_subquery_qs = summaries_subquery_qs.alias(
            total_grades=Count("grades"),
            finished_grades=Count("grades", filter=Q(
                grades__evidences__isnull=False, grades__level__isnull=False
            ))).annotate(
            # ExpressionWrapper allows you to output using a FloatField.
            summary_progress=ExpressionWrapper(
                # Multiplying by 100 in order to get a percentage value between 0-100
                (F("finished_grades") * Value(100) / F("total_grades")),
                output_field=FloatField())
        )

        qs = qs.annotate(
            # Now you can annotate that subquery, and "pluck" out the value you want
            summary_progress=Subquery(summaries_subquery_qs.values('summary_progress')[:1])
        )

        return qs

And here's the SQL that is generated by that query:

SELECT "myapp_person"."id",
       "myapp_person"."name",
       (SELECT ((COUNT(U2."id") FILTER (WHERE (U3."id" IS NOT NULL AND U2."level_id" IS NOT NULL)) * 100) /
                COUNT(U2."id")) AS "finished_percentage"
        FROM "myapp_summary" U0
                 LEFT OUTER JOIN "myapp_grade" U2
                                 ON (U0."id" = U2."summary_id")
                 LEFT OUTER JOIN "myapp_evidence" U3
                                 ON (U2."id" = U3."grade_id")
        WHERE (NOT U0."finalized" AND U0."person_id" = "myapp_person"."id")
        GROUP BY U0."id"
        LIMIT 1) AS "finished_percentage"
FROM "myapp_person"