Allowing queries to be ordered by model methods

Django Internals ORM
1

As I’ve been working with Django queries, I can’t help but recognize how nice it would be to order queries by a model method, as model methods are meant to act like a field to their respective model. Obviously this cannot be, as the model methods are not actually columns in the model’s table. However, I have also discovered a workaround that could potentially turn into a solution.

Currently, I check if the field to be passed in to order_by is a field name or a model method name. If it is the name of a model method, then I do not include order_by in the query. Instead, I run:

sorted(query_set, key= lambda obj: Model.modelmethod(obj))

I am not sure what the performance hit of this is, but it works great in creating the illusion that the results were ordered by the model method field.

To implement this in to django, there would have to be a similar check to see if the field passed in is a field name or a model method name. It would then need to wait until all of the .order_by() calls are made as it is my understanding that it can be called several times thanks to Django’s lazy querying. Following the same logic, order_by could then perform a basic sort on the queryset as exemplified above as opposed to actually passing any fields in to the query.

I am looking for thoughts and suggestions on this idea and the implementation, as I am currently looking in to how to do so.

Full Example:

class Project(models.Model):
  due_date = models.DateTimeField()

  # I want to order a query for projects by priority
  @property
  def priority(self):
        '''
        declares the object priority with 1 being highest priority and 3 being least
        '''
        if (self.due_date - timezone.now()).total_seconds() < 0:
            return 1
        elif round(((self.due_date - timezone.now()).days * 24) + ((self.due_date - timezone.now()).seconds / 60 / 60)) < 25:
            return 2
        else:
            return 3
1 Like
2

For me its not clear why you aren’t using the order_by() method to order querysets. Can you give some example of what you are trying to achieve?

3

@MaximilianKindshofer I edited my response with an example to help visualize. You cannot order_by a model method, which is priority above. This is because model methods and properties are not saved to the table and therefore can’t be used in a query.

4

Now I understand that you don’t want to just order a queryset but order based on the return value of a function of a Django Model.
Some optimizations come to mind like cached properties and overwriting the save method but yeah as far as I know this can’t be done right now. But it would be nice…

1 Like
5

Hmmm… I’m not sure I agree that this would be a good idea.

I’d be wary of anything that would add “local processing” to a queryset that isn’t obvious that it’s occurring.

The one thing you can guarantee about a queryset is that the entire queryset is passed along to the database and executed there. Now you’re talking about blurring the lines between what’s executed in the database and what’s executed in your Python code - with some unidentified performance implications.

I think I’d prefer something that would be added to a custom manager to perform post-processing on the resultset after it has been returned from the database, than having some magic try to occur to process a queryset in two places.

(As a side note, the example you provide could be implemented with a annotation to have all the work performed in the database. It wouldn’t look as nice as your model method, but it is functional.)

2 Likes
6

The main problem is slicing. What if you only wanted the first 10 results? The DB needs to understand how to order the results so it knows which 10 to return. SQL can achieve what you need, but you need to write it in SQL (or something the ORM will understand, like an annotate).

If you absolutely must sort using python, execute the query first, then sort the results in a list or something.

7

Hello!

I do not think it would be a good idea. Sorting in that way would have to be done client-side (i.e., python-side). What if your queryset has 10 millions rows? 1 billion? You’d have to get all of it, and then sort locally. I think.
Instead, I believe it is doable, in your example at least, to work with annotations around the Case/When django methods.

Something like this? (untested just scribbled this )

from datetime import datetime, timedelta
from django.db.models import Q, Case, When, Value
from django.utils import timezone

now = timezone.now()
prio_1_threshold = timedelta(minutes=30)
prio_2_threshold = timedelta(hours=2)


projs = Project.objects.annotate(
  priority=Case(
    When(
      due_date__range=[
        now,
        now - Value(prio_1_threshold)
      ],
      then=Value(1),
    ),
    When(
      due_date__range=[
        now - Value(prio_1_threshold),
        now - Value(prio_2_threshold)
      ],
      then=Value(2),
    ),
    default=Value(3),
    output_field=models.IntegerField()
  )
).filter(
  priority=2
)

Now this is unlikely to be lightning fast on a large table, but I would hope way faster than 100% client-side (the composite key masters will say otherwise, but I am not one…).
Another benefit is that you can (as mentioned by jonathan), slice database side, sort, do Q() filters, based on that “priority variable derived database-side from the ‘due_date’ ‘native’ field”.

1 Like