Django-在一对多关系中以分组方式选择嵌套集合Avg

问题描述

我有以下模型，我想要选择一个业务集合，每个业务都具有基于group by review_question_id的AVG（rate）集合。

I have the following models, and what I would like is to select a collection of businesses, each of them with a collection of AVG(rate) based on group by review_question_id.

以下是必要的模型：

class ReviewQuestion(models.Model):
    """Represents a question to be given in a business type
    """
    business_type = models.ForeignKey(BusinessType, on_delete=models.CASCADE)
    question_text = models.CharField(max_length=100)


class Business(models.Model):
    """Values for a specific business, based on a type
    will inherit questions and reviews
    """
    business_type = models.ForeignKey(BusinessType, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)


class CustomerReview(models.Model):
    """Rate value by customers on a specific quesiton
    for a specific business
    """
    business = models.ForeignKey(Business, on_delete=models.CASCADE)
    review_question = models.ForeignKey(
        ReviewQuestion, on_delete=models.CASCADE)
    review_value = models.PositiveSmallIntegerField()

我尝试接近的最近查询：

The nearest query I tried to get close on this:

items = Business.objects.filter(business_type_id=type_id).values(
        'id', 'name', 'business_type_id', 'address', 'customerreview__review_question_id').annotate(rate=Avg('customerreview__review_value'))

，它的问题是重复。它复制了整个列表，结果是平坦的，类似于使用平坦tsql编写时得到的结果。

and the problem with it is duplication. It duplicates the entire list and the result is flat, sort of what you get when you write it in flat tsql.

理想的结果如下所示：

[
    {
        "business_id": 1,
        "business_name": "something",
        "rating":[
            {
                "Question_1":{
                    "title":"ReviewQuestion__question_text",
                    "Avg":5.0
                },
                "Question_2":{
                    "title":"ReviewQuestion__question_text",
                    "Avg":5.0
                },{
                    ...
                }
            }
        ]
    }
]

任何帮助都会谢谢，因为我是python / django的新手。

Any help would be appreciated as I'm new to python/django.

推荐答案

如果使用django-rest-framework，这将很容易。
您应该使用两个序列化器。

If you use django-rest-framework it will be easy.You should use two serializers.

from rest_framework import serializers

class BussnessSerializer(serializers.ModelSerializer):
     rating = QuestionWithRatingSerializer(
                  source='reviewquestion_set', 
                  many=True
     )
     business_id = serializers.IntegerField(source='id')
     business_name = serizlizers.CharField(source='name')


     class Meta:
           model = Business
           fields = ('rating', 'business_id', 'business_name')


class QuestionWithRatingSerializer(serializers.ModelSerializer):
      title = serializers.CharField(source='question_text')
      avg = serizlizers.SerializerMethodField()

      def get_avg(self, obj):

          return obj.customerreview_set.aggregave(
                     avg=Avg('review_value')['avg'])

      class Meta:
           model = ReviewQuestion
           fields = ('title', 'avg')

这篇关于Django-在一对多关系中以分组方式选择嵌套集合Avg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！