如何用Django ORM实现两个QuerySet的LEFT JOIN操作？

我已在SO和Google上搜索两天，仍未找到针对特定问题的解决方案。我正尝试将一段SQL查询转换为Django ORM（若可行），该SQL查询如下：

SELECT *
FROM(
SELECT
    max(effective_date) AS effective_date,
    floorplan_id
FROM
    floorplan_counts
GROUP BY
    floorplan_id) s1
LEFT JOIN (
        SELECT
            floorplan_id AS fc_floorplan_id,
            units AS fc_units,
            beds AS fc_beds,
            expirations AS fc_expirations,
            effective_date
        FROM
            floorplan_counts) s2 ON s1.floorplan_id = s2.fc_floorplan_id

我已能用Django ORM生成子查询s1和s2：

s1 = FloorplanCount.objects.values('floorplan_id').annotate(effective_date=Max('effective_date'))
s2 = FloorplanCount.objects.values('effective_date').annotate(
            fc_floorplan_id=F('floorplan_id'), fc_units=F('units'), fc_beds=F('beds'), fc_expirations=F('expirations'))

目前我使用Django的.raw()方法实现，但希望改用Django ORM，且不想使用.extra()方法（因文档说明该方法将被弃用）。请问如何将s1左连接到s2？

这里提供两种无需.raw()或.extra()的Django ORM实现方案，完全贴合你的需求：

方案一：直接筛选最新记录（性能更优） #

这个方案等价于原SQL的逻辑，但通过直接筛选避免了显式左连接，性能更好：

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

# 定义子查询：获取每个floorplan_id对应的最新effective_date
latest_date_subquery = FloorplanCount.objects.filter(
    floorplan_id=OuterRef('floorplan_id')
).values('floorplan_id').annotate(
    max_date=Max('effective_date')
).values('max_date')

# 直接查询每个floorplan_id的最新日期记录
result = FloorplanCount.objects.annotate(
    latest_effective_date=Subquery(latest_date_subquery)
).filter(
    effective_date=F('latest_effective_date')
).values(
    'floorplan_id', 'effective_date', 'units', 'beds', 'expirations'
)

方案二：严格对应原SQL的左连接结构 #

如果你需要完全匹配原SQL的左连接写法，可以通过Subquery关联两个子查询：

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

# 先构建s1：每个floorplan_id及其最大effective_date
s1 = FloorplanCount.objects.values('floorplan_id').annotate(
    effective_date=Max('effective_date')
)

# 定义s2的子查询，关联s1的floorplan_id和effective_date
s2_subquery = FloorplanCount.objects.filter(
    floorplan_id=OuterRef('floorplan_id'),
    effective_date=OuterRef('effective_date')
).values(
    fc_units=F('units'),
    fc_beds=F('beds'),
    fc_expirations=F('expirations')
)

# 对s1进行左连接，获取s2的字段
result = s1.annotate(
    fc_units=Subquery(s2_subquery.values('fc_units')[:1]),
    fc_beds=Subquery(s2_subquery.values('fc_beds')[:1]),
    fc_expirations=Subquery(s2_subquery.values('fc_expirations')[:1])
)

验证方式 #

你可以通过执行print(result.query)来查看生成的SQL语句，确认是否和原SQL逻辑一致。

内容的提问来源于stack exchange，提问作者d-dev