Aggregate functions are not allowed in this query

September 22, 2017, 11:19 am

Got burnt today by an odd error thrown by the Django ORM when writing some tests. It was an easy mistake to make and the exception "Aggregate functions are not allowed in this query" has lots of unrelated Google juice and was not helpful in getting to the bottom of the problem. Hence this post.

Start with a pretty simple model that we are going to be updating via an API.

1
2
3

class Foo(models.Model):
    name = models.CharField(max_length=512)
    value = models.CharField(max_length=512)

Then write some code to hit an API via requests and update the database with a new value.

import requests
from .models import Foo


def make_request():

    # go get foo from your api
    resp = requests.get("http://your.thing/api/foo/name")

    # the name and value of the foo?
    value = resp.json()["value"]

    # Grab the instance of foo corresponding to name
    # and update its value
    foo = Foo.objects.get(name="name")
    foo.value = value
    foo.save()

Then a test using a patch on yourthing.helpers.requests so we don't hit the real API when we run our test. Let's pretend that make_requets has some side effect that we are testing for rather than the value stored in the Foo table. Given that assumption we might not mock the actual values returned by the API (see THIS --->).

from mock import Mock, patch

from django.test import TestCase

from .models import Foo
from .helpers import make_request


class MockTestCase(TestCase):

    @patch('yourthing.helpers.requests')
    def test_an_api(self, mock_requests):

        # Create the instance we are going to update
        Foo.objects.create(name="name", value=0)

        # THIS ---> mock_requests.get.return_value.json.return_value = { "value" : 1}
        make_request()

        # Test for something unrelated to the contents of the Foo table. Perhaps some other
        # side effect of the make_requests() call.

Run this test and you will get this error.

======================================================================
ERROR: test_an_api (yourthing.test_helpers.MockTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/mock/mock.py", line 1305, in patched
return func(*args, **keywargs)
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/test_helpers.py", line 18, in test_an_api
make_request()
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/helpers.py", line 17, in make_request
foo.save()
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 807, in save
force_update=force_update, update_fields=update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 837, in save_base
updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 904, in _save_table
forced_update)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/base.py", line 954, in _do_update
return filtered._update(values) > 0
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/query.py", line 664, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1191, in execute_sql
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 863, in execute_sql
sql, params = self.as_sql()
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1143, in as_sql
raise FieldError("Aggregate functions are not allowed in this query")
django.core.exceptions.FieldError: Aggregate functions are not allowed in this query
----------------------------------------------------------------------

This unhelpful error might lead us to examine queries & relationship looking for an aggregate function. In fact there is no aggregate function. Instead this is happening because without an explict mock the resp.json()["value"] call in make_request will return a MagicMock instead of a string. When the ORM tries to save the instance it gets into trouble dealing with a field that will mock any property it tries to access.

1	<MagicMock name='requests.get().json().__getitem__()' id='4385055128'>

So I guess the moral of the story is to make sure you mock every response that ends up being passed to a Django ORM model instance. If you don't you might spent a chunk of time investigating rabbit holes as I did.

Permalink - Tags: Development,Django

Aggregate functions are not allowed in this query

Tags

Archive