Django:注释每个对象重复值的数量 [英] Django: Annotate the count of the number of duplicate values for each object

问题描述

我有一个名为 Product 的模型，如下所示.产品可以具有相同的字段bc_sku".

I have a model called Product seen below. Products can have the same field 'bc_sku'.

class Product(models.Model)

    bc_sku                          = models.IntegerField(null=True, blank=True)
    product_type                    = models.CharField(null=True, blank=True, max_length=50)
    merchant                        = models.CharField(null=True, blank=True, max_length=50)
    product_price                   = models.DecimalField(null=True, blank=True, max_digits=10, decimal_places=2)

例如，假设我有这个对象列表

For example, imagine I had this list of objects

    bc_sku | product_type | merchant | product_price
    100    | good         | A        | 1.00
    100    | bad          | B        | 2.00
    100    | bad          | C        | 3.00
    101    | good         | A        | 7.00
    101    | bad          | B        | 5.00

我想做的是创建一个查询，用重复数量的计数以及每个bc_sku"的最低价格来注释每个好"产品.然后我希望能够在模板中使用这些对象和值.

What I'd like to do is create a query which annotates each "good" product with the count of the number of duplicates along with the minimum price for each 'bc_sku'. I would then like to be able to use these objects and values in a template.

    bc_sku | product_type | merchant | dup_count | min_price
    100    | good         | A        | 3         | 1.00
    101    | good         | A        | 2         | 5.00

任何帮助将不胜感激，因为我正在努力获取注释和过滤器以使其当前正常工作.

Any help would be greatly appreciated as I'm struggling to get annotations and filters to make it work currently.

推荐答案

第一个愿望是使用 窗口函数，可惜是不允许结合annotate(Window(...))和filter(...)

The first wish was to use window function, but unfortunately it is not allowed to combine annotate(Window(...)) and filter(...)

答案是:

from django.db.models import OuterRef, Subquery, Count, Min

subquery = Product.objects.filter(bc_sku=OuterRef('bc_sku')).values('bc_sku')
                          .annotate(dup_count=Count('*'), min_price=Min('product_price'))
Product.objects.filter(product_type='good')
               .annotate(dup_count=Subquery(subquery.values('dup_count')), 
                         min_price=Subquery(subquery.values('min_price')))

您可以在 文档

这篇关于Django:注释每个对象重复值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！