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.

NKSM · Accepted Answer · 2021-04-23 14:49:26Z

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')

edited Apr 23, 2021 at 14:49

answered Apr 23, 2021 at 13:06

NKSM

5,8944 gold badges31 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

StaticName Over a year ago

I do not need to filter products, I need to order products by task execute_at date where execute_at date is >= timezone.now()

NKSM Over a year ago

@StaticName, to do that first you have to do filter execute_at__gte=timezone.now(). Then you can do order_by("taskgroup__task__execute_at") for ascending or order_by("taskgroup__task__execute_at") for descending.

NKSM Over a year ago

@zelenyjan, i think that he need first item, from the question: I need to consider only the first date that is greater than today

NKSM Over a year ago

@StaticName, if you don't need to filter simple exclude it and use only ....order_by("-taskgroup__task__execute_at").first(). But to get items greater or equal to timezone.now() you have to do filter().

StaticName Over a year ago

Even you do the filter, it does not work. Because at the end products are ordered by taskgroup__task__execute_at, so filter has no effect in this query.

|

Collectives™ on Stack Overflow

Django: complex order by query with nested models

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related