通过相关模型子集的最新实例对django查询进行排序 [英] sort django queryset by latest instance of a subset of related model

问题描述

我有一个订单模型和order_event模型。每个order_event都有一个外键订购。所以从订单实例我可以得到： myorder.order_event_set 。我想得到所有订单的列表，但我希望他们按照最后一个事件的日期进行排序。这样的声明可以按照最新的事件日期排序：

  queryset = Order.objects.all（）。annotate（
 latest_event_date = Max（'order_event__event_datetime'）
）.order_by（'latest_event_date'）
  

然而，我真正需要的是按照活动的最新日期排序的所有订单的列表。例如我的事件被分类为调度，处理等。因此，我应该能够得到由最新调度事件排序的所有订单的列表。此django文档（https://docs.djangoproject.com/en/dev/topics/db/aggregation/#filter-and-exclude）显示了如何使用过滤器获取最新的计划事件，但这排除了没有计划的订单事件。

我以为我可以将过滤的查询器与一个查询器结合在一起，其中包含那些缺少调度事件的订单，但我不太确定去做这个。我看到有关使用python列表的答案，但是要有一个正确的django查询器（即对于一个分页视图等），这将会更有用。

编辑：评论/建议

  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）
  

此外，我不介意在多个语句中执行此操作。此外，通过python而不是orm排序等等，因为我没有使用巨大的数据集。如果可能，我宁愿远离sql，因为你提到的原因。结合单独的排序查询器看起来很有希望，但是我被困住了。

解决方案

好的，我想你可以用 extra 方法，但不能，只要我能告诉，与纯数据库不可知的orm。这意味着您必须确保您的sql适用于您正在使用的db后端，并且要警惕django对表名进行别名，但这也意味着您可以使用一个查询来完成所有操作。

这些行应该有效：

  latest_event_subquery ='select max（event_datetime）' \ 
'from appname_order_event'\ 
'其中appname_order_event.category =计划和'\ 
'appname_order_event.event_id = appname_order.id'
 queryset = Order。 objects.extra（select = {'latest_event_date'：latest_event_subquery}）。order_by（'latest_event_date'）
  

这是否有意义？

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.

解决方案

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?

这篇关于通过相关模型子集的最新实例对django查询进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！