如何在Django ORM中无外键实现LEFT OUTER JOIN查询

现有模型代码 #

class User(Model):
    ### 
    user fields
    ###

class CaseModel(Model):
    id = # django build in id field
    owner = models.ForeignKey('User')
    ### 
    other fields
    ###


class DraftMessageModel(Model):
    entity_id = models.IntegerField(null=True, blank=True) # CaseModel.id is linked to this
    ### 
    other fields
    ###

目标SQL #

SELECT 
  C.id, 
  C.some_field, 
  D.another_field, 
  U.username,
  < some extra fields > 
FROM 
  CaseModel AS C 
  LEFT OUTER JOIN User AS U ON (C.owner_id = U.id) 
  LEFT OUTER JOIN DraftMessageModel AS D ON (C.id = D.entity_id) 
WHERE 
  C.owner_id = 100 
  AND < some extra condition >

已尝试的方法及问题 #

方法1：annotate + FilteredRelation 报错 #

代码：

queryset = CaseModel.objects.select_related('owner').filter(owner_id=100)

queryset = queryset.annotate(
    draft_message=FilteredRelation(
        'draftmessagemodel',
        condition=Q(draftmessagemodel__entity_id=F('id'))
    )
)

错误提示：字段解析错误，无法将'draftmessagemodel'解析为字段

方法2：extra 生成SQL不符合预期 #

代码：

queryset = CaseModel.objects.select_related('owner').filter(owner_id=100)

queryset = queryset.extra(
    select={
        'draftmessagemodel__id': 'draftmessagemodel.id',
        'draftmessagemodel__text': 'draftmessagemodel.text',
    },
    tables=['draftmessagemodel'],
    where=[
        'casemodel.id = draftmessagemodel.entity_id'
    ]
)

问题：表关联处出现多余逗号，且未实现LEFT OUTER JOIN（实际生成INNER JOIN）

解决方案 #

方案1：修改模型添加外键关联（推荐） #

如果能修改DraftMessageModel，把entity_id改成关联CaseModel的外键，ORM就能自动处理关联逻辑：

class DraftMessageModel(Model):
    # 替换原有的entity_id字段
    entity = models.ForeignKey(
        'CaseModel', 
        on_delete=models.SET_NULL, 
        null=True, 
        blank=True, 
        related_name='draft_messages'
    )
    ### 
    other fields
    ###

之后直接用ORM的关联查询即可生成目标LEFT JOIN：

from django.db.models import F

# 构造QuerySet，指定需要的字段
queryset = CaseModel.objects.select_related('owner').filter(owner_id=100).annotate(
    draft_another_field=F('draft_messages__another_field')
).values(
    'id', 'some_field', 'draft_another_field', 'owner__username'
)

方案2：不修改模型，用Subquery实现关联查询 #

如果无法修改模型，用子查询关联DraftMessageModel的对应字段：

from django.db.models import Subquery, OuterRef

# 定义子查询，匹配当前CaseModel的id
draft_subquery = DraftMessageModel.objects.filter(
    entity_id=OuterRef('id')
).values('another_field')[:1]

# 主查询中注入子查询结果
queryset = CaseModel.objects.select_related('owner').filter(owner_id=100).annotate(
    draft_another_field=Subquery(draft_subquery)
).values(
    'id', 'some_field', 'draft_another_field', 'owner__username'
)

这种方式会生成类似LEFT JOIN的效果，保证即使没有对应Draft记录，CaseModel数据也会被保留。

方案3：用RawSQL手动构造左连接逻辑 #

如果需要更精细的SQL控制，可以直接用RawSQL指定关联逻辑：

from django.db.models import RawSQL, F

queryset = CaseModel.objects.select_related('owner').filter(owner_id=100).annotate(
    draft_another_field=RawSQL(
        'SELECT D.another_field FROM draftmessagemodel D WHERE D.entity_id = %s',
        (F('id'),)
    )
)

错误原因说明 #

• 方法1失败：FilteredRelation依赖ORM层面的模型关联（比如外键的反向关系），但当前CaseModel和DraftMessageModel没有定义任何关联，所以'draftmessagemodel'这个关系名不存在，导致解析错误。
• 方法2失败：extra的tables参数默认用INNER JOIN添加表，加上手动指定的where条件，最终生成的是INNER JOIN而非LEFT JOIN，不符合需求。

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