Using model methods in aggregates

Question

I am trying to use a model method in a django aggregate query. I'm not sure if it is possible and I may be going at this the wrong way.

Here is the model I wish to query.

class ClassNumbers(models.Model):
   """
   The class year level and number inline model for a booking
   """
   booking = models.ForeignKey('Booking')
   yearLevel = models.CharField(max_length=10, choices=YEAR_CHOICES, verbose_name='Year Level')
   numberOfStudents = models.IntegerField(verbose_name='Number of Students') 

class Booking(models.Model):
   # A shorter version of the model
   date = models.DateField()
   institution = models.ForeignKey(Institution)

   def getStudentTotal(self):
      # Total class numbers
      classes = ClassNumbers.objects.filter(booking=self.id)
      classTotal = 0
      if ( classes ):
          for c in classes:
              classTotal += c.numberOfStudents
      return classTotal

   def getHDSV(self):
      HDSVunits = {
                'Full-Day': 2.0,
                'Half-Day AM': 1.0,
                'Half-Day PM': 1.0,
                'Three-Quarter Day': 1.5,
                '1 Hour': 0.5,
                'Custom': 1.0,
                }
      numStudents = self.getStudentTotal()
      result = numStudents * HDSVunits[self.price.name]
      return result

The getHDSV method returns a reporting metric used internally where the app lives. I wish to aggregate the metric into a total for the month between a date period.

I am no aggregate/annotate master. My attempts so far have not netted the results I'm after.

Ultimately I queried Bookings between the specified dates and then looped over the results and tallied the reporting unit into a dictionary by calling the getHDSV method each iteration. Of course the resulting dictionary is not sorted the way I would like. So I am now turning to get some help.

Given the way the metric is generated can I call a model method while aggregating data in a query? Or should I be using the HDSVunits dictionary while creating the aggregate? Or is there a better way?

Thanks.

Answer 1

You've got quite a difficult setup, it might be easier to have the HDSVunits mapping on the Price model to make it easier to access in queries.

The best I can come up with is something like this:

Booking.objects.aggregate(
    hdsv=(
        Sum('classnumbers__numberofstudents') * 
        Case(
            When(price__name='Full-Day', then=2.0),
            When(price__name='Half-Day AM', then=1.0),
            When(price__name='Full-Day PM', then=1.0),
            When(price__name='Three-Quarter Day', then=1.5),
            When(price__name='1 Hour', then=0.5),
            When(price__name='Custom', then=1.0),
            output_field=FloatField(),
        )
    )
)

If the HDSV value were stored as a field on the Price model, you could simply do:

Booking.objects.aggregate(
    hdsv=Sum('classnumbers__numberofstudents') * F('price__hdsv'))

On a side note, you should really consider following the Python naming convensions which would make it easier for other Python developers to help you.