I have 2 models Puzzle and Play. for each play I have a rating. I would like to annotate to a Puzzle queryset the median rating value for all corresponding plays.
class Puzzle(models.Model):
name = models.CharField(max_length=255)
class Play(models.Model):
puzzle = models.ForeignKey(Puzzle, on_delete=models.CASCADE,related_name='plays')
rating = models.IntegerField(default=-1)
puzzle_completed = models.BooleanField(default=None, blank=False, null=False)
I know how to count:
Puzzle.objects.annotate(nb_sucesses=Count('plays', filter = Q(plays__puzzle_completed=True), distinct = True),)
and I expected getting the median in a similar way:
Puzzle.objects.annotate(rating_median=Median('plays__rating', filter = Q(plays__puzzle_completed=True), distinct = True),)
however, apparently due to my Django development environnment database (sqlite), I'm not able to do that.
From what I infered from the following info https://mariadb.com/kb/en/median/, in my production environment(MariaDB), this approach should work (but I didn't tested it yet)
My current understanding (based on different internet searches) is that I should be able to use Django Func() function to use a custom Median function (following that model ?) that would either call Median if in production, or a more complicated sql raw query if in Development (based on this one ? or on this one?).
But after a few hours, I must admit I am out of my depth here.
Anyone could please help me connect the dots ?
OK, I found out a package with a robust and multi-database implementation of Median and percentile for Django: https://github.com/ankane/tailslide/
it enables user to perform the following:
from tailslide import Median, Percentile
Item.objects.aggregate(Median('price'))
Request.objects.aggregate(Percentile('response_time', .95))