only get newest item of a type from queryset

Question

Two models:

class University(models.Model):
    name = models.CharField("name", max_length = 48)
    city = models.ForeignKey(City, on_delete = models.CASCADE) 

class Course(models.Model):
    name = models.CharField("course name", max_length = 48)
    university = models.ForeignKey(University, on_delete = models.CASCADE)
    students = models.ManyToManyField(Student, related_name = "%(class)s_name", related_query_name = "student_course_qs", blank = True)
    online = models.BooleanField("online course", default = False)
    semester = models.PositiveIntegerField("choose 1-6", default = 1, choices = [(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6)])
    time_update = models.DateTimeField("last info update", auto_now = True)

Restriction: If a Course has students, it cannot be online and vice versa.

How would I obtain a list of all Courses on all Universities of a given city where the latest entry on Course has students and the Course is held in the 3rd semester?

I tried it with a loop over the different groups of University but this results in a massive amount of queries:

universities = University.objects.filter(city = City.objects.first())

wanted = []
for univ in universities:
    c = Course.objects.filter(university = univ).order_by("time_update").last()
    if c.semester == 3 and not c.online:
        wanted.append(c.id)

selected_courses = Courses.objects.filter(id__in = wanted)
    

The problem I see here is, that I create too many queries and just after the filter for universities they would already be grouped - can I handle those groups one after another without re-making my query?

Answer 1

You can work with a subquery:

from django.db.models import F, OuterRef, SubQuery

universities = (
    University.objects.filter(city=City.objects.first())
    .annotate(
        last_course_id=Subquery(
            Course.objects.filter(university_id=OuterRef('pk'))
            .order_by('-time_update')
            .values('pk')[:1]
        )
    )
    .filter(
        course__id=F('last_course_id'), course__semester=3, course__inline=True
    )
)
selected_courses = Course.objects.filter(
    id__in=[university.last_course_id for university in universities]
)

We here first select the primary key of the last Course for each University, then we can use this in the filtering of a JOIN, and finally we fetch the Courses with one extra query.