Ordering a queryset by the count of a field

Using Django Forms & APIs
1

For context, I have a JobPost model that has information about job posts:

class JobPost(models.Model):
    title = models.CharField(max_length=100, default='job_title')
    min_salary_range = models.PositiveIntegerField(help_text='Format example: enter 50 for $50K', default = 12)
    max_salary_range = models.PositiveIntegerField(help_text='Format example: enter 50 for $50K', default = 24)
    description = models.TextField(blank=True, null=True)
    date_posted = models.DateField(default = date.today()

I want to write an api view that displays all the job posts and the count of each job post. For example: ‘Web development’: 31, ‘Data science’: 27, etc.
And I want to order the job posts by the count of their postings.

This is the code that groups the jobposts by their title and orders them by their count:

JobPost.objects.values('title').annotate(number_of_jobs=Count('title')).order_by('number_of_jobs')

This code gives the correct result in the ORM, however, it is not working in my api view.
This is my api view class:

class JobPostsCount(generics.ListAPIView):
    queryset = JobPost.objects.values('title').annotate(number_of_jobs=Count('title')).order_by('number_of_jobs')
    serializer_class = JobPostsCountSerializer
  # add the number of jobs as extra context to the serializer, because number of jobs is not a model field and the serializer does not have access to it 
    number_of_jobs = [JobPost.objects.values('title').annotate(number_of_jobs = Count('title'))[i]['number_of_jobs']
                      for i in range (0, JobPost.objects.values('title').distinct().count())]
   
    def get_serializer_context(self):
        context = super().get_serializer_context()
        context.update({"number_of_jobs": self.number_of_jobs})
        return context

And this is my serializer class:

class JobPostsCountSerializer(serializers.ModelSerializer):
     number_of_jobs = serializers.SerializerMethodField()
     class Meta:
         model = JobPost
         fields = ['title',"number_of_jobs"]
     # get the number of jobs from the context returned by the api view's get_serializer_context()
     def get_number_of_jobs(self, obj):
         count_list = self.context['number_of_jobs']
         job_count = 0
         for item in count_list:
             job_count = item
             count_list.remove(item)
             break
         return job_count

So, as a result, I am getting the number of jobs correctly but the job posts are not in the right order: ‘Data science’: 27, ‘Web development’: 31, ‘Data analyst’ : 20, etc…

Any idea what can I do to make the order correct?

2

First, you don’t need to pass the counts through the context since you already have them in the queryset

class JobPostsCount(generics.ListAPIView):
    queryset = (
        JobPost.objects
        .values('title')
        .annotate(number_of_jobs=Count('title'))
        .order_by('-number_of_jobs')  # Note the '-' for descending order
    )
    serializer_class = JobPostsCountSerializer

Then the simplified serializer can be:

class JobPostsCountSerializer(serializers.Serializer):
    title = serializers.CharField()
    number_of_jobs = serializers.IntegerField()

The reason your solution wasn’t maintaining the order is that you were handling the count separately through the context, which meant the ordering information from the queryset was being lost in the process of serialization.

If you need to customize the output format further, you can do so by modifying the serializer’s to_representation method, for example:

class JobPostsCountSerializer(serializers.Serializer):
    title = serializers.CharField()
    number_of_jobs = serializers.IntegerField()

    def to_representation(self, instance):
        return {
            instance['title']: instance['number_of_jobs']
        }

I hope that helps :slight_smile:

1 Like
3

It did help! Thank you so much!