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')))

您可以在 查看全文