How to annotate a queryset with a new field of information from a related model?

Question

I have a model where a Vehicle table has more Wheels tables. I am trying to display in a single field the information of the vehicle and of the first wheel in the related Wheels table. I have seen that the F function might be useful but I cannot find the correct configuration for it to work. The tables are related through another field named colour which is declared as a foreign key in the Wheels table.


    class VehicleListView(ListView):
        template_name = 'vehicle.html'
        queryset = Vehicle.objects.all()

        queryset = queryset.annotate(wheel1_name = F('Wheels__wheel_name'))

        def get_context_data(self, **kwargs):
            context = super().get_context_data(**kwargs)
            return context

I would like the line


    queryset = queryset.annotate(wheel1_name = F('Wheels__wheel_name'))

to return a list of the first wheel name for each vehicle so I can iterate through it and show it in a table.

models.py

class Vehicle(models.Model):
    vehicle_id = models.AutoField(primary_key=True)
    vehicle_name = models.CharField(max_length=100)
    color = models.CharField(max_length=100)

class Wheels(models.Model):
    wheels_id = models.AutoField(primary_key=True)
    color = models.ForeignKey(Vehicle, null=True, on_delete=models.SET_NULL,
                                  db_column='color')
    wheel_name = models.CharField(max_length=100)

You want your API to return a list that contains each `Vehicle` with the first related `Wheels` object right? — Stevy, Jul 17 '19 at 15:30
@Stevy, thanks, your solution is almost exactly what I need. I am actually using the data as {{x.wheel}} in an html template, is there an option of getting the same result without serialization that requires REST? — Andrei, Jul 18 '19 at 11:32

Stevy · Accepted Answer · 2019-07-17T17:13:05.880

So I changed your code a bit, setup the database, created 2 Vehicle objects with each 2 related Wheels objects, so 4 Wheels objects in total.

I added a function that queries for the first related Wheel object for a certain Vehicle object.

# models.py

class Vehicle(models.Model):
    vehicle_id = models.AutoField(primary_key=True)
    vehicle_name = models.CharField(max_length=100)
    color = models.CharField(max_length=100)

    # this function returns a slice of the resulting queryset which contains
    # the first related Wheels object for each Vehicle object
    def get_first_wheels(self):
        return Wheels.objects.filter(color=self).order_by('wheels_id')[:1]


class Wheels(models.Model):
    wheels_id = models.AutoField(primary_key=True)
    color = models.ForeignKey(Vehicle, null=True, on_delete=models.SET_NULL)
    wheel_name = models.CharField(max_length=100)

I used Django serializers to serialize the data. To get the first related Wheels object I used a SerializerMethodField() that calls the get_first_wheels() function in models.py like this:

# serializers.py

class WheelsSerializer(serializers.ModelSerializer):
    class Meta:
        model = Wheels
        fields = ('wheels_id', 'color', 'wheel_name')


class VehicleSerializer(serializers.ModelSerializer):
    first_wheel = serializers.SerializerMethodField(read_only=True)

    def get_first_wheel(self, model):
        qs = model.get_first_wheels()
        return WheelsSerializer(qs, many=True).data

    class Meta:
        model = Vehicle
        fields = ('vehicle_id', 'vehicle_name', 'color', 'first_wheel')

I changed your view a bit and used a ModelViewSet instead.

# views.py

class VehicleViewSet(viewsets.ModelViewSet):
    serializer_class = VehicleSerializer
    queryset = Vehicle.objects.all()

I went and used a DefaultRouter() to register the endpoint like this:

# urls.py

from rest_framework import routers
# import ViewSet here

router = routers.DefaultRouter()

router.register(r'vehicles`, views.VehicleViewSet, base_name='vehicle')

Then I ran the following commands:

manage.py makemigrations
manage.py migrate
manage.py runserver

I created 2 Vehicle objects with each 2 related Wheels objects.

When I go to http://127.0.0.1:8000/vehicles/ in my browser it returns all the Vehicle objects with their first related Wheels object like this:

[
    {
        "vehicle_id": 1,
        "vehicle_name": "BMW",
        "color": "Blue",
        "first_wheel": [
            {
                "wheels_id": 1,
                "color": 1,
                "wheel_name": "BMW1"
            }
        ]
    },
    {
        "vehicle_id": 2,
        "vehicle_name": "Ferrari",
        "color": "Red",
        "first_wheel": [
            {
                "wheels_id": 3,
                "color": 2,
                "wheel_name": "Ferrari1"
            }
        ]
    }
]

How to annotate a queryset with a new field of information from a related model?

1 Answers1