1

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?

artem
  • 16,382
  • 34
  • 113
  • 189

1 Answers1

2

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()
)
hancho
  • 1,345
  • 3
  • 19
  • 39
  • One `App` has multiple `AppVersion`'s, I need `size` only for first `AppVersion` in the given queryset, but your suggested code will output sum of `Apk`'s and `Obb`s for all versions. – artem Sep 30 '19 at 18:03
  • What does this model look like? `AndroidApplicationVersion` – hancho Sep 30 '19 at 18:27
  • sorry, I've edited the question — it's `AppVersion`. – artem Sep 30 '19 at 18:30
  • What field determines which version is the latest? – hancho Sep 30 '19 at 18:31
  • Updated code in question once again. Latest version is with the biggest `version_code` (queryset is reverse-ordered by field `version_code`, so it's the first item in queryset). – artem Sep 30 '19 at 18:33
  • thanks, would adding `.order_by('-version_code').first()` or `.order_by('-version_code')[0]` not work? – hancho Sep 30 '19 at 18:36