Django：使用传播数据优化查询 [英] Django: optimizing a query with spread data

问题描述

我有 Order 对象和 OrderOperation 对象，它们表示对订单的操作（创建，修改，取消）

I have Order objects and OrderOperation objects that represent an action on a Order (creation, modification, cancellation).

从概念上讲，一个订单具有1到许多订单操作。每次对订单执行一次操作时，都会在该操作中计算总数。这意味着当我需要查找订单总数时，我只获得上次订单操作总数。

Conceptually, an order has 1 to many order operations. Each time there is an operation on the order, the total is computed in this operation. Which means when I need to find the total of an order, I just get the last order operation total.

class OrderOperation(models.Model):
    order = models.ForeignKey(Order)
    total = DecimalField(max_digits=9, decimal_places=2)

class Order(models.Model):

    @property
    def last_operation(self) -> Optional['OrderOperation']:
        try:
            qs = self.orderoperation_set.all()
            return qs[len(qs) - 1]
        except AssertionError:  # when there is a negative indexing (no operation)
            # IndexError can not happen
            return None

    @property
    def total(self) -> Optional[Decimal]:
        last_operation = self.last_operation
        return last_operation.total if last_operation else None

问题

由于我收到很多订单，因此每次我想进行简单的过滤，例如总计低于5欧元，这需要花费很长时间，因为我需要使用以下明显不好的查询来浏览所有订单：

The issue

Since I get lots of orders, each time I want to make a simple filtering like "orders that have a total lower than 5€", it takes a long time, because I need to browse all orders, using the following, obviously bad query:

all_objects = Order.objects.all()
Order.objects.prefetch_related('orderoperation_set').filter(
    pk__in=[o.pk for o in all_objects if o.total <= some_value])

我当前的想法/尝试的事情

数据非规范化？

我可以简单地创建总计 属性 Order ，并在每次创建操作时将操作总计复制到订单总计。
然后， Order.objects.filter（total__lte = some_value）可以工作。
但是，在复制数据库中的数据之前，我想确保没有更简单/更干净的解决方案。

I could simply create a total attribute on Order, and copy the operation total to the order total every time on operation is created. Then, Order.objects.filter(total__lte=some_value) would work. However, before duplicating data in my database, I'd like to be sure there is not an easier/cleaner solution.

使用注释（

我希望能够做到： Order.objects.annotate（total = something_magical_here）.filter（ total__lte = some_value）。

先过滤然后匹配？

order_operations = OrderOperation.objects.filter(total__lte=some_value)
orders = Order.objects.filter(orderoperation__in=order_operations)

这非常快，但是由于我没有过滤最后的操作，但是所有的操作都没有过滤，因此过滤效果很差。这是错误的。

This is very fast, but the filtering is bad since I didn't filter last operations, but all operations here. This is wrong.

还有其他想法吗？谢谢。

Any other idea? Thanks.

推荐答案

使用annotate（）方法

Using annotate() method

似乎不可能。

It seems it's not possible.

当然，这是可能的；）您可以使用子查询或一些巧妙的条件表达式。假设您想从上次订单操作中获得总金额，下面是带有子查询的示例：

Of course, it is possible ;) You can use subqueries or some clever conditional expressions. Assuming that you want to get total amount from last order operation, here is example with subquery:

from django.db.models import Subquery, OuterRef

orders = Order.objects.annotate(
    total=Subquery(                             # [1]
        OrderOperation.objects \
            .filter(order_id=OuterRef("pk")) \  # [2]
            .order_by('-id') \                  # [3]
            .values('total') \                  # [4]
            [:1]                                # [5]
    )
)

上面的代码：

1. 我们正在向结果列表添加新字段，称为总计 taht将由子查询填充。您可以在此查询集中将其作为模型 Order 的任何其他字段进行访问（在评估之后，在模型实例中或在过滤和其他注释中）。您可以从 Django文档了解注释的工作方式。 / li>
   
2. 子查询仅应针对当前订单中的操作调用。 OuterRef 只是将替换为对结果SQL查询中所选字段的引用。


3. 我们要按操作进行排序 id 降序，因为我们确实想要最新的。如果您要在操作中有其他字段要排序（例如创建日期），请在此处填写。


4. 该子查询应该只返回总计操作中的值


5. 我们只需要一个元素。使用切片符号而不是普通索引来获取它，因为在Django查询集上使用index会立即调用它。切片仅将 LIMIT 子句添加到SQL查询中，而不调用它，这就是我们想要的。

1. We are adding new field to results list, called total taht will be filled in by subquery. You can access it as any other field of model Order in this queryset (either after evaluating it, in model instances or in filtering and other annotations). You can learn how annotation works from Django docs.
2. Subquery should only be invoked for operations from current order. OuterRef just will be replaced with reference to selected field in resulting SQL query.
3. We want to order by operation id descending, because we do want latest one. If you have other field in your operations that you want to order by instead (like creation date), fill it here.
4. That subquery should only return total value from operation
5. We want only one element. It is being fetched using slice notation instead of normal index, because using index on django querysets will immediately invoke it. Slicing only adds LIMIT clause to SQL query, without invoking it and that is what we want.

现在您可以使用：

orders.filter(total__lte=some_value)

仅提取您想要的订单。您也可以使用该注释来

to fetch only orders that you want. You can also use that annotation to

这篇关于Django：使用传播数据优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！