About Subqueries and Filter Objects which Related Objects Fall under a Certain Condition

from django.utils.timezone import now as tz_now
from django.db import models
from django.db.models.functions import Coalesce
from .models import Ad, AdSchedule

now = tz_now()

scheduled_ad_subquery = models.Subquery(
    AdSchedule.objects.filter(
        ad=models.OuterRef("pk"),
        start_date__lte=now,
        end_date__gte=now,
    ).values('id')
)

active_ads = Ad.objects.filter(is_active=True).annotate(
    is_scheduled=Coalesce(
        scheduled_ad_subquery,
        False,
    )
).filter(is_scheduled=True)

Tips and Tricks Programming Development Databases Django 5.x Django 4.2 Django 3.2 PostgreSQL

• Share
  on X
• Share
  on Facebook
• Share
  on LinkedIn