Django / DRF: annotate a model with extra (computed) information

Question

I'm adding a very simple roadmap voting feature to my website, where people can add feature requests, and people can then vote on each other's suggestions. The basics are pretty simple:

# models.py
class FeatureRequest(models.Model):
    title = models.CharField(max_length=50)
    description = models.TextField()
    author = models.ForeignKey(User, editable=False, on_delete=models.CASCADE)
    is_implemented = models.BooleanField(default=False, editable=False, db_index=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)


class Vote(models.Model):
    feature = models.ForeignKey(FeatureRequest, editable=False, on_delete=models.CASCADE)
    user = models.ForeignKey(User, editable=False, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        unique_together = ["feature", "user"]

#views.py
class RoadmapController(viewsets.ModelViewSet):
    permission_classes = (AuthorOrReadOnlyPermission,)
    serializer_class = FeatureRequestSerializer

    def get_queryset(self):
        return FeatureRequest.objects.filter(is_implemented=False)

    def perform_create(self, serializer):
        return serializer.save(author=self.request.user)

# serializers.py
class FeatureRequestSerializer(serializers.ModelSerializer):
    class Meta:
        model = FeatureRequest
        fields = "__all__"

There is also a separate view for actually creating and deleting votes, but that's not needed here for my question.

What I want is that the response of the feature request list includes the number of votes for each one, and a boolean if the logged-in user has voted for it:

[
  {
    "title": "This is a feature request", 
    "description": "Foo bar", 
    "author": 1, 
    "number_of_votes": 1, 
    "has_my_vote": true
  }
]

I already figured out that I can change my queryset to FeatureRequest.objects.filter(is_implemented=False).annotate(number_of_votes=Count("vote")), add number_of_votes = serializers.ReadOnlyField() to the serializer, and the number of votes is visible. Is that the best way? It adds a query for each feature request I would assume.

But most of all, I don't know how to add that "has_my_vote" boolean to the result (and how to keep that performant, of course).

It is possible to set [calculated fields](https://books.agiliq.com/projects/django-admin-cookbook/en/latest/calculated_fields.html) inside Model (or another [example](https://stackoverflow.com/a/48750089/13946204)) — rzlvmp, Apr 18 '22 at 00:19
But that would add an extra query for each feature request, right? That doesn't seem like the best solution — Kevin Renskers, Apr 18 '22 at 08:20
1. sometimes two simple queries faster than one complicated. 2. calculated fields are easy to read and allow to understand code faster (in my opinion). Also for example you may create child `class DetailedFeatureRequest(FeatureRequest)` and add calculated field inside it to be able get these values only when it necessary. Anyway best solution depends on situation. — rzlvmp, Apr 18 '22 at 09:28
This isn't about 2 queries vs 1, but potentially hundreds vs 1. — Kevin Renskers, Apr 18 '22 at 14:10

score 0 · Accepted Answer · answered Apr 18 '22 at 08:22

0

After some trial and error, I came up with this:

    def get_queryset(self):
        return (
            FeatureRequest.objects.filter(is_implemented=False)
            .annotate(number_of_votes=Count("vote"))
            .annotate(has_my_vote=Exists(Vote.objects.filter(feature=OuterRef("pk"), user=self.request.user)))
        )

That works, and it only does one query instead of one plus one or two per feature request.

answered Apr 18 '22 at 08:22

Kevin Renskers

5,156
4
47
95

a little clarification... `Vote.objects.filter(feature=OuterRef("pk"), user=self.request.user)` is a second query ;) – rzlvmp Apr 18 '22 at 09:40
It's a subquery, sure. My point is that only one query is sent to the database, instead of one query PLUS one query for every single feature request in the database. So let's say there's 100 of those in the database, that would result in 101 queries. My solution doesn't. – Kevin Renskers Apr 18 '22 at 14:09

Django / DRF: annotate a model with extra (computed) information

1 Answers1