如何在Django ORM中两次关联CustomerAddress表实现多条件过滤？

我有以下两个Django模型：

class Customer(AbstractBaseModel):    
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, db_column='uuid')
    name = models.CharField(max_length=55)

class CustomerAddress(AbstractBaseModel):
    uuid = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, db_column='uuid')
    customer = models.ForeignKey(Customer, on_delete=models.DO_NOTHING, related_name='addresses', db_column='customer_uuid')
    country_code = models.CharField(max_length=3)
    type = models.CharField(max_length=8, choices=[('shipping', 'shipping'), ('billing', 'billing')])

需求是允许用户通过billing_country和shipping_country字段过滤客户，可填其中一个或同时填两个。当同时填写两个字段时，需要两次关联CustomerAddress表（分别关联shipping和billing类型的地址）。我已用原生SQL实现该逻辑：

SELECT *
FROM customer c
LEFT OUTER JOIN address shipping_address ON shipping_address.customer_uuid = c.uuid AND shipping_address.type = 'shipping'
LEFT OUTER JOIN address billing_address ON billing_address.customer_uuid = c.uuid AND billing_address.type = 'billing'

并根据过滤条件追加WHERE子句：

# billing_country and shipping_country are sanitized at this point
filters = []
if billing_country:
    filters.append(f"billing_address.country_code = '{billing_country}'")
if shipping_country:
    filters.append(f"shipping_address.country_code = '{shipping_country}'")
where_part = 'WHERE ' + ' AND '.join(filters)

请问如何使用Django ORM实现该需求？

方法一：使用FilteredRelation（与原生SQL逻辑完全匹配） #

Django 2.0及以上版本支持FilteredRelation，可以给关联表添加过滤条件并赋予别名，完美对应原生SQL中的两次LEFT JOIN操作：

from django.db.models import FilteredRelation, Q

# 示例用户输入，实际从请求参数获取
billing_country = "CN"
shipping_country = "US"

# 先给两种类型的地址创建关联别名
queryset = Customer.objects.annotate(
    billing_address=FilteredRelation(
        'addresses', condition=Q(addresses__type='billing')
    ),
    shipping_address=FilteredRelation(
        'addresses', condition=Q(addresses__type='shipping')
    )
)

# 组装过滤条件
filters = Q()
if billing_country:
    filters &= Q(billing_address__country_code=billing_country)
if shipping_country:
    filters &= Q(shipping_address__country_code=shipping_country)

# 应用过滤
queryset = queryset.filter(filters)

该方法生成的SQL与你编写的原生SQL几乎一致，会生成两次LEFT JOIN，分别对应billing_address和shipping_address别名，再在WHERE子句中添加对应过滤条件。

方法二：使用Q对象组合过滤 #

如果不需要显式的关联别名，可直接用Q对象组合多条件过滤，Django会自动处理多次关联：

from django.db.models import Q

billing_country = "CN"
shipping_country = "US"

filters = Q()
if billing_country:
    filters &= Q(addresses__type='billing', addresses__country_code=billing_country)
if shipping_country:
    filters &= Q(addresses__type='shipping', addresses__country_code=shipping_country)

# 加distinct避免同一客户因多个符合条件地址返回重复结果
queryset = Customer.objects.filter(filters).distinct()

方法三：使用子查询过滤（适合大数据量场景） #

如果追求性能，可使用子查询检查客户是否存在符合条件的地址，避免JOIN操作：

from django.db.models import Exists, OuterRef, Q

billing_country = "CN"
shipping_country = "US"

filters = Q()
if billing_country:
    billing_subquery = CustomerAddress.objects.filter(
        customer=OuterRef('pk'),
        type='billing',
        country_code=billing_country
    )
    filters &= Exists(billing_subquery)
if shipping_country:
    shipping_subquery = CustomerAddress.objects.filter(
        customer=OuterRef('pk'),
        type='shipping',
        country_code=shipping_country
    )
    filters &= Exists(shipping_subquery)

queryset = Customer.objects.filter(filters)

内容的提问来源于stack exchange，提问作者ზურა კუპატაძ</think_never_used_51bce0c785ca2f68081bfa7d91973934>