71 lines
2.5 KiB
Python
71 lines
2.5 KiB
Python
"""Reusable queryset annotations for the Course API.
|
|
|
|
Centralised here so list + detail views always return the same shape, and
|
|
expensive aggregations are computed via correlated subqueries (single SQL
|
|
statement, no Cartesian explosion across multi-relation joins).
|
|
"""
|
|
|
|
from datetime import timedelta
|
|
|
|
from django.db.models import Count, IntegerField, OuterRef, Subquery, Sum
|
|
from django.db.models.functions import Coalesce
|
|
from django.utils import timezone
|
|
|
|
|
|
def _published_lesson_qs():
|
|
from apps.courses.models import Lesson, LessonStatus
|
|
return Lesson.objects.filter(status=LessonStatus.PUBLISHED)
|
|
|
|
|
|
def annotate_course_stats(qs, *, bestseller_window_days: int = 90):
|
|
"""Annotate Course queryset with `lesson_count`, `total_minutes_seconds`,
|
|
`enrolled_count`, and `popularity` (= paid orders in the last
|
|
`bestseller_window_days`). Each is a correlated subquery so they don't
|
|
interact — safe to combine."""
|
|
|
|
from apps.courses.models import Lesson, LessonStatus
|
|
from apps.enrollments.models import Enrollment
|
|
from apps.payments.models import Order, OrderStatus
|
|
|
|
cutoff = timezone.now() - timedelta(days=bestseller_window_days)
|
|
|
|
lesson_count_sq = (
|
|
Lesson.objects.filter(section__course=OuterRef("pk"), status=LessonStatus.PUBLISHED)
|
|
.order_by()
|
|
.values("section__course")
|
|
.annotate(c=Count("id"))
|
|
.values("c")[:1]
|
|
)
|
|
duration_sum_sq = (
|
|
Lesson.objects.filter(section__course=OuterRef("pk"), status=LessonStatus.PUBLISHED)
|
|
.order_by()
|
|
.values("section__course")
|
|
.annotate(s=Coalesce(Sum("duration_seconds"), 0))
|
|
.values("s")[:1]
|
|
)
|
|
enrolled_count_sq = (
|
|
Enrollment.objects.filter(course=OuterRef("pk"))
|
|
.order_by()
|
|
.values("course")
|
|
.annotate(c=Count("id"))
|
|
.values("c")[:1]
|
|
)
|
|
popularity_sq = (
|
|
Order.objects.filter(
|
|
course=OuterRef("pk"),
|
|
status=OrderStatus.PAID,
|
|
created_at__gte=cutoff,
|
|
)
|
|
.order_by()
|
|
.values("course")
|
|
.annotate(c=Count("id"))
|
|
.values("c")[:1]
|
|
)
|
|
|
|
return qs.annotate(
|
|
lesson_count=Coalesce(Subquery(lesson_count_sq, output_field=IntegerField()), 0),
|
|
total_minutes_seconds=Coalesce(Subquery(duration_sum_sq, output_field=IntegerField()), 0),
|
|
enrolled_count=Coalesce(Subquery(enrolled_count_sq, output_field=IntegerField()), 0),
|
|
popularity=Coalesce(Subquery(popularity_sq, output_field=IntegerField()), 0),
|
|
)
|