Django ORM: Conditional filtering

Question

In a Django app, I keep daily scores of users in such a model:

class Score(models.Model):
    user = models.ForeignKey(User)
    score = models.PositiveIntegerField(default=0)
    date = models.DateField(auto_now_add=True)

I want to find out the days when a user's score has changed drastically compared to a consequent day. That is, if for example, the user scores 10 times higher than the previous day.

How can I include such a condition in a query filter using Django ORM? Is it possible with a single query using conditional expressions as described here: https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/

Thanks.

Answer 1

If you change your Score class slightly to include the previous day's score (which is admittedly pretty wasteful), you can pack the query into one line using F expressions.

Your new class:

class Score(models.Model):
user = models.ForeignKey(User)
score = models.PositiveIntegerField(default=0)
lastscore = models.PositiveIntegerField(default=0)
date = models.DateField(auto_now_add=True)

Then the filter becomes:

from django.db.models import F
daily_chg = 10
big_changes = Score.objects.filter(score__gt=daily_chg*F('lastscore'))

Instead of using timedeltas to search for and set the previous day's score field, I'd look into establishing an ordering via a Meta class and calling latest() when saving the current day's score.