Skip to content

Instantly share code, notes, and snippets.

@arthuralvim
Forked from bblanchon/example.py
Created December 19, 2022 23:05
Show Gist options
  • Save arthuralvim/00ace1c065e6e37721b6b86f4041bb85 to your computer and use it in GitHub Desktop.
Save arthuralvim/00ace1c065e6e37721b6b86f4041bb85 to your computer and use it in GitHub Desktop.
Django Subquery Aggregate (Count, Sum...)
from django.db.models import OuterRef
weapons = Weapon.objects.filter(unit__player_id=OuterRef('id'))
units = Unit.objects.filter(player_id=OuterRef('id'))
qs = Player.objects.annotate(weapon_count=SubqueryCount(weapons),
rarity_sum=SubquerySum(units, 'rarity'))
class SubqueryCount(Subquery):
# Custom Count function to just perform simple count on any queryset without grouping.
# https://stackoverflow.com/a/47371514/1164966
template = "(SELECT count(*) FROM (%(subquery)s) _count)"
output_field = PositiveIntegerField()
class SubqueryAggregate(Subquery):
# https://code.djangoproject.com/ticket/10060
template = '(SELECT %(function)s(_agg."%(column)s") FROM (%(subquery)s) _agg)'
def __init__(self, queryset, column, output_field=None, **extra):
if not output_field:
# infer output_field from field type
output_field = queryset.model._meta.get_field(column)
super().__init__(queryset, output_field, column=column, function=self.function, **extra)
class SubquerySum(SubqueryAggregate):
function = 'SUM'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment