CODEWITHSUNDEEP
djangodjango-annotatedjango-subquery
artem
artem

Reputation: 16777

Django annotate by sum of two values with multiple relations

I have 4 models:

class App(models.Model):
   ...

class AppVersion(models.Model):
   app = models.ForeignKey(App)
   version_code = models.IntegerField()

   class Meta:
        ordering = ('-version_code',)
   ...

class Apk(models.Model):
   version = models.OneToOneField(AppVersion)
   size = models.IntegerField()

class Obb(models.Model):
   version = models.ForeignKey(AppVersion)
   size = models.IntegerField()

AppVersion version always has one Apk, but may have 0, 1 or 2 Obb's

I want to annotate QuerySet by total size of the App (which is Apk.size + sum of all Obb.size for given AppVersion).

My App QuerySet looks like this:

qs = App.objects.filter(is_visible=True)

and versions subquery is:

latest_versions = Subquery(AppVersion.objects.filter(application=OuterRef(OuterRef('pk'))).values('pk')[:1])

This subquery always gives the latest AppVersion of the App.

So what subquery should I use to annotate qs with size attribute calculated as shown above?

Upvotes: 1

Views: 692

Answers (1)

hancho
hancho

Reputation: 1437

How about something like this - from my understanding you want an Apps apk, and obb sizes summed. apk and obb_set can be replaced by the fields related name if you added one. What I chose should be the defaults for a django OneToOne and Fk related name. 

from django.db.models import F, Value, Sum, IntegerField

qs = App.objects.filter(
    is_visible=True
).annotate( 
    apk_size=Sum('apk__size'), 
    obb_size=Sum('obb_set__size')
).annotate( 
    total_size=Value( 
    F('apk_size') + F('obb_size'), 
    output_field=IntegerField()
)

Upvotes: 2

Related Questions