It is possible to GROUP BY multiple columns separately and aggregate each one of them by other column with django ORM?

user_10941410 0
Viewed 790

I know how to GROUP BY and aggregate:

>>> from expenses.models import Expense
>>> from django.db.models import Sum
>>> qs = Expense.objects.order_by().values("is_fixed").annotate(is_fixed_total=Sum("price"))
>>> qs
<ExpenseQueryset [{'is_fixed': False, 'is_fixed_total': Decimal('1121.74000000000')}, {'is_fixed': True, 'is_fixed_total': Decimal('813.880000000000')}]>

However, If I want to do the same for other two columns, it only returns the last:

>>> qs = (
...     Expense.objects.order_by()
...     .values("is_fixed")
...     .annotate(is_fixed_total=Sum("price"))
...     .values("source")
...     .annotate(source_total=Sum("price"))
...     .values("category")
...     .annotate(category_total=Sum("price"))
... )
>>> qs
<ExpenseQueryset [{'category': 'FOOD', 'category_total': Decimal('33.9000000000000')}, {'category': 'GIFT', 'category_total': Decimal('628')}, {'category': 'HOUSE', 'category_total': Decimal('813.880000000000')}, {'category': 'OTHER', 'category_total': Decimal('307')}, {'category': 'RECREATION', 'category_total': Decimal('100')}, {'category': 'SUPERMARKET', 'category_total': Decimal('52.8400000000000')}]>

It is possible to accomplish what I want with only one query instead of three?

Expected result:

<ExpenseQueryset [{'category': 'FOOD', 'total': Decimal('33.9000000000000')}, {... all other categories ...}, 
{'source': 'MONEY', 'total': Decimal('100')}, {... all other sources ...}, {'is_fixed': False, 'total': Decimal('1121.74000000000')}, {'is_fixed': True, 'total': Decimal('813.880000000000')}]>

Optimally, it could be split into something like:

<ExpenseQueryset ['categories': [{'category': 'FOOD', 'total': Decimal('33.9000000000000')}, {... all other categories ...}], 
'sources': [{'source': 'MONEY', 'total': Decimal('100')}, {... all other sources ...}], 'type': [{'is_fixed': False, 'total': Decimal('1121.74000000000')}, {'is_fixed': True, 'total': Decimal('813.880000000000')}]]>

But this is just a big plus.

django-aggregation django-queryset django python
1 Answers

The Answer is NO, Cause it's not possible with SQL

But you can use the below approach combined with python coding:

I don't think it's possible even in raw SQL, Cause in each query you can group by one or multiple fields together, but not with separated results for each. But it's possible to do it with one query and use little python codes to merge results in the format u want. below I described how u can use it step by step. and in the next section wrote a python method that u can dynamically use for any further usages.

How it works

The only simple solution I can mention is that you group by those 3 fields you want, and do a simple python programming to sum results together for each field. In this method, u will have only one query but separate results for each field group-by.

from expenses.models import Expense
from django.db.models import Sum

qs = Expense.objects.order_by().values("is_fixed", "source", "category").annotate(total=Sum("price"))

Now the result will be something like below:

<ExpenseQueryset [{'category': 'FOOD', 'is_fixed': False, 'source': 'MONEY', 'total': Decimal('33.9000000000000')}, { ...},

Now we can simple aggregate each field result by iterating on this result

category_keys = []
for q in qs:
    if not q['category'] in category_keys:
        category_keys.append(q['category'])

# Now we have proper values of category in category_keys
category_result = []
for c in category_keys:
    value = sum(item['total'] for item in qs if item['category'] == c)
    category_result.append({'category': c, 'total': value)

And the result for the category field will be something like this:

[{'category': 'FOOD', 'total': 33.3}, {... other category results ...}

Now we can continue and make result for other group by fields is_fixed and source like below:

source_keys = []
for q in qs:
    if not q['source'] in source_keys:
        source_keys.append(q['source'])
source_result = []
for c in source_keys:
    value = sum(item['total'] for item in qs if item['source'] == c)
    source_result.append({'source': c, 'total': value)

is_fixed_keys = []
for q in qs:
    if not q['is_fixed'] in is_fixed_keys:
        source_keys.append(q['is_fixed'])
is_fixed_result = []
for c in is_fixed_keys:
    value = sum(item['total'] for item in qs if item['is_fixed'] == c)
    is_fixed_result.append({'is_fixed': c, 'total': value)

Global solution

Now that we know how to use this solution, here is a function to just give fields you want to it and will make proper results for u dynamically.

def find_group_by_separated_by_keys(key_list):
    """ key_list in this example will be:
        key_list = ['category', 'source', 'is_fixed']
    """
    qs = Expense.objects.order_by().values(*tuple(key_list)).annotate(total=Sum("price"))
    qs = list(qs)
    result = []
    for key in key_list:
        key_values = []
        for item in qs:
            if not item[key] in key_values:
                key_values.append(item[key])
        
        key_result = []
        for v in key_values:
            value = sum(item['total'] for item in qs if item[key] == v)
            key_result.append({key: v, 'total': value})

        result.extend(key_result)
    return result

Now just simple use it like below in ur code:

find_group_by_separated_by_keys(['category', 'source', 'is_fixed')

And it will give a list of values like the proper format u wanted

user_107122740
Related