Annotating in django using a field from another model

Question

I'm trying to calculate a field that it's a sum agregation when two of other fields are the same. The problem is that one of this fields is from the same model as the sum field, but the other one is from another model.

models.py:

class Node(model.Model):
    text = models.CharField(max_length=100, verbose_name=_('Text'))
    perimeter_ID = models.CharField(max_length=100, verbose_name=_('Perimeter_ID'))
    pos_x = models.FloatField(verbose_name=_('Position X'))
    pos_y = models.FloatField(verbose_name=_('Position Y'))
    class Meta:
        ordering = ['pk']


class Routes(models.Model):
    from_id = models.ForeignKey(Node, on_delete=models.PROTECT, verbose_name=_('From'), related_name='transport_from')
    to_id = models.ForeignKey(Node, on_delete=models.PROTECT, verbose_name=_('To'), related_name='transport_to')
    n_box = models.FloatField(verbose_name=_('Number of boxes'))
    day = models.CharField(max_length=10, verbose_name=_('day'))

    class Meta:
        ordering = ['from_id__id', 'to_id__id']

And in my views, I'm first filtering by day as the request will be something like filter/?day=20220103 and then I want to know the sum of boxes when from_id and perimeter_id are the same (the perimeter_id of the node corresponding to to_id). So, I need somehow to make the relation between, the to_id node and its perimeter_id.

views.py:

class sumByPerimeterListAPIView(ListAPIView):
    queryset = Routes.objects.all()
    filter_backends = [DjangoFilterBackend]
    filter_fields = {
        'day': ["in", "exact"],
    }
    def get(self, request, **kwargs):
        queryset = self.get_queryset()
        filter_queryset = self.filter_queryset(queryset)
        values = filter_queryset.values('from_id')\ # TODO: INCLUDE  perimeter_id OF to_id
            .annotate(n_box=Sum('n_box'))
        return Response(values)

I've been reading about subquery and OuterRef in Django, also in the following link: Django 1.11 Annotating a Subquery Aggregate. But these examples are not valid for me as I don't need to annotate the field perimeter_id. I need to agrupate by from_id (model Routes) and perimeter_id (model Node) and annotate by n_box.

I understand that `values = filter_queryset.values('from_id', 'to_id__perimeter_ID') .annotate(n_box=Sum('n_box'))` should do it, but it's not doing the agrupation. — patatafly, Jun 01 '22 at 08:17

score 0 · Answer 1 · answered Jun 01 '22 at 08:33

To get a conditioned annotation, try using the case when statement inside the annotation:

Below is an example of it

from django.db.models import Count, Case, When, IntegerField
Article.objects.annotate(
    numviews=Count(Case(
        When(readership__what_time__lt=treshold, then=1),
        output_field=IntegerField(),
    ))
)

In your case you have to change the below line as follows:

values = filter_queryset.annotate(n_box=Sum(When(Case (from_id=from_id.perimeter_ID, then=n_box), n_box=DecimalField())

Try something similar using when, case, and then.

I hope it will solve your conditioning issues.

Also, have a look at F expressions. They are very useful when it comes to conditioning.

Annotating in django using a field from another model

1 Answers1