Python – Django: Comment on the count of duplicate values per object

I have a model called Product as shown 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, suppose I have 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

What I want to do is create a query that annotates each “good” product with a duplicate count and the lowest price for each “bc_sku”. Then I want to be able to use these objects and values in the 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 appreciated as I am struggling to get the comments and filters to make it work at the moment.

The first wish is to use window function, Unfortunately it is not allowed in combination with annotate(Window(...)). and filter(...).

The answer is:

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

You can find docs find more information about how this query works