sort django queryset by latest instance of a subse

Question

I have an Order model and order_event model. Each order_event has a foreignkey to order. so from an order instance i can get: myorder.order_event_set. I want to get a list of all orders but i want them to be sorted by the date of the last event. A statement like this works to sort by the latest event date:

queryset = Order.objects.all().annotate(
    latest_event_date=Max('order_event__event_datetime')
    ).order_by('latest_event_date')

However, what I really need is a list of all orders sorted by latest date of A SUBSET OF EVENTS. For example my events are categorized into "scheduling", "processing", etc. So I should be able to get a list of all orders sorted by the latest scheduling event. This django doc (https://docs.djangoproject.com/en/dev/topics/db/aggregation/#filter-and-exclude) shows how I can get the latest schedule event using a filter but this excludes orders without a scheduling event.

I thought I could combine the filtered queryset with a queryset that includes back those orders that are missing a scheduling event...but I'm not quite sure how to do this. I saw answers related to using python list but it would be much more useful to have a proper django queryset (ie for a view with pagination, etc.)

EDIT re: comments/suggestions

class Order(models.Model):
    desc = models.CharField(max_length=30)

class Order_event(models.Model):
    order = models.ForeignKey(Order)
    event_datetime = models.DateTimeField(auto_now_add = True)

Also, I don't mind doing it in more than one statement. Also, sorting, etc via python instead of the orm is ok since i'm not working with huge datasets. If possible I would prefer to stay away from the sql for reasons you mentioned. Combining separate, sorted querysets seems promising, but i'm getting stuck.

Answer 1

Ok, I think you can achieve this with the extra method, but not, as far as I can tell, with pure database-agnostic orm. This means you'll have to ensure your sql works for the db backend you're using, and to be wary of django aliasing the table names, but it will also mean you can do it all with one query.

Something along these lines should work:

latest_event_subquery = 'select max(event_datetime) ' \
                        'from appname_order_event ' \
                        'where appname_order_event.category="scheduling" and ' \
                              'appname_order_event.event_id=appname_order.id'
queryset = Order.objects.extra(select={'latest_event_date': latest_event_subquery}).order_by('latest_event_date')

Does that make sense?

Answer 2

Greg's answer is great, and I used it for awhile. However, I wanted to access other fields of latest_event. So, I ended up storing the latest_event as a field of Order:

# The latest event
latest_event = db.ForeignKey('whateverapp.Event', editable=False, null=True)

Then I set up a signal to recalculate the latest event upon save:

@receiver(post_save, sender=Event)
def visit_calculate_latest(sender, instance, **kwargs):
    """Recalculate latest event"""
    instance.parent.latest_event = instance.parent.get_latest_event()

where get_latest_event() did the query:

def get_latest_event(self):
    """Return most recent event or None."""
    try:
        return Event.objects.filter(
            parent=self.id).order_by('-event_date', '-id')[0]
    except IndexError:
        return None

Now I can filter by latest_event__whatever:

    return qs.filter(qobjs).order_by('-latest_event__event_date',
        '-latest_event__pk')