Django how to sort by fields that don't exist

Currently, I have two models

posts.models

from django.db import models
from django_prometheus.models import ExportModelOperationsMixin
from users.models import User
from category.models import Category

# Create your models here.

class Post(ExportModelOperationsMixin('post'), models.Model):
  
  header_image = models.ImageField(default='ancean-no-header-image.png')
  title = models.CharField(max_length=100)
  introduce = models.TextField(default='')
  content = models.JSONField('json', null=True, blank=True)
  author = models.ForeignKey(User, on_delete=models.CASCADE, db_column="author", related_name='author')
  category = models.ForeignKey(Category, on_delete=models.SET_NULL, db_column="category", related_name='category', null=True, blank=True)
  wave = models.IntegerField(default=0) # wave field like 'like post' on general SNS
  created_at = models.DateTimeField(null=True)
  updated_at = models.DateTimeField(auto_now=True)
  is_finish = models.BooleanField(default=False)
  is_public = models.BooleanField(default=False)
  
  def __str__(self):
    return f'{self.title} - {self.author}'

category.models

from django.db import models

class Category(models.Model):
  
  name = models.CharField(max_length=30, unique=True)
  
  def __str__(self):
    return f'{self.name}' 

I want to create a logic that gets the number of posts per category and sorts them in descending order. "http://localhost:8000/api/category?ordering=-post_count" Through the url as above I want to make it like the result of this query.

        SELECT c.name, count(*) AS post_count FROM category_category c JOIN posts_post p ON (c.id = p.category)
        GROUP BY p.category
        ORDER BY count(*) DESC

I want to use Django ordering, but post_count is a non-existent field(Category model), so I can't use it. I'd appreciate it if you could tell me a good way.

category.views

class CategoryView(GenericAPIView, ListModelMixin):

  queryset = Category.objects.all()
  serializer_class = CategorySerializer
  filter_backends = [OrderingFilter]
  ordering_fields = ['post_count']
  authentication_classes = []
  
  def get(self, request, *args, **kwargs):
    return self.list(request)

I tried to query the data I wanted to get. I tried to create a custom query.

The related_name=… parameter ^[Django-doc] is the name of the relation in reverse, so from the Category model to the Post model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the category relation to posts:

class Post(ExportModelOperationsMixin('post'), models.Model):
    # …
    category = models.ForeignKey(
        Category,
        on_delete=models.SET_NULL,
        db_column='category',
        related_name='posts',
        null=True,
        blank=True,
    )
    # …

We can then define an alias of the count and order by the number of posts:

from django.db.models import Count


class CategoryView(GenericAPIView, ListModelMixin):
    queryset = Category.objects.annotate(post_count=Count('posts')).order_by(
        '-post_count'
    )
    serializer_class = CategorySerializer
    filter_backends = [OrderingFilter]
    ordering_fields = ['post_count']
    authentication_classes = []
    # …

Note: In order to resolve from what class a method call is routed, the Method Resolution Order ^[python-doc] is used. Since mixins typically define a function in terms of what is already defined, mixins typically are listed before the base class (there are exceptions), but in this case, you thus reorder the classes to:

class CategoryView(ListModelMixin, GenericAPIView):
    # …