CODEWITHSUNDEEP
djangoforeign-keyssql-order-bydjango-queryset
StaticName
StaticName

Reputation: 295

Django: order by multi-level reverse look up

Django: order by multi-level reverse look up

I have following 3 models

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 want to get all the Products ordered by the execute_at date of Task. How can I do this? Is it possible to do this using a single query?

There can be multiple tasks for a product. But always consider the smaller execute_at date i.e the date that is closest to current date.

Upvotes: 1

Views: 141

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476534

You can .annotate(…) [Django-doc] by the maximum of the created_at field, and then order with:

from django.db.models import Max

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

You can order items with no related Task at the end with:

from django.db.models import F, Max

Products.objects.annotate(
    last_task=Max('taskgroup__task__execute_at')
).order_by(F('last_task').desc(nulls_last=True))

Upvotes: 3

Related Questions