I am creating a site similar to reddit and hacker news that has a database of links and votes. I am implementing hacker news' popularity algorithm and things are going pretty swimmingly until it comes to actually gathering up these links and displaying them. The algorithm is simple:
Y Combinator's Hacker News:
Popularity = (p - 1) / (t + 2)^1.5`
Votes divided by age factor.
Where`
p : votes (points) from users.
t : time since submission in hours.
p is subtracted by 1 to negate submitter's vote.
Age factor is (time since submission in hours plus two) to the power of 1.5.factor is (time since submission in hours plus two) to the power of 1.5.
I asked a very similar question over yonder http://stackoverflow.com/questions/1964395/complex-ordering-in-django but instead of contemplating my options I choose one and tried to make it work because that's how I did it with PHP/MySQL but I now know Django does things a lot differently.
My models look something (exactly) like this
class Link(models.Model):
category = models.ForeignKey(Category)
user = models.ForeignKey(User)
created = models.DateTimeField(auto_now_add = True)
modified = models.DateTimeField(auto_now = True)
fame = models.PositiveIntegerField(default = 1)
title = models.CharField(max_length = 256)
url = models.URLField(max_length = 2048)
def __unicode__(self):
return self.title
class Vote(models.Model):
link = models.ForeignKey(Link)
user = models.ForeignKey(User)
created = models.DateTimeField(auto_now_add = True)
modified = models.DateTimeField(auto_now = True)
karma_delta = models.SmallIntegerField()
def __unicode__(self):
return str(self.karma_delta)
and my view:
def index(request):
popular_links = Link.objects.select_related().annotate(karma_total = Sum('vote__karma_delta'))
return render_to_response('links/index.html', {'links': popular_links})
Now from my previous question, I am trying to implement the algorithm using the sorting function. An answer from that question seems to think I should put the algorithm in the select and sort then. I am going to paginate these results so I don't think I can do the sorting in python without grabbing everything. Any suggestions on how I could efficiently do this?
EDIT
This isn't working yet but I think it's a step in the right direction:
from django.shortcuts import render_to_response
from linkett.apps.links.models import *
def index(request):
popular_links = Link.objects.select_related()
popular_links = popular_links.extra(
select = {
'karma_total': 'SUM(vote.karma_delta)',
'popularity': '(karma_total - 1) / POW(2, 1.5)',
},
order_by = ['-popularity']
)
return render_to_response('links/index.html', {'links': popular_links})
This errors out into:
Caught an exception while rendering: column "karma_total" does not exist
LINE 1: SELECT ((karma_total - 1) / POW(2, 1.5)) AS "popularity", (S...
EDIT 2
Better error?
TemplateSyntaxError: Caught an exception while rendering: missing FROM-clause entry for table "vote"
LINE 1: SELECT ((vote.karma_total - 1) / POW(2, 1.5)) AS "popularity...
My index.html is simply:
{% block content %}
{% for link in links %}
karma-up
{{ link.karma_total }}
karma-down
{{ link.title }}
Posted by {{ link.user }} to {{ link.category }} at {{ link.created }}
{% empty %}
No Links
{% endfor %}
{% endblock content %}
EDIT 3
So very close! Again, all these answers are great but I am concentrating on a particular one because I feel it works best for my situation.
from django.db.models import Sum
from django.shortcuts import render_to_response
from linkett.apps.links.models import *
def index(request):
popular_links = Link.objects.select_related().extra(
select = {
'popularity': '(SUM(links_vote.karma_delta) - 1) / POW(2, 1.5)',
},
tables = ['links_link', 'links_vote'],
order_by = ['-popularity'],
)
return render_to_response('links/test.html', {'links': popular_links})
Running this I am presented with an error hating on my lack of group by values. Specifically:
TemplateSyntaxError at /
Caught an exception while rendering: column "links_link.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...karma_delta) - 1) / POW(2, 1.5)) AS "popularity", "links_lin...
Not sure why my links_link.id wouldn't be in my group by but I am not sure how to alter my group by, django usually does that.
