Django 不支持复合主键时查询供应商对指定买家折扣的实现方案

Django本身不支持复合主键，但不需要依赖复合主键也能实现你的业务需求，以下是两种可落地的方案：

方案1：保留现有表结构，通过联合唯一约束查询 #

你已经给Discount表设置了supplier_id和buyer_id的联合唯一约束，该约束已经能保证同一供应商对同一买家只会有一个有效折扣，完全可以替代复合主键的业务唯一性作用。

模型定义参考 #

from django.db import models
from django.contrib.auth import get_user_model
User = get_user_model()

class Discount(models.Model):
    supplier = models.ForeignKey(User, on_delete=models.CASCADE, related_name="supplied_discounts")
    buyer = models.ForeignKey(User, on_delete=models.CASCADE, related_name="received_discounts")
    discount_rate = models.DecimalField(max_digits=5, decimal_places=2, help_text="示例值0.8代表8折")

    class Meta:
        # Django 3.2+ 推荐用UniqueConstraint替代unique_together，扩展性更强
        constraints = [
            models.UniqueConstraint(fields=['supplier', 'buyer'], name='unique_supplier_buyer_discount')
        ]

class Product(models.Model):
    product_name = models.CharField(max_length=128)
    supplier = models.ForeignKey(User, on_delete=models.CASCADE, related_name="supplied_products")
    buyer = models.ForeignKey(User, on_delete=models.CASCADE, related_name="available_products")
    price = models.DecimalField(max_digits=10, decimal_places=2)

常见场景查询代码 #

• 单个商品查询场景

# 假设当前登录用户为买家，查看id为12的商品详情
product = Product.objects.get(id=12)
try:
    discount = Discount.objects.get(supplier_id=product.supplier_id, buyer_id=request.user.id)
    discounted_price = product.price * discount.discount_rate
except Discount.DoesNotExist:
    # 无匹配折扣时默认返回原价
    discounted_price = product.price

• 商品列表批量查询场景（避免N+1查询问题）

from django.db.models import Subquery, OuterRef, F, DecimalField

current_buyer_id = request.user.id
# 构造折扣子查询
discount_subquery = Discount.objects.filter(
    supplier_id=OuterRef('supplier_id'),
    buyer_id=current_buyer_id
).values('discount_rate')[:1]

# 批量查询当前买家可见的所有商品，并附带计算折扣价
products = Product.objects.filter(
    buyer_id=current_buyer_id
).annotate(
    discount_rate=Subquery(discount_subquery, output_field=DecimalField(max_digits=5, decimal_places=2))
).annotate(
    discounted_price=F('price') * F('discount_rate')
)

# 处理无折扣的商品
for product in products:
    if not product.discounted_price:
        product.discounted_price = product.price

方案2：抽取供应商-买家关联表（适合业务后续扩展） #

如果后续还会新增供应商和买家的绑定属性（比如专属账期、配送规则、合同有效期等），可以单独抽取一张关联表，Discount和Product都关联该表的单主键，逻辑更清晰。

调整后模型定义参考 #

from django.db import models
from django.contrib.auth import get_user_model
User = get_user_model()

# 新增供应商-买家关联表
class SupplierBuyerRelation(models.Model):
    supplier = models.ForeignKey(User, on_delete=models.CASCADE, related_name="buyer_relations")
    buyer = models.ForeignKey(User, on_delete=models.CASCADE, related_name="supplier_relations")
    # 可扩展其他公共属性：is_active、contract_no、delivery_cycle等

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['supplier', 'buyer'], name='unique_supplier_buyer_relation')
        ]

class Discount(models.Model):
    # 折扣和关联表一对一绑定
    relation = models.OneToOneField(SupplierBuyerRelation, on_delete=models.CASCADE, primary_key=True)
    discount_rate = models.DecimalField(max_digits=5, decimal_places=2)

class Product(models.Model):
    product_name = models.CharField(max_length=128)
    relation = models.ForeignKey(SupplierBuyerRelation, on_delete=models.CASCADE, related_name="products")
    price = models.DecimalField(max_digits=10, decimal_places=2)

查询代码示例 #

# 批量查询当前买家可见的商品及对应折扣
products = Product.objects.filter(
    relation__buyer_id=request.user.id
).select_related('relation__discount')

for product in products:
    if hasattr(product.relation, 'discount'):
        discounted_price = product.price * product.relation.discount.discount_rate
    else:
        discounted_price = product.price

内容的提问来源于stack exchange，提问作者Basquiat