Django: complex order by query with nested models

Question

I have following 3 models

from django.db.models import Max
from django.utils import timezone


class Product(models.Model):

    name = models.CharField(
    blank=False,
    max_length=256
    )


class TaskGroup(models.Model):
    name = models.CharField(
    blank=False,
    max_length=256
    )

    product = models.ForeignKey(
    Product,
    on_delete=models.CASCADE,
    null=False,
    blank=True
    )


class Task(models.Model):
    name = models.CharField(
    blank=False,
    max_length=256
    )

    task_group = models.ForeignKey(
    TaskGroup,
    on_delete=models.CASCADE,
    null=False,
    blank=True
    )
    
    execute_at = models.DateField(
    blank=True
    null=True,
    )

I can order the products by Task execute_at date.

Products.objects.annotate(
    last_task=Max('taskgroup__task__execute_at')
).order_by('-last_task')

However, I need to consider only the first date that is greater than today i.e I need something like

Products.objects.annotate(
    last_task=('taskgroup__task__execute_at' >= timezone.now()).first()
).order_by('last_task')

So I need to order products by the closest Task execute_at date i.e closest to current date.

How can I do this? It would be nice to do it in a single query.

Answer 1

Since DJANGO 2.0 we can use the filter argument to achieve this.

The below line must be do what you want:

from django.db.models import Min, Q

Products.objects.annotate(
    last_task=Min(
        'taskgroup__task__execute_at',
        filter=Q(taskgroup__task__execute_at__gte=timezone.now()
))).order_by('last_task')