sqldjangosqlitemariadbdjango-annotate

How to annotate median value to django queryset


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 ?


Solution

  • 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))