Multiply two fields together in django orm

Question

How would I do the following query (in pseudocode):

CueCompletion.objects.filter(
    user_id=profile_user.pk, status_action_taken_on__gte=day_ago
).sum('amount' * 'opportunities')

Basically, I want to get the sum of the amount * opportunities for the above query.

score 5 · Answer 1 · answered Mar 07 '18 at 07:53

5

You could also add an annotation to the queryset while using the F object. This way you are doing all the calculations in the database which could be faster than pulling all the values into a list and doing a list comprehension using python memory.

CueCompletion.objects.filter(
user_id=profile_user.pk, status_action_taken_on__gte=day_ago
).annotate(total=F('amount') * F('opportunities'))

Now you can access this value by the total variable.

answered Mar 07 '18 at 07:53

HoneyNutIchiros

541
3
9

this is awesome, was looking for this solution for over an hour – cwhisperer Jul 21 '20 at 07:44
Perfect solution – Maha Waqar Nov 03 '20 at 14:17
from django.db.models import F – pevac Apr 15 '21 at 22:13

score 1 · Accepted Answer · edited May 23 '17 at 11:44

One possible way to achieve it is to use a Queryset method called values_list.

It returns a particular queryset (a ValuesListQuerySet), and then you can make some further computations on it.

You can do this:

vqs = CueCompletion.objects.filter( user_id=profile_user.pk, status_action_taken_on__gte=day_ago ).values_list('amount','opportunities')

and then you will have something like vqs = [(50, 2), (100, 4), (25, 2), ...], i.e. a "list" of n tuples, each with the value of amount and opportunities field.

(actually vqs it's not a list, it's a ValuesListQuerySet, but doesn't matter for our next move).

Our next move (a.k.a. the further computations) is:

total = sum(t[0] * t[1] for t in vqs)

where we are using the built-it sum() function on a generator containing all our (amount * opportunities) results.

(why using a generator instead of a list comprehension? check this answer!)

You deserve a medal for this solution. – MiniGunnR Nov 03 '15 at 22:23 — MiniGunnR, Nov 03 '15 at 22:23

Multiply two fields together in django orm

2 Answers2