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.

Answer 2

Using timedelta we can test for the last week's days for a given user as such:

from my_app.models import Score
import datetime

def some_view(request):
    days_with_score_boost = []
    today = datetime.date.today()
    for each_day in xrange(0,7):
        day_to_test, day_before_to_test = today - datetime.timedelta(days=each_day), today - datetime.timedelta(days=each_day + 1)  
        day_before_score = Score.objects.get(user=request.user,date=today - datetime.timedelta(days=each_day)).score # will need to catch the exception raised if .get return None and process appropriately
        if Score.objects.filter(user=request.user,score__gte=days_before_score * 10,date=day_before_to_test).count() > 0:
            days_with_score_boost.append(day_to_test)

days_with_score_boost will be a list of datetime.date objects where the score increased by 10 or more from the day before.

In reponse to your comment, I'd make a measure that checks at save time whether the score boost has occured. However, I would get rid of auto_now_add in favor of writing in the save method.

from django.utils import timezone
from django.core.exceptions import ObjectDoesNotExist

class Score(models.Model):
    user = models.ForeignKey(User)
    score = models.PositiveIntegerField(default=0)
    date = models.DateField(null=True,blank=True)
    increased_ten_over_previous_day = models.BooleanField(null=True,blank=True)

    def save(self, *args, **kwargs):
        self.date = timezone.now().today()
        try:
            yesterday_score = Score.objects.get(date=self.date-timezone.timedelta(days=1)).score
            self.increased_ten_over_previous_day = (yesterday_score * 10) <= self.score
        except ObjectDoesNotExist: # called if Score.object.get returns no object; requires you only have one score per user per date
            self.increased_ten_over_previous_day = False
        super(self, Score).save(*args, **kwargs)

Then you could filter objects for a date_range where increased_ten_over_previous_day is True.