How can I serialize list with an object relation produced from queryset TruncYear in Django 1.11

Question

I manage to get time series data with TruncYear/TruncMonth/TruncDay/etc like below from Tracking table. However the data for the venue just produce the venue_id. I would like to have that serialized so that it returns the "name" from the relation Venue table.

I am using Django 1.11 a postgres 9.4

Here is my time series code:

tracking_in_timeseries_data = Tracking.objects.annotate(
                                          year=TruncYear('created_at')).values('year', 'venue_id').annotate(
                                            count=Count('employee_id',
                                                        distinct = True)).order_by('year')   


return Response(tracking_in_timeseries_data, status=status.HTTP_200_OK)    

currently it output like this:

[
    {
      "venue_id": 4,
      "year": "2017-01-01T00:00:00Z",
      "count": 1
    },
    {
      "venue_id": 2,
      "year": "2018-01-01T00:00:00Z",
      "count": 2
    },
    {
      "venue_id": 6,
      "year": "2019-01-01T00:00:00Z",
      "count": 1
    }
  ]

I want to explode venue data to return the id & name like this:

  [
    {
      "venue": {
                id: 4,
                name: "room A"
                },
      "year": "2017-01-01T00:00:00Z",
      "count": 1
    },
    {
      "venue": {
                id: 2,
                name: "room B"
                },
      "year": "2018-01-01T00:00:00Z",
      "count": 2
    },
    {
      "venue": {
                id: 6,
                name: "room C"
                },
      "year": "2019-01-01T00:00:00Z",
      "count": 1
    }
  ]

How to explode the "venue" to return the id and name ? The name is useful for presentation purpose.

UPDATE (here are some attempts that failed):

this only displays count but accumulative ( https://gist.github.com/axilaris/0cd86cec0edf675d654eadb3aff5b066). something weird and not sure why.

class TimeseriesSerializer(serializers.ModelSerializer):

    venue = VenueNameSerializer(source="venue_id",many=False, read_only=True)
    year = serializers.TimeField(read_only=True)
    count = serializers.IntegerField(read_only=True)

    class Meta:
        model = Tracking
        fields = ("venue",
                  "year",
                  "count")


class TimeseriesSerializer(serializers.Serializer): <-- here is another try but doesnt work serializers.Serializer

    venue_id = VenueNameSerializer(many=False, read_only=True)
    year = serializers.TimeField(read_only=True)
    count = serializers.IntegerField(read_only=True)

I think the answer is quite close to this: django rest framework serialize a dictionary without create a model

FYI, this is my actual code (must as well put it here) for the test, names may differ slightly but the whole intention is the same. https://gist.github.com/axilaris/919d1a20d3e799101a8cf6aeb4d120b5

Answer 1

What you need is to create a serializer for the Venue which displays the id and name fields and use it as a field in the TrackingSerializer.

In your case there is something more to consider: since you are using values to group, what you get from the queryset is not a Tracking object, thus your venue_id can't be translated to a Venue object by DRF.

To fix this you need to override the to_representation method of VenueSerializer to get the Venue object from its primary key.

I'm including models and views to give you a working example, but you probably only need serializers and the adjusted queryset from the view.

Models

class Employee(models.Model):
    first_name = models.CharField(max_length=128)
    last_name = models.CharField(max_length=128)


class Venue(models.Model):
    name = models.CharField(max_length=128)


class Tracking(models.Model):
    venue = models.ForeignKey(Venue)
    employee = models.ForeignKey(Employee)
    created_at = models.DateTimeField(auto_now_add=True)

Views

class TrackingViewSet(viewsets.ModelViewSet):
    queryset = (
        Tracking.objects
        .annotate(
            year=ExtractYear('created_at'),
        )
        .values('year', 'venue')
        .annotate(
            count=Count('employee_id', distinct=True),
        )
        .order_by('year')
    )
    serializer_class = TrackingSerializer

Serializers

class VenueSerializer(serializers.ModelSerializer):
    class Meta:
        model = Venue
        fields = ['id', 'name']

    def to_representation(self, value):
        value = Venue.objects.get(pk=value)
        return super(VenueSerializer, self).to_representation(value)


class TrackingSerializer(serializers.ModelSerializer):
    venue = VenueSerializer(read_only=True)
    year = serializers.IntegerField(read_only=True)
    count = serializers.IntegerField(read_only=True)

    class Meta:
        model = Tracking
        fields = ['venue', 'year', 'count']

Note that I replaced your TruncYear with ExtractYear, the difference between the two is that the former returns a datetime, the latter an int which is what you want I guess.

If you prefer to use TruncYear you will have to replace:

year = serializers.IntegerField(read_only=True)

with:

year = serializers.DateTimeField(read_only=True)

in TrackingSerializer.