如何使用Django ORM关联ComponentInstance与ComponentShipment表

需求 #

需要关联ComponentInstance和ComponentShipment表，查询结果包含：

• ComponentInstance字段：component, serial_number, condition_received, date_received, received_from, quantity
• ComponentShipment字段：date_shipped, shipped_to, shipped_condition, invoice
• 未发货的组件，对应的ComponentShipment字段需返回Null

模型代码 #

class ComponentInstance(models.Model):
    component = models.ForeignKey('Component', on_delete=models.CASCADE)
    serial_number = models.CharField(max_length=50)
    customer = models.ForeignKey('Customer', on_delete=models.CASCADE, default=1)
    condition_received = models.ForeignKey("Condition", on_delete=models.CASCADE)
    date_received = models.DateField(default=django.utils.timezone.now)
    time_create = models.DateTimeField(auto_now_add=True)
    time_update = models.DateTimeField(auto_now=True)
    updated_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='updated_by_user', null=True, blank=True)
    created_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='created_by_user', null=True, blank=True)
    received_from = models.CharField(max_length=30)
    staff_received = models.ForeignKey("StoreStaff", on_delete=models.CASCADE)
    quantity = models.PositiveSmallIntegerField(default=1)
    unit = models.ForeignKey('QuantityType', on_delete=models.CASCADE, default=1)
    location = models.ForeignKey('Location', on_delete=models.CASCADE, null=True, blank=True)
    notes = models.CharField(max_length=50, null=True, blank=True)

    def __str__(self):
        return f"{self.component} {'S/N  '} {self.serial_number}"

    class Meta:
        verbose_name = "Component"
        verbose_name_plural = "Components"
        ordering = ('component',)


class ComponentShipment(models.Model):
    component = models.ForeignKey('ComponentInstance', on_delete=models.CASCADE)
    date_shipped = models.DateField(default=django.utils.timezone.now)
    shipped_to = models.CharField(max_length=30, null=True, blank=True)
    shipped_condition = models.ForeignKey("Condition", on_delete=models.CASCADE)
    invoice = models.CharField(max_length=30, null=True, blank=True)
    staff_shipped = models.ForeignKey("StoreStaff", on_delete=models.CASCADE, null=True, blank=True)
    scrapped_company = models.ForeignKey('RepairCompany', on_delete=models.CASCADE, null=True, blank=True)

    def __str__(self):
        return f'{self.component} {self.date_shipped} {self.shipped_to}'

解决方案 #

场景1：每个组件仅对应一条发货记录 #

如果业务上一个组件只能发货一次，推荐将ComponentShipment的外键改为一对一关联，简化查询：

修改ComponentShipment的外键字段：

component = models.OneToOneField('ComponentInstance', on_delete=models.CASCADE, related_name='shipment')

使用select_related做左外连接查询，获取所有组件（含未发货的）：

from django.db.models import F

queryset = ComponentInstance.objects.select_related('shipment').values(
    # ComponentInstance字段
    'component__name',  # 替换为Component模型实际字段，比如id或name
    'serial_number',
    'condition_received__name',  # 替换为Condition模型实际字段
    'date_received',
    'received_from',
    'quantity',
    # ComponentShipment字段，未发货则为Null
    shipment_date=F('shipment__date_shipped'),
    shipment_to=F('shipment__shipped_to'),
    shipment_condition=F('shipment__shipped_condition__name'),
    shipment_invoice=F('shipment__invoice')
)

# 遍历结果
for item in queryset:
    print(item)

场景2：组件可能有多条发货记录 #

如果一个组件可以多次发货，需要获取最新的发货记录，用Subquery实现：

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

# 子查询：获取每个ComponentInstance的最新发货记录ID
latest_shipment = ComponentShipment.objects.filter(
    component=OuterRef('pk')
).order_by('-date_shipped').values('pk')[:1]

# 主查询：关联最新发货记录
queryset = ComponentInstance.objects.annotate(
    latest_shipment_id=Subquery(latest_shipment)
).select_related('componentshipment').values(
    # ComponentInstance字段
    'component__name',
    'serial_number',
    'condition_received__name',
    'date_received',
    'received_from',
    'quantity',
    # 最新发货记录字段
    shipment_date=F('componentshipment__date_shipped'),
    shipment_to=F('componentshipment__shipped_to'),
    shipment_condition=F('componentshipment__shipped_condition__name'),
    shipment_invoice=F('componentshipment__invoice')
)

关键说明 #

• 代码中component__name、condition_received__name是假设关联模型有name字段，需根据你的实际模型调整（比如用component_id直接取外键ID）。
• 若需要模型实例而非字典，去掉values()，直接通过实例属性访问：instance.shipment.date_shipped（未发货时instance.shipment为None，需做判空处理）。

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