I am working in DRF and trying to output nested serialized data from a single model. I am already using annotate() and getting the correct values for the data but I would like to go a step further and nest data if it is possible.
I have a model looking like this:
class Paragraph(models.Model):
uuid = models.UUIDField()
date = models.DateTimeField(null=True)
author = models.CharField(max_length=200, null=True)
paragraph = models.TextField(null=False, blank=False)
value = models.CharField(max_length=20, null=True)
score = models.DecimalField(max_digits=8, decimal_places=4, null=True)
final_score = models.DecimalField(max_digits=8, decimal_places=4, null=True)
and my serializer looks like this:
class ParagraphGroupByAuthorDateListGeneric(serializers.ModelSerializer):
date = serializers.DateTimeField()
total = serializers.DecimalField(max_digits=8, decimal_places=4)
sum_positive = serializers.DecimalField(max_digits=8, decimal_places=4)
count_positive = serializers.IntegerField(min_value=0)
sum_negative = serializers.DecimalField(max_digits=8, decimal_places=4)
count_negative = serializers.IntegerField(min_value=0)
count_neutral = serializers.IntegerField(min_value=0)
average = serializers.DecimalField(max_digits=8, decimal_places=4)
count = serializers.IntegerField(min_value=0)
class Meta:
model = Paragraph
fields = ['date', 'total', 'average', 'count', 'author',
'sum_positive', 'sum_negative',
'count_positive', 'count_negative', 'count_neutral'
]
My view looks like this:
class ParagraphGroupByAuthorDateListGeneric(ListAPIView):
queryset = Paragraph.objects.all()
pagination_class = MyLimitOffsetPagination
serializer_class = ParagraphGroupByAuthorDateListGeneric
filter_backends = [DjangoFilterBackend, filters.OrderingFilter]
filterset_fields = ['uuid', 'date', 'author']
ordering_fields = ['total', 'average', 'sum_positive', 'sum_negative']
def get_queryset(self):
return Paragraph.objects.values('author', 'date').annotate(
total=Sum('final_score'),
average=Avg('final_score'),
count=Count('final_score'),
sum_positive=Sum('final_score', filter=Q(value='positive')),
count_positive=Count('final_score', filter=Q(value='positive')),
sum_negative=Sum('final_score', filter=Q(value='negative')),
count_negative=Count('final_score', filter=Q(value='negative')),
count_neutral=Count('final_score', filter=Q(value='negative'))
).order_by('author', 'date')
and I' am getting this result.
"results": [
{
"date": "2022-09-29T00:00:00Z",
"total": "0.9637",
"average": "0.1377",
"count": 7,
"author": "Peter",
"sum_positive": "0.9637",
"sum_negative": null,
"count_positive": 1,
"count_negative": 0,
"count_neutral": 0
},
{
"date": "2022-10-03T00:00:00Z",
"total": "0.9307",
"average": "0.1034",
"count": 9,
"author": "Peter",
"sum_positive": "1.9287",
"sum_negative": "-0.9980",
"count_positive": 2,
"count_negative": 1,
"count_neutral": 1
},
{
"date": "2022-10-06T00:00:00Z",
"total": "2.9870",
"average": "0.3734",
"count": 8,
"author": "Ana",
"sum_positive": "2.9870",
"sum_negative": null,
"count_positive": 3,
"count_negative": 0,
"count_neutral": 0
},
{
"date": "2022-10-11T00:00:00Z",
"total": "0.7079",
"average": "0.0787",
"count": 9,
"author": "Ana",
"sum_positive": "0.7079",
"sum_negative": null,
"count_positive": 1,
"count_negative": 0,
"count_neutral": 0
},
but I would like to somehow get this or similar to this, grouped by author:
"results": [
"Peter": [
{
"date": "2022-09-29T00:00:00Z",
"total": "0.9637",
"average": "0.1377",
"count": 7,
"sum_positive": "0.9637",
"sum_negative": null,
"count_positive": 1,
"count_negative": 0,
"count_neutral": 0
},
{
"date": "2022-10-03T00:00:00Z",
"total": "0.9307",
"average": "0.1034",
"count": 9,
"sum_positive": "1.9287",
"sum_negative": "-0.9980",
"count_positive": 2,
"count_negative": 1,
"count_neutral": 1
},
],
"Ana": [
{
"date": "2022-10-06T00:00:00Z",
"total": "2.9870",
"average": "0.3734",
"count": 8,
"sum_positive": "2.9870",
"sum_negative": null,
"count_positive": 3,
"count_negative": 0,
"count_neutral": 0
},
{
"date": "2022-10-11T00:00:00Z",
"total": "0.7079",
"average": "0.0787",
"count": 9,
"sum_positive": "0.7079",
"sum_negative": null,
"count_positive": 1,
"count_negative": 0,
"count_neutral": 0
}
]
]
What would be the best way to approach the problem?