使用注释和聚合而不获取重复项

Question

我的Django应用程序中有一个queryset。查询集在多个表中访问信息，这些表中填充了产品信息（每个提供商的价格、名称、库存等）。。。在购物相关应用程序中常见的东西）。你知道吗

因为一个产品可以有多个价格，我最终会得到重复的产品。这实际上很好，也很符合逻辑，因为SQL只是向我展示了与每个产品的价格一样多的重复产品。你知道吗

这就是我使用聚合的地方：

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

这使得我的queryset只返回每个产品的最低价格，这就阻止了产品被复制。你知道吗

此时，查询如下所示：

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

问题是，除了最低价格之外，我还需要得到该价格的实际ID。因此，我相应地修改了查询集：

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

这就是我要解决的问题。这将产生以下查询：

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

这使得我的查询集与产品重复。我理解这种情况的发生是因为我要求PostgreSQL将价格的ID添加到每一行（产品），这会以某种方式破坏MIN聚合器。你知道吗

我的问题是：如何让Django只退回最低价格的产品，同时退回该价格的ID？你知道吗

Answer 1

可以使用子查询

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)

下面的解决方案不能像注释中指出的那样工作，因为您不能引用过滤器中的窗口函数

你应该能够用最低价格来标注每个产品

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)

如果在某些情况下可以有相同的价格，那么您可能希望再次通过注释分区来标识最低的id，然后筛选与最低id匹配的id。你知道吗

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