CODEWITHSUNDEEP
pythonpython-3.xdjango
nadermx
nadermx

Reputation: 2776

Sorting by user upvotes and downvotes not working properly

I currently have a model as such

class IpAddress(models.Model):
    creado = models.DateTimeField(auto_now_add=True)
    ip_address = models.GenericIPAddressField(unique=True)

class Palabra(models.Model):
    nombre = models.CharField(max_length=250)
    definicion = models.CharField(max_length=1000, unique=True)
    ejemplo = models.CharField(max_length=250)
    creado = models.DateTimeField(auto_now_add=True)
    user = models.ForeignKey(CustomUser, on_delete=models.SET_NULL, null=True, blank=True)
    anonimo = models.BooleanField(default=True)
    aprobada = models.BooleanField(default=False)
    userUpVotes = models.ManyToManyField(IpAddress, blank=True, related_name='threadUpVotes')
    userDownVotes = models.ManyToManyField(IpAddress, blank=True, related_name='threadDownVotes')
    gramatica = models.CharField(max_length=250, null=True, blank=True)
    pais = models.CharField(max_length=250, null=True, blank=True)

And my ranking functions is as such

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

I have a situation where a word has two upvotes and no downvotes. But a word with no upvotes or downvotes, ranks higher.

I'm using NullIf due to postgres.

And an example is here where it ranks one with 1 upvote over one with 6 upvotes.

Full view

class PalabraView(View):
    def get(self, request, palabra=None):
        defeniciones = Palabra.objects.prefetch_related(
            'tag_set'
        ).filter(
            nombre__iexact=palabra,
            aprobada=True
        ).annotate(total_upvotes=Count('userUpVotes'),
                   total_votes=Count('userUpVotes') / NullIf((Count('userUpVotes') + Count('userDownVotes')),
                                                             0)).order_by(F('total_votes').desc(nulls_last=True),
                                                                          "-total_upvotes")
        for post in defeniciones:
            post.up_votes = post.userUpVotes.all()
            post.down_votes = post.userDownVotes.all()
            tags = post.tag_set.all()
            post.sins = ', '.join(['<a href="/significado/%s/">%s</a>' % (
                str(item).lower().strip('.'),
                str(item).lower().strip('.'),
            ) for item in tags.filter(tag_type='Sinónimo')])
            post.ants = ', '.join(['<a href="/significado/%s/">%s</a>' % (
                str(item).lower().strip('.'),
                str(item).lower().strip('.'),
            ) for item in tags.filter(tag_type='Antónimo')])

        paginator = Paginator(defeniciones, 20)  # Show 25 contacts per page.
        page_number = request.GET.get('page')
        page_obj = paginator.get_page(page_number)
        add_word = False
        ipaddress = IpAddress.get_my_ip(request)
        if defeniciones.count() == 0:
            add_word = True
        context = {
            'add_word': add_word,
            'palabra': palabra,
            'defeniciones': defeniciones,
            'title': '%s: Significado de %s - DiccionarioEspañol.com' % (palabra.capitalize(), palabra),
            'description': '%s: Significado de %s en DiccionarioEspañol.com' % (palabra.capitalize(), palabra),
            'page_obj': page_obj,
            'ipaddress': ipaddress,
            'countries': COUNTRIES
        }

        return render(request, 'palabra.html', context)

Upvotes: 0

Views: 92

Answers (1)

Yevhenii Kosmak
Yevhenii Kosmak

Reputation: 3860

total_votes for word with 0 upvotes and 0 downvotes equals NULL. You can check it this way:

SELECT 0 / NULL;
---
<null>

Also NULL ranks higher than everything when you're sorting DESC, for example:

SELECT * FROM (VALUES (NULL), (1), (NULL)) "n" ORDER BY "n" DESC;
---
<null>
<null>
1

So you just need to add NULLS LAST and everything would work fine:

from django.db.models import F  

...

defeniciones = Palabra.objects.prefetch_related(
    'tag_set'
).filter(
    nombre__iexact=palabra,
    aprobada=True
).annotate(
    total_votes=Count('userUpVotes') /
                NullIf((Count('userUpVotes') + Count('userDownVotes')), 0)
).order_by(F('total_votes').desc(nulls_last=True))

Upvotes: 1

Related Questions