Dry ORM

Code

Result

no cache

from decimal import Decimal
from django.db import models
from django.db.models import OuterRef, Subquery, Sum
from django.db.models.functions import Coalesce

class A(models.Model):
    name = models.CharField() # not an FK
    amount = models.DecimalField(max_digits=10, decimal_places=2)

class B(models.Model):
    name = models.CharField()
    foo = models.CharField()

class BSnapshot(models.Model):
    version_name = models.TextField()
    name = models.CharField()
    foo = models.CharField()

def run():
    B.objects.bulk_create([
        B(name='Alice', foo='live_alice_foo'),
        B(name='Bob', foo='live_bob_foo'),
        B(name='Eve', foo='live_eve_foo'),
    ])

BSnapshot.objects.bulk_create([
        BSnapshot(version_name='v1', name='Alice', foo='snap_v1_alice'),
        BSnapshot(version_name='v2', name='Alice', foo='snap_v2_alice'),
        BSnapshot(version_name='v1', name='Charlie', foo='snap_v1_charlie'),
    ])

A.objects.bulk_create([
        A(name='Alice', amount=Decimal('10.50')),
        A(name='Alice', amount=Decimal('5.25')),
        A(name='Bob', amount=Decimal('7.00')),
        A(name='Charlie', amount=Decimal('3.00')),
        A(name='Dennis', amount=Decimal('4.00')),  # no B or snapshot -> will be filtered out
    ])

version_name = 'v1'
    live_foo = B.objects.filter(name=OuterRef('name'))
    snapshot_foo = BSnapshot.objects.filter(name=OuterRef('name'), version_name=version_name)

foo = Coalesce(
        Subquery(snapshot_foo.values('foo')[:1]),
        Subquery(live_foo.values('foo')[:1])
    )

version_1 = list(
        A.objects
        .values('name')
        .order_by('name')
        .annotate(amount_sum=Sum('amount'))
        .annotate(foo=foo)
        .filter(foo__isnull=False)
    )

Output

No output.

Queries

Queries Q (0)

No queries.