CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
Andrew
Andrew

Reputation: 12442

Combine two query sets based on shared field

I have two query sets that look like this:

product_first = Reports.objects.filter(product='product1', type='week', profile__team=team).select_related('profile')

product_second = Reports.objects.filter(product='product2', type='week', profile__team=team).select_related('profile')

Each of those share obj.profile.user.username, and have other similiar attributes (obj.total, for instance) - I am trying to end up with a query set based on obj.profile.user.username, but with the obj.total being product_first.total + product_second.total.

Example of that table I'm querying:

user_id  total  total_items  product    type
1        150    15           product1   week
1        180    19           product2   week

Upvotes: 1

Views: 596

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477684

We can do this by annotating, and grouping on the username:

from django.db.models import F, Sum

qs = Reports.object.filter(
    product__in=['product1', 'product2'],
    type='week',
    profile__team=team
).values('profile_id').annotate(
    username=F('profile__user__username')
    the_total=Sum('total')
).order_by('profile_id', 'username')

This will result in a QuerySet of dictionaries, each dictionary containing three keys: 'profile_id', 'username', and 'the_total'. So for the given sample data, it will look like:

<QuerySet [{'profile_id': 1, 'username': 'foo', 'the_total': 330}]>

(given 'foo' is the username of the user with id=1).

Note that the the_total will contain the sum of the totals of all the product1s and product2s. If the there is no product2 then it will still show the sum of the product1s. If there are multiple product1s, it will sum up these.

Upvotes: 1

Related Questions