Combine two query sets based on shared field

Question

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

So for every user_id, you want toe sum of the totals? What if there are multiple product1s and/or product2s for that user? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 26, 2018 at 18:52
Right. Fortunately, that table will only have have one entry for each type of product. The data there comes from a CSV that's imported that already totals each product. I could iterate through the CSV and combine totals with and set a product type of "all" or something, I suppose, but I wanted to avoid that route for a few reasons. — Andrew
– Andrew, Commented Aug 26, 2018 at 18:59

willeM_ Van Onsem · Accepted Answer · 2018-08-26 18:59:09Z

1

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.

answered Aug 26, 2018 at 18:59

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Andrew Over a year ago

That's exactly what I was going for. Now to dig into the documentation because that seems like magic. Thank you!

Andrew Over a year ago

Is there a way to get an average using the aggregated values of total and total_items? Effectively, in annotate() I have total_items=Sum('total_items'), total=Sum('total') which returns the expected values, and I want to do average=total/total_items. I attempted average=total/total_items and got an error that neither total or total_items were defined, even though I was getting the correct values in the QuerySet for both.

willeM_ Van Onsem Over a year ago

Yes, average=Avg('total') (with `from django.db.models import Avg). Note that you better not use the same name as one of the fields, since that can result in incorrect queries.

Andrew Over a year ago

That gives me the average of product_1 and product_2 totals (product_1_total + product_2_total / 2, where I'm attempting product_1_total + product_2_total / product_1_total_items + product_2_total_items. Would I need to iterate through the QuerySet of dictionaries and create a new key for the total average?

willeM_ Van Onsem Over a year ago

Ah, you want the average of (all these products)?

|

Collectives™ on Stack Overflow

Combine two query sets based on shared field

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related