Search code examples
pythondjango

Implementing a ranking algorithm in Django query


I currently have upvotes and downvotes stored. I am trying to make a ranking algorithm as such

ranking = log(upvotes - downvotes)

But in doing my query in Django I am using annotate as such, and am unsure where I would add in the math.log to the annotate so it ranks it by log?

Here is the code:

    defeniciones = Palabra.objects.prefetch_related(
        'tag_set'
    ).filter(
        nombre__iexact=palabra,
        aprobada=True
    ).annotate(total_votes=Count('userUpVotes') - Count('userDownVotes')).order_by('-total_votes')

As can be seen here, https://diccionarioespañol.com/significado/wey/ It ranks the 1 upvote 0 downvotes fine. But then ranks a 6 upvotes, and 1 downvote at the bottom, instead of number 2.

So I think log will fix this, but if not I am unsure how what an equivalent fix would be.


Solution

  • Finally figured it out, after checking django documenation. It says here

    "Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries:"

    So the soluction was to change to annotate to include distinct=True in the Count like such

    ).annotate(total_votes=Count('userUpVotes', distinct=True) - Count('userDownVotes', distinct=True)).order_by('-total_votes')