Django Rest Framework- how to put complex SQL query in views.py

Question

I have some problem with put sql query in Django Rest views.py.

views.py

from rest_framework import generics
from ..models import Stat
from .serializers import StatSerializer

class StatListView(generics.ListAPIView):
    queryset = Stat.objects.raw("SELECT parameter1, COUNT(*) FROM 
               statistic_stat GROUP BY parameter1 order by count(*) desc 
               LIMIT 10")
    serializer_class = StatSerializer

serializers.py

from rest_framework import serializers
from ..models import Stat

class StatSerializer(serializers.ModelSerializer):
    class Meta:
        model = Stat
        fields = ('parameter1', 'parameter2')

When i run this code i get an error like: "get() returned more than one Stat -- it returned 122!"

The same query works in another script (with the same database) and returns the result like this:

[('something1', 56), ('something2', 32), ('something3', 21), ('something4', 
19), ('something5', 10), ('something6', 8), ('something7', 4), ('something8', 
3), ('something9', 2), ('something10', 1)]

Traceback

Environment:


Request Method: GET
Request URL: http://127.0.0.1:8000/api/author/

Django Version: 1.11.3
Python Version: 3.6.1
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'statistic',
 'rest_framework']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\exception.py" in inner
  41.             response = get_response(request)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\core\handlers\base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\views\decorators\csrf.py" in wrapped_view
  58.         return view_func(*args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\views\generic\base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in dispatch
  489.             response = self.handle_exception(exc)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in handle_exception
  449.             self.raise_uncaught_exception(exc)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\views.py" in dispatch
  486.             response = handler(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\generics.py" in get
  201.         return self.list(request, *args, **kwargs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\mixins.py" in list
  48.         return Response(serializer.data)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in data
  739.         ret = super(ListSerializer, self).data

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in data
  263.                 self._data = self.to_representation(self.instance)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in to_representation
  657.             self.child.to_representation(item) for item in iterable

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in <listcomp>
  657.             self.child.to_representation(item) for item in iterable

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\serializers.py" in to_representation
  488.                 attribute = field.get_attribute(instance)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\fields.py" in get_attribute
  445.             return get_attribute(instance, self.source_attrs)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\rest_framework\fields.py" in get_attribute
  104.                 instance = getattr(instance, attr)

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\query_utils.py" in __get__
  116.                 instance.refresh_from_db(fields=[self.field_name])

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\base.py" in refresh_from_db
  696.         db_instance = db_instance_qs.get()

File "C:\Users\310041180\AppData\Local\Continuum\Miniconda3\lib\site-packages\django\db\models\query.py" in get
  384.             (self.model._meta.object_name, num)

Exception Type: MultipleObjectsReturned at /api/author/
Exception Value: get() returned more than one Stat -- it returned 877!

Any ideas or suggestions what is wrong ? Thank you in advance

Answer 1

Not sure if this is the proper answer since I can't reproduce the error you got, but this works for me;

from rest_framework.generics import ListAPIView
from rest_framework import serializers

# you're not serializing a model and since you want to display that count I guess, I'd suggest changing the serializer to this.
class StatSerializer(serializers.Serializer):
    parameter1 = serializers.CharField(max_length=128, required=True)
    count = serializers.integerField(required=True)

    class Meta:
        fields = ('parameter1', 'count')

class StatListView(ListAPIView):
    queryset = Stat.objects.raw("SELECT parameter1, COUNT(*) FROM 
               statistic_stat GROUP BY parameter1 order by count(*) desc 
               LIMIT 10")
    serializer_class = StatSerializer

    def list(self, request):
        queryset = self.get_queryset()
        # the serializer didn't take my RawQuerySet, so made it into a list
        serializer = StatSerializer(list(queryset), many=True)
        return Response(serializer.data)