Using annotations and aggregations without getting duplicates

Question

I have a queryset in my Django app. The queryset is accessing information across several tables filled with products information (price per provider, name, stock, etc... the usual stuff that you'd find in a shopping-related application).

Since one product can have multiple prices, I'm ending up with duplicated products. This is actually fine and logical, since SQL is just showing me as many duplicated products as prices each product has.

That's where I use aggregations:

queryset.annotate(
   annotate_min_price=Min("product_prices__price"),
)

That makes my queryset to return only the lowest price for each product, which stops the products from getting duplicated.

At this point the query looks like this:

SELECT DISTINCT 
    "prod_prod"."id",
    ...
    MIN ( "monetary_prodprice"."system_all_included_price" ) AS "annotate_min_price" 
FROM
    "prod_prod"
    INNER JOIN "monetary_prodprice" ON ( "prod_prod"."id" = "monetary_prodprice"."prod_id" )
    INNER JOIN "monetary_pricelist" ON ( "monetary_prodprice"."pricelist_id" = "monetary_pricelist"."id" )
    INNER JOIN "monetary_pricelistdestinations" ON ( "monetary_pricelist"."id" = "monetary_pricelistdestinations"."pricelist_id" )
    INNER JOIN "prodtransaction_carrier_pricelists" ON ( "monetary_pricelist"."id" = "prodtransaction_carrier_pricelists"."pricelist_id" )
    INNER JOIN "prodtransaction_carrier" ON ( "prodtransaction_carrier_pricelists"."carrier_id" = "prodtransaction_carrier"."id" )
    INNER JOIN "prodtransaction_carrierdelivery" ON ( "prodtransaction_carrier"."id" = "prodtransaction_carrierdelivery"."carrier_id" )
    INNER JOIN "monetary_pricelistcountry" ON ( "monetary_pricelist"."id" = "monetary_pricelistcountry"."pricelist_id" ) 
WHERE
    (
        ...
    ) 
GROUP BY
    "prod_prod"."id",
ORDER BY
    "annotate_min_price" DESC

The problem is that, besides the lowest price, I need to get the actual ID of that price. So I modify my queryset accordingly:

queryset.annotate(
    annotate_min_price=Min("prod_prices__system_all_included_price"),
    annotate_best_price=F('prod_prices__pk')).order_by(ordering)

This is where I'm hitting the problem. This will produce the following query:

SELECT DISTINCT 
    "prod_prod"."id",
    ...
    MIN ( "monetary_prodprice"."system_all_included_price" ) AS "annotate_min_price",
    "monetary_prodprice"."id" ) AS "annotate_best_price" 
FROM
    "prod_prod"
    INNER JOIN "monetary_prodprice" ON ( "prod_prod"."id" = "monetary_prodprice"."prod_id" )
    INNER JOIN "monetary_pricelist" ON ( "monetary_prodprice"."pricelist_id" = "monetary_pricelist"."id" )
    INNER JOIN "monetary_pricelistdestinations" ON ( "monetary_pricelist"."id" = "monetary_pricelistdestinations"."pricelist_id" )
    INNER JOIN "prodtransaction_carrier_pricelists" ON ( "monetary_pricelist"."id" = "prodtransaction_carrier_pricelists"."pricelist_id" )
    INNER JOIN "prodtransaction_carrier" ON ( "prodtransaction_carrier_pricelists"."carrier_id" = "prodtransaction_carrier"."id" )
    INNER JOIN "prodtransaction_carrierdelivery" ON ( "prodtransaction_carrier"."id" = "prodtransaction_carrierdelivery"."carrier_id" )
    INNER JOIN "monetary_pricelistcountry" ON ( "monetary_pricelist"."id" = "monetary_pricelistcountry"."pricelist_id" ) 
WHERE
    (
        ...
    ) 
GROUP BY
    "prod_prod"."id",
    "monetary_prodprice"."id"
ORDER BY
    "annotate_min_price" DESC

This makes my queryset duplicate the products. I understand that this is happening because I'm asking PostgreSQL to add the ID of the prices to each row (product), and that somehow breaks the MIN aggregator.

My question is: how can I make Django return only the products with the lowest price and the ID of that price at the same time?

Answer 1

you can use subqueries

min_query =
    ProductPrice.objects.filter(product_id=OuterRef('id'))
    .order_by('system_all_included_price')
    .values('system_all_included_price', 'id')[:1]

queryset.annotate(
    annotate_min_price=Subquery(
        min_query.values('system_all_included_price')
    ).annotate(
    annotate_min_id=Subquery(
        min_query.values('id')
    )
).order_by(ordering)

Solution below does not work as pointed out in the comments because you cannot reference a window function in the filter

You should be able to annotate each product with the min price

queryset.annotate(
    annotate_min_price=Window(
        expression=Min("prod_prices__system_all_included_price"),
        partition_by=F('prod_prices__pk'),
        order_by=ordering,
    ),
).filter(annotate_min_price=F('prod_prices__system_all_included_price)

if you can have equal prices in some cases then you might want to go again through a annotate partition by to identify the lowest id this time and then filter on the ID matching the lowest.

queryset.annotate(
    annotate_min_id=Window(
        expression=Min("prod_prices__pk"),
        partition_by=F('prod_prices__system_all_included_price'),
        order_by=ordering,
    ),
).filter(annotate_min_id=F('prod_prices__pk'))