Django Advanced Filtering for a Queryset that can have many related foreign keys

Question

I am trying to sort a query in django on a model that can have multiple foreign keys. I only want the filtering to apply to one of those keys and I am having a hard time getting it to work.

models.py

class Post(models.Model):
    content = models.TextField(max_length=1000)
    reaction_counts = GenericRelation(ReactionCount, related_query_name='post_reaction_counts')

class ReactionType(models.TextChoices):
    LIKE = 'like', ('Like')
    DISLIKE = 'dislike', ('Dislike')
    STAR = 'star', ('Star')
    LOVE = 'love', ('Love')
    LAUGH = 'laugh', ('Laugh')

class ReactionCount(models.Model):
    count = models.PositiveIntegerField(default=0)
    type = models.CharField(
        max_length=7,
        choices=ReactionType.choices,
    )
    post = models.ForeignKey(Post, related_name="reaction_count", on_delete=models.CASCADE)

In this above example the keyed element (Foreign Key Relation) is ReactionCount for Post. I want to sort the posts by the posts with the most likes, dislikes, stars, loves, laughs, etc.

I am trying to do this by doing Post.objects.all().order_by('-reaction_count__type', '-reaction_count__count')

This however does not work as there are multiple ReactionCount objects for each post causing the ordering to not work.

What I need is for a way to specify that I want the ordering of count to only apply only to Posts with a ReactionCount foreign key of a specific type.

Is there a way this can be done by changing how the ordering is done on object Posts? If not is there a way for me to change my models while still preserving the ReactionType choice field such that you can order by the count of a specific reaction type?

As a side note, I managed to get this working by querying ReactionCount objects directly, however if you remove the filtering by a specific type then you get duplicate results of posts since a single post can have multiple reaction types, therefore I believe this option will not work, however I am open to suggestions!

Answer 1

I'm not sure to understand what is the order you want so here are some examples.
I'm you want posts with the most likes

Post.objects.filter(reaction_count__type=ReactionCount.ReactionType.LIKE.value).order_by("-reaction_count__count")

If you want the post with the most reactions

Post.objects.alias(reaction_total=Sum("reaction_count__count")).order_by("-reaction_total")

If you want the post with the most positive reactions

Post.objects.alias(positive_reactions=Sum("reaction_count__count", filter=Q(reaction_count__type__in=[ReactionCount.ReactionType.LIKE.value, ReactionCount.ReactionType.LOVE.value ...]))).order_by("-positive_reactions")