Django Subquery Expression contains mixed types. You must set output_field

Question

I am trying to add sum for credits that are taxable my models.py. If I calculate the balance without the taxable_credits it works. The minute I add the taxable_credits into the mix I get the error.

class Account(models.Model):
    name = models.CharField(max_length=32)


class Debit(models.Model):
    account = models.ForeignKey(Account, related_name='debits', on_delete=models.CASCADE)
    amount = models.DecimalField(max_digits=12, decimal_places=2)


class Credit(models.Model):
    account = models.ForeignKey(Account, related_name='credits', on_delete=models.CASCADE)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    taxable = models.BooleanField(default=False)

My test is as follows:

lass TestAccount(TestCase):
    # https://mixedquantum.blogspot.com/2017/08/django-tips-3-subquery-expressions.html

    def setUp(self) -> None:
        self.accounts = dict()
        self.accounts['fox'] = Account.objects.create(name='FOX')
        self.accounts['dog'] = Account.objects.create(name='DOG')
        self.accounts['snake'] = Account.objects.create(name='SNAKE')
        """
        # Credits
        +----------------+-----------------+-----------------+
        | account_name   |   credit_amount | taxable         |
        |----------------+-----------------|-----------------+
        | FOX            |           100.0 | False           |
        | SNAKE          |            50.0 | False           |
        | SNAKE          |            20.0 | False           |
        | DOG            |           300.0 | False           |
        | DOG            |           100.0 | True            |
        +----------------+-----------------+-----------------+
        """
        Credit.objects.create(account=self.accounts['fox'], amount=Decimal('100.0'))
        Credit.objects.create(account=self.accounts['snake'], amount=Decimal('50.0'))
        Credit.objects.create(account=self.accounts['snake'], amount=Decimal('20.0'))
        Credit.objects.create(account=self.accounts['dog'], amount=Decimal('300.0'))
        Credit.objects.create(account=self.accounts['dog'], amount=Decimal('100.0'), taxable=True)
        """
        # Debits
        +----------------+----------------+
        | account_name   |   dedit_amount |
        |----------------+----------------|
        | FOX            |           40.0 |
        | SNAKE          |           30.0 |
        | DOG            |           12.0 |
        | DOG            |           23.0 |
        +----------------+----------------+
        """
        Debit.objects.create(account=self.accounts['fox'], amount=Decimal('40.0'))
        Debit.objects.create(account=self.accounts['snake'], amount=Decimal('30.0'))
        Debit.objects.create(account=self.accounts['dog'], amount=Decimal('12.0'))
        Debit.objects.create(account=self.accounts['dog'], amount=Decimal('23.0'))

    def test_sum(self):
        credits = Credit.objects.filter(
            account=OuterRef('pk')).values('account_id').annotate(sum_credits=Sum('amount'))
        taxable_credits = Credit.objects.filter(
            account=OuterRef('pk'), taxable=True).values('account_id').annotate(sum_taxable_credits=Sum('amount'))
        debits = Debit.objects.filter(
            account=OuterRef('pk')).values('account_id').annotate(sum_debits=Sum('amount'))

        balances = Account.objects.annotate(
            credit_sum=Subquery(credits.values('sum_credits')),
            taxable_credit_sum=Subquery(taxable_credits.values('sum_taxable_credits')),
            debit_sum=Subquery(debits.values('sum_debits')),
            balance= F('credit_sum') - F('debit_sum') - F('taxable_credit_sum')
        ).values_list('name', 'balance')  # , 'taxable_credit_sum')

        self.assertEqual(balances[0], ('FOX', Decimal('60.0')))
        self.assertEqual(balances[2], ('SNAKE', Decimal('40.0')))
        self.assertEqual(balances[1], ('DOG', Decimal('365.0')))
        """
        [('FOX', Decimal('60.00')),
         ('SNAKE', Decimal('40.00')),
         ('DOG', Decimal('265.00'))]
        """

When run the test I get the follow error traceback

Error
Traceback (most recent call last):
  File "/opt/project/alpha_clinic/banking/tests/tests_models.py", line 65, in test_sum
    self.assertEqual(balances[0], ('FOX', Decimal('60.0')))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 308, in __getitem__
    qs._fetch_all()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 144, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1085, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1120, in execute_sql
    sql, params = self.as_sql()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 474, in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 54, in pre_sql_setup
    self.setup_query()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 45, in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 254, in get_select
    sql, params = self.compile(col, select_format=True)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 407, in compile
    return node.output_field.select_format(self, sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/utils/functional.py", line 80, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 258, in output_field
    output_field = self._resolve_output_field()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 290, in _resolve_output_field
    sources_iter = (source for source in self.get_source_fields() if source is not None)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 344, in get_source_fields
    return [e._output_field_or_none for e in self.get_source_expressions()]
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 344, in <listcomp>
    return [e._output_field_or_none for e in self.get_source_expressions()]
  File "/usr/local/lib/python3.6/site-packages/django/utils/functional.py", line 80, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 271, in _output_field_or_none
    return self.output_field
  File "/usr/local/lib/python3.6/site-packages/django/utils/functional.py", line 80, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 258, in output_field
    output_field = self._resolve_output_field()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 1010, in _resolve_output_field
    return super()._resolve_output_field()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 293, in _resolve_output_field
    raise FieldError('Expression contains mixed types. You must set output_field.')
django.core.exceptions.FieldError: Expression contains mixed types. You must set output_field.

I have tried adding output_field to Subquery, which makes no sense to me, but get another error.

Guidance would be appreciated

Do you care about creating the 2 annotations `credit_sum` and `taxable_credit_sum` if you could just calculate them in 1 annotation? Will they be used elsewhere? — Iain Shelvington, Jan 04 '20 at 18:49
Would it be easier to have just 1 model (`Charge` or something) that replaces the `Credit` and `Debit` models? Querying would be far easier — Iain Shelvington, Jan 04 '20 at 21:55

score 0 · Answer 1 · answered Jan 04 '20 at 19:06

You can create these annotations without using Subquery, using the other aggregation functions. All the functions used in this answer are imported like this

from django.db.models import Sum, Case, When, F, Value

To annotates sums of related records you can do this:

Account.objects.annotate(
    credit_sum=Sum('credits__amount')
).values('name', 'credit_sum')
# <QuerySet [{'name': 'FOX', 'credit_sum': Decimal('100.00')}, {'name': 'DOG', 'credit_sum': Decimal('400.00')}, {'name': 'SNAKE', 'credit_sum': Decimal('70.00')}]>

One issue with doing this is that when you sum on 2 relationships you will get duplicates when there are multiple related rows for a record (here all the values for "DOG" are doubled)

Account.objects.annotate(
    credit_sum=Sum('credits__amount'),
    debit_sum=Sum('debits__amount')
).values('name', 'credit_sum', 'debit_sum')
# <QuerySet [{'name': 'FOX', 'credit_sum': Decimal('100.00'), 'debit_sum': Decimal('40.00')}, {'name': 'DOG', 'credit_sum': Decimal('800.00'), 'debit_sum': Decimal('70.00')}, {'name': 'SNAKE', 'credit_sum': Decimal('70.00'), 'debit_sum': Decimal('60.00')}]>

To count distinct values you can add distinct= to the Sum

Account.objects.annotate(
    credit_sum=Sum('credits__amount', distinct=F('credits__id')),
    debit_sum=Sum('debits__amount', distinct=F('debits__id'))
)

To conditionally sum or count rows you can use Case and When, for your taxable_credit_sum annotation it would look like this

balances = Account.objects.annotate(
    credit_sum=Sum('credits__amount', distinct=F('credits__id')),
    debit_sum=Sum('debits__amount', distinct=F('debits__id')),
    taxable_credit_sum=Sum(Case(
        When(credits__taxable=True, then=F('credits__amount')),
        default=Value(0),
        output_field=models.DecimalField()
    ), distinct=F('credits__id'))
).annotate(
    balance=F('credit_sum') - F('debit_sum') - F('taxable_credit_sum')
).order_by('id').values_list('name', 'balance')

Now you should have the query you need

You don't need to calculate both the credit and non taxable credit

Since you just subtract one from the other anyway, you can just create one annotation for this where you sum all credits which are not taxable

balances = Account.objects.annotate(
    debit_sum=Sum('debits__amount', distinct=F('debits__id')),
    non_taxable_credit_sum=Sum(Case(
        When(credits__taxable=False, then=F('credits__amount')),
        default=Value(0),
        output_field=models.DecimalField()
    ), distinct=F('credits__id'))
).annotate(
    balance=F('non_taxable_credit_sum') - F('debit_sum')
).order_by('id').values_list('name', 'balance')

Thanks Ian. When I run your solution I get "Sum does not allow distinct.". As far as not needing the to calculate both, I know. It's just that my actual model is more complex what I wanted was to figure how to filter the result with a variable such as taxable. — Luis Berrocal, Jan 04 '20 at 19:35

score 0 · Answer 2 · answered Jan 04 '20 at 19:42

0

You can try as following:

credits = Credit.objects.filter(account=OuterRef('pk')).values('account_id').annotate(sum_credits=Sum('amount'))
taxable_credits = Credit.objects.filter(account=OuterRef('pk'), taxable=True).values('account_id').annotate(sum_taxable_credits=Sum('amount'))

debits = Debit.objects.filter(account=OuterRef('pk')).values('account_id').annotate(sum_debits=Sum('amount'))

balances = Account.objects.annotate(
        credit_sum=Case(When(Exists(credits), then=Subquery(credits.values('sum_credits'))), default=Value(0)),
        taxable_credit_sum=Case(When(Exists(taxable_credits), then=Subquery(taxable_credits.values('sum_taxable_credits'))), default=Value(0)),
        debit_sum=Case(When(Exists(debits), then=Subquery(debits.values('sum_debits'))), default=Value(0)),
        balance=F('credit_sum') - F('debit_sum') - F('taxable_credit_sum')
    ).values_list('name', 'balance')  # , 'taxable_credit_sum')

self.assertEqual(balances[0], ('FOX', Decimal('60.0')))
self.assertEqual(balances[2], ('SNAKE', Decimal('40.0')))
self.assertEqual(balances[1], ('DOG', Decimal('265.0')))

answered Jan 04 '20 at 19:42

Hasan

878
1
8
19

What version of Django are you using. When run the test I get: Error Traceback (most recent call last): File "/opt/project/alpha_clinic/banking/tests/tests_models.py", line 59, in test_sum credit_sum=Case(When(Exists(credits), then=Subquery(credits.values('sum_credits'))), default=Value(0)), File "/usr/local/lib/python3.6/site-packages/django/db/models/expressions.py", line 866, in __init__ raise TypeError("__init__() takes either a Q object or lookups as keyword arguments") TypeError: __init__() takes either a Q object or lookups as keyword arguments – Luis Berrocal Jan 04 '20 at 20:43
Python 3.7 and Django 3.0. Which django version are you using? – Hasan Jan 05 '20 at 02:09
Django 2.2.8 python 3.6.8 – Luis Berrocal Jan 06 '20 at 15:02
I tried upgrading my application to Django 3.0 and I have libraries I'm using that do not support it yet. I any one figures how to do it in 2.2 please help me out – Luis Berrocal Jan 09 '20 at 22:38

Django Subquery Expression contains mixed types. You must set output_field

2 Answers2

You don't need to calculate both the credit and non taxable credit