Django：如何使用SubQuery注释M2M或OneToMany字段？ [英] Django: How to annotate M2M or OneToMany fields using a SubQuery?

问题描述

我有 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 an attribute of an order, I just get the last order operation attribute instead, using a Subquery.

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

class Order(models.Model)
    # ...

class OrderQuerySet(query.Queryset):

    @staticmethod
    def _last_oo(field):
        return Subquery(OrderOperation.objects
                        .filter(order_id=OuterRef("pk"))
                        .order_by('-id')
                        .values(field)
                        [:1])

    def annotated_total(self):
        return self.annotate(oo_total=self._last_oo('total'))

这样，我可以运行 my_order_total = Order.objects.annotated_total（）[0] .oo_total 。

计算总数很容易，因为它是简单的价值。但是，当存在M2M或OneToMany字段时，此方法不起作用。例如，使用上面的示例，我们添加以下字段：

Computing total is easy as it's a simple value. However, when there is a M2M or OneToMany field, this method does not work. For example, using the example above, let's add this field:

class OrderOperation(models.Model):
    order = models.ForeignKey(Order)
    total = DecimalField(max_digits=9, decimal_places=2)
    ordered_articles = models.ManyToManyField(Article,through='orders.OrderedArticle')                                       

编写类似以下内容的方法不起作用，因为它仅返回1个外键（不是所有FK的列表）：

Writing something like the following does NOT work as it returns only 1 foreign key (not a list of all the FKs):

def annotated_ordered_articles(self):
    return self.annotate(oo_ordered_articles=self._last_oo('ordered_articles'))

目的

整个目的是允许用户在所有订单中进行搜索，并在输入中提供列表或文章。例如：请找到所有至少包含第42条或第43条的订单，或请找到所有完全包含第42条和第43条的订单，等等。

The purpose

The whole purpose is to allow a user to search among all orders, providing a list or articles in input. For example: "Please find all orders containing at least article 42 or article 43", or "Please find all orders containing exactly article 42 and 43", etc.

如果我可以得到类似的东西：

If I could get something like:

>>> Order.objects.annotated_ordered_articles()[0].oo_ordered_articles
<ArticleQuerySet [<Article: Article42>, <Article: Article43>]>

甚至：

>>> Order.objects.annotated_ordered_articles()[0].oo_ordered_articles
[42,43]

那会解决我的问题。

• 也许类似于 ArrayAgg （我正在使用pgSQL）可以解决问题，但是我不确定在我的情况下如何使用它。


• 也许这与 values（）方法有关，该方法似乎不打算处理文档中所述的M2M和1TM关系：

• Maybe something like ArrayAgg (I'm using pgSQL) could do the trick, but I'm not sure to understand how to use it in my case.
• Maybe this has to do with values() method that seems to not be intended to handle M2M and 1TM relations as stated in the doc:

values（）和values_list（）均是针对
特定用例的优化：检索不包含
创建模型实例的开销。当处理
多对多关系和其他多值关系（例如反向外键的一对多
关系）时，此隐喻会崩溃，因为一行，一个对象
的假设不成立。

values() and values_list() are both intended as optimizations for a specific use case: retrieving a subset of data without the overhead of creating a model instance. This metaphor falls apart when dealing with many-to-many and other multivalued relations (such as the one-to-many relation of a reverse foreign key) because the "one row, one object" assumption doesn’t hold.

推荐答案

ArrayAgg <如果您只想从所有文章中获取一个变量（即名称），则/ code>会很棒。如果您需要更多，还有一个更好的选择：

ArrayAgg will be great if you want to fetch only one variable (ie. name) from all articles. If you need more, there is a better option for that:

相反，您可以预取每个订单，后期 OrderOperation 的整体对象。这样便可以轻松从 OrderOperation`获取任何字段而无需额外的魔法。

Instead, you can prefetch for each Order, latesOrderOperationas a whole object. This adds the ability to easily get any field fromOrderOperation` without extra magic.

唯一的警告是，您将始终获得列出一个操作或一个空列表（当所选订单没有任何操作时）。

The only caveat with that is that you will always get a list with one operation or an empty list when there are no operations for selected order.

为此，您应该使用 prefetch_related 查询集模型与 Prefetch 对象和自定义查询 OrderOperation 。示例：

To do that, you should use prefetch_related queryset model together with Prefetch object and custom query for OrderOperation. Example:

from django.db.models import Max, F, Prefetch

last_order_operation_qs = OrderOperation.objects.annotate(
    lop_pk=Max('order__orderoperation__pk')
).filter(pk=F('lop_pk'))

orders = Order.objects.prefetch_related(
    Prefetch('orderoperation_set', queryset=last_order_operation_qs, to_attr='last_operation')
)

然后，您可以只使用 order.last_operation [0] .ordered_articles 来获取特定订单的所有订购商品。您可以在第一个查询集中添加 prefetch_related（’ordered_articles'），以提高性能并减少对数据库的查询。

Then you can just use order.last_operation[0].ordered_articles to get all ordered articles for particular order. You can add prefetch_related('ordered_articles') to first queryset to have improved performance and less queries on database.

这篇关于Django：如何使用SubQuery注释M2M或OneToMany字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！