Django 4.x – Conditional order_by of a QuerySet

The Objective

The objective is to conditionally order a QuerySet by one of three different date fields in the view based on another field in the model. Since conditional ordering cannot be accomplished with Class Meta I am exploring accomplishing this objective in the view.

Here is the relevant excerpt from models.py:

READING_PROGRESS = [
    ('---', '---'),
    ('1) On Reading List', '1) On Reading List'),
    ('2) Reading In Progress', '2) Reading In Progress'),
    ('3) Completed Reading', '3) Completed Reading'),
]

class ReadingProgress(models.Model):
    record = models.ForeignKey(
        LibraryRecord,
        related_name='record_in_reading_progress',
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        verbose_name='Library record'
    )
    user = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        null=True,
        blank=True
    )
    reading_progress = models.CharField(
        max_length=30,
        choices=READING_PROGRESS,
        default='---'
    )
    date_added = models.DateField(
        auto_now=False,
        auto_now_add=False,
        null=True,
        blank=True,
    )
    date_started = models.DateField(
        auto_now=False,
        auto_now_add=False,
        null=True,
        blank=True,
    )
    date_completed = models.DateField(
        auto_now=False,
        auto_now_add=False,
        null=True,
        blank=True,
    )

    class Meta:
        ordering = [
            'reading_progress',
        ]
        verbose_name_plural = 'Reading Progress'

        unique_together = ('record', 'user',)

    # Record metadata
    date_created = models.DateTimeField(
        auto_now_add=True
    )

    def __str__(self):
        return f'{self.record.title} - {self.reading_progress}'

The relevant fields in the model are:

• reading_progress
• date_added
• date_started
• date_completed

Each date field corresponds to a status value. I want to be able to order_by the QuerySet in the view by the field reading_progress:

• When reading_progress == ‘1) On Reading List’ then order by date_added
• When reading_progress == ‘2) Reading In Progress’ then order by date_started
• When reading_progress == ‘3) Completed Reading’ then order by date_completed

Research Before The Answer

I did some research and found a useful looking QuerySet API called, annotate(). This looks to be way to go (Django docs).

The Django docs appeared to suggest that:

• I could filter
• and then annotate on the filtered QuerySet
• With additional research I concluded that I could use F() as a way of implementing the query within the annotate() API

The Answer

It turns out that when using Case be sure to follow the documentation and import what is needed. Thanks to the provider of the answer marked as correct! 🙂

In addition, I removed output_field as there was only one possible field data type (DateTime) and this did not need to be explicitly referred to.

from django.db.models import Case, F, Q, Value, When

        reading_progress = ReadingProgress.objects.filter(user__username=self.request.user)\
            .annotate(
            date_to_display=Case(
                When(reading_progress='1) On Reading List', then=F('date_added')),
                When(reading_progress='2) Reading In Progress', then=F('date_started')),
                When(reading_progress='3) Completed Reading', then=F('date_completed')),
            )
        )

>Solution :

You have to import the relevant parts within views.py

from django.db.models import Case, F, Q, Value, When