Is there a way in Django to order a model using a "custom filter logic"?

Question

I'm currently having a problem in a personal project which involves ordering by 2 custom fields (limit_date and priority). Let's suppose a model like:

PRIORITY_CHOICES = [
        ("red", "Red"),
        ("yellow", "Yellow"),
        ("green", "Green")
    ]

class Todo(models.Model):
    limit_date = models.DateField()
    priority = models.CharField(choices=PRIORITY_CHOICES, max_length=15,         
                                blank=False, null=True)

Let's suppose i want to order them following the following logic:

if <current_todo>.priority == 'red':
    priority_scale = 3
elif <current_todo>.priority == 'yellow':
    priority_scale = 2
else:
    priority_scale = 1

priority_scale_with_date = <current_todo>.limit_date - datetime.today() * (<current_todo>.priority_scale)

I know i can do some things with "conditional expressions" to get priorities as values, like:

Todo.objects.annotate(
    priority_scale=Case(
        When(priority='red',
            then=Value(3)),
        When(priority='yellow',
            then=Value(2)),
        When(priority='green',
            then=Value(1)),
        default=Value(0),
        output_field=models.IntegerField())).order_by('-priority_scale', 'limit_date')

But i don't know how can i put them all "together". Let's suppose a object with priority 'red' (value 3), with two days left to be done and another one with priority 'green' (value 1) with one day to be done. Their priorities will be something like: first_obj = 1 * 3 = 3 (days untill limit_date and 'priority_scale') second_obj = 2 * 1 = 2 (days untill limit_date and 'priority_scale') What i want, is to order them with a "junction" of 'priority_scale' and 'limit_date'. I am very confused here and sorry by this very poor text.

Answer 1

I would suggest that this is a case for storing the value at the database level as an integer, and creating a model method to present it to views and users as "red" etc.

PRIORITY_CHOICES = [
    (3, "Red"),
    (2, "Yellow"),
    (1, "Green"),
    (0, "Null")
]
priority = models.IntegerField(choices=PRIORITY_CHOICES, max_length=15,         
     )  # default=0 ? 

In views `todo_instance.get_priority_display()` will return `"Red"` etc.

Where I can't help, is how to get the number of days left at the level of the database, so you can compute the value to annotate and order_by using F expressions. (I've looked at database functions, but I can't immediately see how to do a date difference. I'm thinking "it must be possible"...)