如何将用户资产统计SQL查询转换为Django ORM语句？

问题背景 #

我有一个包含4个模型的Django项目，定义如下：

stock/models.py #

class StockIndex(models.Model):
  name = models.CharField(max_length=128)
  
  def __str__(self):
    return self.name

class Stock(models.Model):
  code = models.CharField(max_length=24)
  name = models.CharField(max_length=128)
  price = models.DecimalField(max_digits=10, decimal_places=2)
  dividend_rate = models.DecimalField(decimal_places=2, max_digits=4)
  index = models.ForeignKey(StockIndex, related_name='stocks', on_delete=models.SET_NULL, null=True)
  
  def __str__(self):
    return self.name

class StockSet(models.Model):
  code = models.ForeignKey(Stock, related_name='stock_set', on_delete=models.CASCADE)
  quantity = models.IntegerField()
  owner = models.ForeignKey(User, on_delete=models.CASCADE, related_name='stock_set', to_field='wallet')
  
  def __str__(self):
    return f'{self.owner} - {self.code}'

users/models.py #

class User(AbstractBaseUser, PermissionsMixin):
  
  objects = UserManager()
  
  username = models.CharField(
      max_length=150,
      unique=True,
  ) 
  email = models.EmailField(blank=True)
  is_staff = models.BooleanField(
    default=False,
  )
  is_active = models.BooleanField(
    default=True,
  )
  wallet = models.UUIDField(unique=True, default=uuid.uuid4, editable=False)
  
  EMAIL_FIELD = "email"
  USERNAME_FIELD = "username"
  REQUIRED_FIELDS = ["email"]
  
  def __str__(self):
    return self.username

需要统计每个用户的资产，计算规则为：将每个StockSet对象的quantity字段与对应Stock的price字段相乘后求和。对应的SQL查询语句已写出：

SELECT u.username, u.email, SUM(st.quantity * s.price) AS user_asset 
FROM stock_stockset st JOIN users_user u ON (st.owner_id = u.wallet) 
JOIN stock_stock s ON (st.code_id = s.id) 
GROUP BY u.username;

现在需要把该SQL转换为Django ORM语句，填充到UserView的queryset中：

class UserView(ListAPIView):
  queryset = ?
  serializer_class = UserSerializer

解决方案 #

用Django ORM实现统计 #

推荐通过get_queryset方法动态生成查询集，结合annotate、F表达式和聚合函数Sum实现需求：

from django.db.models import Sum, F
from users.models import User
from rest_framework.generics import ListAPIView

class UserView(ListAPIView):
    serializer_class = UserSerializer
    
    def get_queryset(self):
        return User.objects.annotate(
            user_asset=Sum(F('stock_set__quantity') * F('stock_set__code__price'))
        ).filter(stock_set__isnull=False).distinct()

代码说明：

• F表达式：直接引用数据库层面的字段值，在数据库内完成quantity和price的乘积计算，比拉取数据到内存计算更高效。
• Sum聚合：对每个用户的所有持仓乘积结果求和，得到总资产品值user_asset。
• 过滤与去重：filter(stock_set__isnull=False)过滤无任何股票持仓的用户（可按需移除）；distinct()避免关联查询导致的用户记录重复。

配套更新序列化器 #

要让API响应返回user_asset字段，需修改UserSerializer：

from rest_framework import serializers
from users.models import User

class UserSerializer(serializers.ModelSerializer):
    user_asset = serializers.DecimalField(max_digits=12, decimal_places=2, read_only=True)
    
    class Meta:
        model = User
        fields = ['username', 'email', 'user_asset']

备选：直接执行原生SQL #

如果ORM写法不符合预期，也可以用raw()方法执行你已编写的SQL语句：

class UserView(ListAPIView):
    serializer_class = UserSerializer
    
    def get_queryset(self):
        return User.objects.raw("""
            SELECT u.username, u.email, SUM(st.quantity * s.price) AS user_asset 
            FROM stock_stockset st JOIN users_user u ON (st.owner_id = u.wallet) 
            JOIN stock_stock s ON (st.code_id = s.id) 
            GROUP BY u.username;
        """)

使用原生SQL时，序列化器同样需要添加user_asset字段，确保字段名与SQL返回结果一致。

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