CODEWITHSUNDEEP
pythondjangoannotations
user5847481
user5847481

Reputation: 55

Django: Annotate the count of the number of duplicate values for each object

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

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.

Upvotes: 3

Views: 3851

Answers (1)

Roman Yakubovich
Roman Yakubovich

Reputation: 893

The first wish was to use window function, but unfortunately it is not allowed to combine 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 details on how this query works in docs

Upvotes: 3

Related Questions