在Django中查询每个Product对应最小mainPrice的VariantProduct

针对你的需求，下面提供几种Django ORM的实现方式，同时纠正你原SQL的问题：

原SQL的问题 #

你写的SQL存在两个问题：

1. 多数SQL数据库（如PostgreSQL）不允许SELECT *配合GROUP BY product，因为非聚合列未包含在GROUP BY子句中，不符合SQL标准。
2. 你的需求是获取mainPrice最小的实例，但SQL里用了min(discountPrice)，和需求不符。

正确的SQL逻辑可以用窗口函数实现：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY mainPrice ASC) AS rn
    FROM variantproduct
) subquery
WHERE rn = 1;

Django ORM实现方法 #

方法1：使用窗口函数（推荐，逻辑清晰） #

通过Window和RowNumber函数给每个Product下的VariantProduct按mainPrice排序，取排序后第一条：

from django.db.models import Window, F
from django.db.models.functions import RowNumber

# 获取每个Product对应的mainPrice最小的VariantProduct
min_price_variants = VariantProduct.objects.annotate(
    # 按product分组，按mainPrice升序编号
    row_num=Window(
        expression=RowNumber(),
        partition_by=F('product'),
        order_by=F('mainPrice').asc()
    )
).filter(row_num=1).select_related('product')

# 遍历结果
for variant in min_price_variants:
    print(f"商品: {variant.product.name}, 最低mainPrice: {variant.mainPrice}")

方法2：使用Subquery和Min聚合 #

先查询每个Product的最小mainPrice，再关联到对应的VariantProduct：

from django.db.models import Subquery, OuterRef, Min

# 子查询：获取每个Product的最小mainPrice
min_main_price_subquery = VariantProduct.objects.filter(
    product=OuterRef('pk')
).values('product').annotate(
    min_mp=Min('mainPrice')
).values('min_mp')

# 获取对应mainPrice等于最小价格的VariantProduct
min_price_variants = VariantProduct.objects.filter(
    mainPrice=Subquery(min_main_price_subquery)
).select_related('product')

方法3：从Product模型出发关联 #

如果需要同时获取Product信息和对应的最小价格Variant，可以用Prefetch：

from django.db.models import Min, OuterRef
from django.db.models.query import Prefetch

# 先给每个Product标注最小mainPrice，再预取对应的VariantProduct
products = Product.objects.annotate(
    min_main_price=Min('variantproduct__mainPrice')
).prefetch_related(
    Prefetch(
        'variantproduct_set',
        queryset=VariantProduct.objects.filter(mainPrice=OuterRef('min_main_price')),
        to_attr='min_price_variant'
    )
)

# 遍历结果
for product in products:
    if product.min_price_variant:
        variant = product.min_price_variant[0]
        print(f"商品: {product.name}, 最低mainPrice: {variant.mainPrice}")

内容的提问来源于stack exchange，提问作者MR.TaChi