如何左连接两张表：保留左表全量数据匹配指定用户右表数据

表说明 #

• Table 1（SubscriptionsPlans）：存储所有订阅方案的基础信息，包含id、name、plan_type、plan_details、request_per_month、price、is_deleted字段
• Table 2（SubscriptionsOrder）：存储用户的订阅订单信息，包含id、user_id、plan_selected、billing_info字段，其中plan_selected关联SubscriptionsPlans的id，代表用户选择的方案。例如user_id=4的用户选择了Table1中id=2的方案。

需求 #

获取SubscriptionsPlans的所有数据，同时匹配SubscriptionsOrder中特定user_id（比如user_id=4）的关联数据，未匹配到的plan_selected字段显示为NULL，预期结果如下：

尝试的SQL #

使用左连接查询但未得到预期结果：

select plans.id, name, plan_details, plan_type, request_per_month, price,is_deleted, plan_selected from SubscriptionsPlans as plans left join SubscriptionsOrder as orders on plans.id=orders.plan_selected where orders.user_id = 4

模型定义 #

以下是Django模型，需要给出正确的SQL查询语句或ORM查询集：

class SubscriptionsPlans(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=255)
    plan_type = models.CharField(max_length=255)
    plan_details = models.TextField(max_length=1000)
    request_per_month = models.IntegerField()
    price = models.FloatField()
    is_deleted = models.BooleanField(default=False)

class SubscriptionsOrder(models.Model):
    id = models.IntegerField(primary_key=True)
    user_id = models.ForeignKey(
        AppUser,
        null=True,
        on_delete=models.SET_NULL
    )
    plan_selected = models.ForeignKey(SubscriptionsPlans, null=True, on_delete=models.SET_NULL)
    billing_info = models.IntegerField()

问题原因 #

你写的左连接后在WHERE条件里过滤orders.user_id =4，会把左连接中未匹配到的记录（即没有对应订单的方案）过滤掉——因为这些记录的orders.user_id是NULL，不满足=4的条件，所以无法保留所有方案数据。

正确的SQL语句 #

需要把user_id的过滤条件放到ON子句中，这样左连接时只会匹配该用户的订单，未匹配的方案仍会保留：

SELECT 
    plans.id, 
    plans.name, 
    plans.plan_type, 
    plans.plan_details, 
    plans.request_per_month, 
    plans.price, 
    plans.is_deleted, 
    orders.plan_selected
FROM SubscriptionsPlans AS plans
LEFT JOIN SubscriptionsOrder AS orders 
    ON plans.id = orders.plan_selected 
    AND orders.user_id = 4;

Django ORM 查询集 #

方法1：直接通过annotate生成字段

from django.db.models import Case, When, Value, IntegerField, F

target_user_id = 4
plans = SubscriptionsPlans.objects.annotate(
    plan_selected=Case(
        When(
            subscriptionsorder__user_id=target_user_id,
            then=F('subscriptionsorder__plan_selected')
        ),
        default=Value(None),
        output_field=IntegerField()
    )
).all()

方法2：通过Prefetch预加载指定用户的订单

如果需要后续处理订单的其他信息，可以用这种方式：

from django.db.models import Prefetch

target_user_id = 4
user_orders = SubscriptionsOrder.objects.filter(user_id=target_user_id)
plans = SubscriptionsPlans.objects.prefetch_related(
    Prefetch('subscriptionsorder_set', queryset=user_orders, to_attr='user_selected_plan')
).all()

# 获取plan_selected值示例
for plan in plans:
    plan_selected = plan.user_selected_plan[0].plan_selected.id if plan.user_selected_plan else None

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