CODEWITHSUNDEEP
pythondjangosql-order-bydjango-querysetcalculated-field
Rabih Kodeih
Rabih Kodeih

Reputation: 9521

Django 1.6: How to order a query set by a computed DateTimeField

I have a project model having a DateTimeField and duration PositiveIntegerField fields.

The model also has a function days_left implementing relatively involved logic to compute the number of days left till expiry. The function returns an integer.

I want to preform a simple queryset ordering by the value returned by this function.

    Project.objects.all().order_by('days_left')

However I keep getting an exception to the effect that days_left is not a field.

Is there any efficient way to do this in native SQL (maybe through views, etc..) and bypass the queryset or does there exist a django solution for such cases?

The whole code:

import datetime as nsdt
from django.db import models 
from django.utils import timezone

class Project(models.Model):
    name                        = models.CharField(max_length=128)
    publish_date                = models.DateTimeField()
    duration_days               = models.PositiveIntegerField() 

    def days_left(self):
        t1 = timezone.now()
        t2 = self.publish_date + nsdt.timedelta(days=self.duration_days)
        return (t2 - t1).days if t2 > t1 else 0


if __name__ == '__main__':

    print Project.objects.all().order_by('days_left')
    # throws excpetion: 
    #    django.core.exceptions.FieldError: Cannot resolve keyword 'days_left_computed' into field.

Upvotes: 0

Views: 376

Answers (1)

Rod Xavier
Rod Xavier

Reputation: 4043

Since sorting is happening on the database level, you cannot use Django's order_by for this. Instead, you can try sorting the objects using sorted().

projects = sorted(Project.objects.all(), key=lambda x: x.days_left())

Update:

Since you have a large number of records, maybe you can use the Queryset.extra() method. Source

Another approach you may try is using django.db.models.F.

Example using F() (disclaimer: this was not tested)

from django.db.models import F

projects = Project.objects.all().order_by((F('publish_date') + nsdt.timedelta(days=F('duration_days'))) - timezone.now())

Upvotes: 2

Related Questions