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 This Far

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

However, my implementation is not working. Here’s what I currently have in views.py:

        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')),
                output_field=DateField(),
            )
        ).order_by(
            'date_to_display'
        )

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

However, my implementation is not quite working. This is the Traceback reporting a NameError:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/generic/base.py", line 84, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/contrib/auth/mixins.py", line 73, in dispatch
    return super().dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/generic/base.py", line 119, in dispatch
    return handler(request, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/views/generic/list.py", line 174, in get
    context = self.get_context_data()
  File "/app/web/library/views.py", line 1280, in get_context_data
    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')),output_field=DateField(),)).order_by('date_to_display')

Exception Type: NameError at /reading_list/
Exception Value: name 'Case' is not defined

I feel like I am close but am currently scratching my head. The example snippets I have seen make use of Case but for some reason I’m getting an error saying I’ve not defined Case. This leads me to believe that maybe I’m approaching this the wrong way.

Answer

You have to import the relevant parts within views.py

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