python - 使用注释和聚合而不重复

Question

我的 Django 应用程序中有一个 queryset。查询集正在访问包含产品信息(每个提供商的价格、名称、库存等)的多个表中的信息...您在购物时会找到的常见东西-相关应用)。

由于一个产品可以有多个价格，我最终得到了重复的产品。这实际上很好并且合乎逻辑，因为 SQL 只是向我显示与每个产品的价格一样多的重复产品。

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

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

这使我的查询集只返回每种产品的最低价格，从而防止产品重复。

此时查询看起来像这样:

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。

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