CODEWITHSUNDEEP
pythondjangopython-3.xdjango-queryset
Lucas Henrique
Lucas Henrique

Reputation: 13

How to get correct queryset for Datatable

I have two Models, Profile and Payment. And i need to show at the front-end a Datatable with the fields "Username","Email" and "Total Spent". Username and Email can be fount at Profile model, so the queryset would be:

def get_initial_queryset(self):
  return Profile.objects.all()

However, the "Total Spent" information needs to be calculated as the Sum of all "payment_amount" fields found at Payment model for the same "profile_id", since a user can have two Payments (one with amount=5 and another one with amount=15, and i need to show total_spent=20).

The problem is, since i'm using Datatables, i NEED this "total_spent" field to be in the queryset (using Annotate or another method).

I've tried using Subquery and OuterRef, but i'm getting erros in the final SQL generated.

return Profile.objects.all().annotate(
  money_spent=Subquery(
    Payment.objects.filter(user_id__in=OuterRef('id')).annotate(
      money_spent=Sum('amount')
    ),
    output_field=CharField()
  )
)

But this gives me SQL error: (1241, 'Operand should contain 1 column(s)')

How can i get the correct queryset ?

Django Version: 1.11 | Python Version: 3.6.8

Upvotes: 1

Views: 458

Answers (1)

iklinac
iklinac

Reputation: 15738

You don't need to do Subquery at all you can check more in docs regarding joins and aggregates

Profile.objects.annotate(money_spent=Sum('payment__amount'))

Which would result in following

SELECT profile.*, SUM(payment.amount) AS money_spent
   FROM profile
   LEFT OUTER JOIN payment ON payment.profile_id = profile.id
   GROUP BY profile.id

Upvotes: 2

Related Questions