Django ORM aggregation with conditional SUM with group by failing

Question

I'm trying to get the follwing query to execute in Django ORM but no luck so far.

I would like to have all PotatoesMeasurement grouped by wn and do a conditional count on alternaria when it greater than, smaller than and in range of certain values. I have the raw mongosh query to get the correct result but I'm unable to get the equivelent in django ORM.

My model looks like:

class BasicEntity(models.Model):
    class Meta:
        abstract = True

    id = models.UUIDField(default=uuid4, editable=False, db_index=True, primary_key=True)
    created_on = models.DateTimeField(auto_now_add=True, editable=False, blank=True)
    updated_on = models.DateTimeField(auto_now=True, editable=False, blank=True)

class PotatoesMeasurement(BasicEntity):
    class Meta:
        db_table = "measurements_potatoes"

    colorado_potato_beetle_larvae = models.FloatField( default=0.0,
                               validators=[MinValueValidator(0), MaxValueValidator(100)])
    aphids_per_leaflet = models.IntegerField(blank=False, null=False)
    late_blight = models.FloatField( default=0.0,
                               validators=[MinValueValidator(0), MaxValueValidator(100)])
    alternaria = models.FloatField( default=0.0,
                               validators=[MinValueValidator(0), MaxValueValidator(100)])
    wn = models.IntegerField(default=datetime.now().isocalendar().week,
                             validators=[MinValueValidator(1), MaxValueValidator(53)])

Mongosh query working perfectly:

db.measurements_potatoes.aggregate([
  {
    $group: {
      _id: "$wn",
      countSmaller: { $sum: { $cond: [{ $lte: ["$alternaria", 1] }, 1, 0] } },
      countRange: { $sum:   { $cond: [{ $range: [ 20, "$alternaria", 30 ] }, 1, 0] } },
      countBigger: { $sum:  { $cond: [{ $gt: ["$alternaria", 90] }, 1, 0] } }
    }
  },
  {$sort: {_id: 1}},
]);

On django ORM side so far I have the following:

res = (
    PotatoesMeasurement.objects.all()
    .values("wn")
    .aggregate(
        countSmaller=Sum(
            Case(When(alternaria__lt=1, then=1), default=0, output_field=IntegerField())
        ),
        countRange=Sum(
            Case(
                When(alternaria__range=[30, 50], then=1),
                default=0,
                output_field=IntegerField(),
            )
        ),
        countBigger=Sum(
            Case(When(alternaria__gt=1, then=1), default=0, output_field=IntegerField())
        ),
    )
    .order_by("wn")
)

The above command fail with the following error:

 raise exe from e
djongo.exceptions.SQLDecodeError: 

        Keyword: None
        Sub SQL: None
        FAILED SQL: SELECT SUM(CASE WHEN "measurements_potatoes"."alternaria" < %(0)s THEN %(1)s ELSE %(2)s END) AS "countSmaller", SUM(CASE WHEN "measurements_potatoes"."alternaria" BETWEEN %(3)s AND %(4)s THEN %(5)s ELSE %(6)s END) AS "countRange", SUM(CASE WHEN "measurements_potatoes"."alternaria" > %(7)s THEN %(8)s ELSE %(9)s END) AS "countBigger" FROM "measurements_potatoes"
        Params: (1.0, 1, 0, 30.0, 50.0, 1, 0, 1.0, 1, 0)
        Version: 1.3.6

The above exception was the direct cause of the following exception:

any idea why the query is failing? or maybe is there an alternative to get the raw query to execute?

Thanks

Answer 1

Your Django ORM query will fail due to incorrect use of .aggregate() and .order_by() together.

.aggregate() is a QuerySet method, which is used to calculate aggregates over the entire QuerySet. Importantly, it does not return a QuerySet itself but instead returns a Dictionary (See Docs).

Since .order_by() is also a QuerySet method, not a Dictionary method, you therefore cannot chain it after .aggregate().

Instead, you should use .annotate() if you want to group by wn and retain the QuerySet to order it.

The corrected approach therefore involves using .annotate() to compute the conditional sums per group, and then using .order_by() as needed on the result.

Here's how you can restructure your query accordingly:

from django.db.models import Sum, Case, When, IntegerField

res = (
    PotatoesMeasurement.objects
    .values('wn')
    .annotate(
        countSmaller=Sum(
            Case(
                When(alternaria__lte=1, then=1),
                default=0,
                output_field=IntegerField()
            )
        ),
        countRange=Sum(
            Case(
                When(alternaria__gte=20, alternaria__lt=30, then=1),
                default=0,
                output_field=IntegerField()
            )
        ),
        countBigger=Sum(
            Case(
                When(alternaria__gt=90, then=1),
                default=0,
                output_field=IntegerField()
            )
        )
    )
    .order_by('wn')
)

Please note I've changed the countRange condition to use __gte and __lt for the range.

Now as for the SQLDecodeError being thrown, if the above does not fix the problem (which it hopefully will do), then it is possible there is a limitation in Djongo's ability to translate these queries to MongoDB's aggregation framework (Djongo being the Django ORM wrapper for MongoDB that you appear to be using).

I'm afraid I'm not a MongoDB expert so I can't help with that if the error does continue to be raised. But hopefully making the above change to your query means that you will be sending a pure QuerySet to Djongo, and that hopefully should be convertable into the MongoDB query and therefore your error should be solved.