如何在Django ORM中实现关联外键表的求和与过滤查询？

模型定义 #

class UserAndQuiz(models.Model):
    user = models.ForeignKey(User,on_delete=models.CASCADE)
    quiz = models.ForeignKey(QuizInfo,on_delete=models.CASCADE)
    created_at = models.DateTimeField(null=True,blank=True)
    is_submit = models.BooleanField(default=False)
    result = models.IntegerField(default=0)

class QuizInfo(models.Model):
    quiz_name = models.CharField(unique=True,max_length=255)
    number_of_question = models.IntegerField()
    number_of_option = models.IntegerField()
    quiz_type = models.CharField(max_length=255,choices=quiz_status)
    price = models.FloatField(default=0.0,null=True,blank=True)
    image = models.ImageField(upload_to='pics')
    description = models.TextField()
    def __str__(self) -> str:
        return self.quiz_name

目标SQL查询 #

SELECT sum(price) from quizsession_quizinfo as q JOIN user_userandquiz as p on q.id = p.quiz_id where p.is_submit=1;

对应的Django ORM语句 #

有两种等价的实现方式：

方式一：从UserAndQuiz表出发关联查询 #

from django.db.models import Sum

total_price = UserAndQuiz.objects.filter(is_submit=True).aggregate(total=Sum('quiz__price'))['total'] or 0.0

方式二：从QuizInfo表出发反向关联查询 #

from django.db.models import Sum

total_price = QuizInfo.objects.filter(userandquiz__is_submit=True).aggregate(total=Sum('price'))['total'] or 0.0

说明 #

• 两种写法都会自动处理表关联逻辑，生成与目标SQL等价的查询语句
• aggregate()方法用于执行聚合计算，返回包含结果的字典，通过['total']提取求和值
• 添加or 0.0是为了避免无符合条件记录时返回None，保证结果为浮点类型

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