Django database queries-CodePudding

I want to change this code to a faster query

response = []

for player in Player.objects.all():
   total_earn = Credit.objects.filter(player=player).aggregate(Sum('amount')).get('amount__sum', 0)
   total_earn  = Purchase.objects.filter(player=player).aggregate(Sum('amount')).get('amount__sum', 0)
   reponse.append([player.id, player.email, player.phone, total_earn])

I try this for a moment, but now it take a lot of time to execute and it causes timeout on the server.

I want something very fast, like that:

response = Player.objects.annotate(
  id='id',
  email='email',
  phone='phone',
  total_earn=(Credit....   Purchase....)
)

My models:

class Player(AbstractUser):
   email = models.EmailField(..)
   phone = models.CharField(..)

class Credit(models.Model):
   player = models.ForeignKey(Player, ..., CASCADE)
   amount = modesl.DecimalField(decimal_places=2, ...)

class Purchase(models.Model):
   player = models.ForeignKey(Player, ...)
   amount = models.DecimalField(decimal_places=2, ...)

CodePudding user response：

You can make use of subqueries:

from django.db.models import OuterRef, Subquery

credits = Credit.objects.filter(
    player=OuterRef('pk')
).values('player').annotate(
    total=Sum('amount')
).order_by('player').values('total')

purchases = Purchases.objects.filter(
    player=OuterRef('pk')
).values('player').annotate(
    total=Sum('amount')
).order_by('player').values('total')

Player.objects.annotate(
    total_earn=Subquery(credits)[:1] - Subquery(purchases)[:1]
)

However it looks like there is some bad modeling. It might be better to make a single model for Credits and Purchases and thus use a negative amount for Purchases. If such model is named Earning for example, then one can do that with a simple Player.objects.annotate(total_earn=Sum('earning__amount')).