Serialize a M-2-M relation with grouping in Django REST framework

I have two models, linked through a separate model as follow:

class Project(models.Model):
    name = models.CharField(max_length=50, unique=True)

class Employee(models.Model):
    name = models.CharField(max_length=50, unique=True)
    project = models.ManyToManyField(
        Project,
        through='Schedule'
    )

    def __str__(self):
        return self.name


class Schedule(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.PROTECT)
    project = models.ForeignKey(Project, on_delete=models.PROTECT)
    date = models.DateField(null=False)
    hours = models.IntegerField()

Serialized is pretty straight forward:

class EmployeeScheduleSerializer(serializers.ModelSerializer):
    month = serializers.DateField()
    booked = serializers.IntegerField()

    class Meta:
        model = Employee
        fields = ['id', 'name', 'month', 'booked']

A viewset returns the total number of houses assigned to each employee for different projects, summed over dates:

class ScheduleViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Employee.objects.all()
    serializer_class = EmployeeScheduleSerializer

    lookup_field = 'employee'

    def get_queryset(self):
        qs = Employee.objects.values(
            'name', month=F('schedule__date')
        ).annotate(
            booked=Sum('schedule__hours', distinct=True)
        )

        if self.request.query_params.get('id') is not None:
            qs = qs.filter(id=self.request.query_params.get('id'))
        return qs

    def retrieve(self, request, *args, **kwargs):
        serializer = self.get_serializer(self.get_queryset(), many=True)
        return Response(data=serializer.data)

In the output we have multiple records for each employee:

[
    {
        "name": "John Doe",
        "month": "10/01/2020",
        "booked": 100
    },
    {
        "name": "John Doe",
        "month": "11/01/2020",
        "booked": 120
    },
...
]

I want to convert it to a nested list so that the booked hours show up as a list for each employee. I checked multiple threads, but couldn’t find a reasonable solution. I think it’s possible to convert it to a grouped dataframe and output as JSON, but it wouldn’t be part of the Django Rest framework.

As far as I know, Django ORM doesn’t support nested queryset. Is there a workaround for that?

Answer

You can create a EmployeeSerializer with a nested serializer with Schedule.

First, to improve reading, you should add a related name to schedule

class Schedule(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.PROTECT, related_name="booked_hours")
    project = models.ForeignKey(Project, on_delete=models.PROTECT)
    date = models.DateField(null=False)
    hours = models.IntegerField()

To have the booked hours, you can update the model :

class Employee(models.Model):
    name = models.CharField(max_length=50, unique=True)
    project = models.ManyToManyField(
        Project,
        through='Schedule'
    )

    def __str__(self):
        return self.name

    @property
    def booked_hours_count:
        return [ dict(date=date, count=self.booked_hours.filter(date=date).count()) for date in set(self.booked_hours.values_list('date', flat=True))]

and then in the serializer

class ScheduleSerializer(serializers.ModelSerializer):

    class Meta:
        model = Schedule
        fields = ['id', 'project', 'date', 'hours']

class EmployeeScheduleSerializer(serializers.ModelSerializer):
    booked_hours = ScheduleSerializer(many=True)

    class Meta:
        model = Employee
        fields = ['id', 'name', 'booked_hours', 'booked_hours_count']

Thanks to that, you will have :

[
    {
        "name": "John Doe",
        "booked_hours": [
            {
               "project":...,
               "hours"....,
               "date"...
            },
        ],
...
]

To optimize the queryset, you should not forget to use prefect_related. The viewset will be :

class ScheduleViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Employee.objects.all()
    serializer_class = EmployeeScheduleSerializer

    def get_queryset(self):
        queryset = Employee.objects.all()
        queryset = queryset.prefetch_related('booked_hours')
        return queryset

ReadOnlyModelViewSetis doing the rest of the job here.