将MySQL多表查询转换为Django ORM查询求助

需求 #

获取Trip ID为4的情况下，各账户的Wallet表总收入、Expense表总支出，以及收支相减后的剩余金额。

Django模型定义 #

class Trip(models.Model):
    name = models.CharField('Name', max_length=254)

class Account(models.Model):
    name = models.CharField('Name', max_length=254)    

class Wallet(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    incoming_date = models.DateField(verbose_name='Incoming Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

class Expense(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    outcome_date = models.DateField(verbose_name='Outcome Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

原始MySQL查询 #

SELECT *, (wallet_total - expense_total) AS remain_total
FROM (
    SELECT account.name, SUM(wallet.total) AS wallet_total 
    FROM account
    INNER JOIN wallet
    ON wallet.account_id = account.id
    WHERE wallet.trip_id=4
    GROUP BY account.name
) AS wallet,
(
    SELECT account.name, SUM(expense.total) AS expense_total 
    FROM account
    INNER JOIN expense
    ON expense.account_id = account.id
    WHERE expense.trip_id=4
    GROUP BY account.name
) AS expense;

Django ORM实现方案 #

方案：使用子查询与注解（推荐） #

通过子查询分别统计每个账户的收入和支出，再通过注解计算收支差额。同时用Coalesce处理无收入/支出的情况，避免None值运算错误。

from django.db.models import Sum, F, Subquery, OuterRef, Coalesce
from .models import Account, Wallet, Expense

# 子查询：获取每个账户在Trip 4下的总收入
wallet_subquery = Wallet.objects.filter(
    account=OuterRef('pk'),
    trip_id=4
).values('account').annotate(
    total_in=Sum('total')
).values('total_in')

# 子查询：获取每个账户在Trip 4下的总支出
expense_subquery = Expense.objects.filter(
    account=OuterRef('pk'),
    trip_id=4
).values('account').annotate(
    total_out=Sum('total')
).values('total_out')

# 构建最终查询集，包含账户名、总收入、总支出、剩余金额
account_stats = Account.objects.annotate(
    wallet_total=Coalesce(Subquery(wallet_subquery), 0),
    expense_total=Coalesce(Subquery(expense_subquery), 0)
).annotate(
    remain_total=F('wallet_total') - F('expense_total')
).values('name', 'wallet_total', 'expense_total', 'remain_total')

# 遍历查看结果
for stat in account_stats:
    print(f"账户：{stat['name']}，总收入：{stat['wallet_total']}，总支出：{stat['expense_total']}，剩余：{stat['remain_total']}")

说明 #

• Subquery用于嵌套查询，针对每个账户单独统计收支数据
• Coalesce将无收支记录产生的None值替换为0，确保差额计算不会抛出异常
• values()指定返回字段，输出结构与原始SQL保持一致

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