Django annotate by sum of two values with multiple relations

Question

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?

hancho · Accepted Answer · 2019-09-30 17:52:23Z

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()
)

answered Sep 30, 2019 at 17:52

hancho

1,4573 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

artem Over a year ago

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 Obbs for all versions.

hancho Over a year ago

What does this model look like? AndroidApplicationVersion

artem Over a year ago

sorry, I've edited the question — it's AppVersion.

hancho Over a year ago

What field determines which version is the latest?

artem Over a year ago

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).

|

Collectives™ on Stack Overflow

Django annotate by sum of two values with multiple relations

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related