how to find sum from foreign key table along with some filtering in django orm?-CodePudding

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

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

what is the ORM of this query?

CodePudding user response：

If I understand the SQL correctly: We are trying to get the total price of all Submitted Quizes

I believe you'd use aggregate and do something like this:

from django.db.models import Sum

aggrDict = UserAndQuiz.objects.filter(is_submit=True).aggregate(total_price=Sum('quiz__price'))
print(aggrDict) # {'total_price': float}