#30085: Postgres query to Django ORM
-------------------------------------+-------------------------------------
Reporter: keyloguer | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: Django ORM, QUERY,
Severity: Normal | Postgresql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I want to do this query bellow in django orm, i've tried to do with the
queryset example and it doesn't work, always returns the error, i want to
know if is possible to solve this.
I think the problem migth be GROUP BY, and the Django ORM doens't do the
GROUP BY, but this it is just a guess.
error:
{{{
Traceback (most recent call last):
File "C:\Python35\lib\site-
packages\django\core\handlers\exception.py", line 34, in inner
response = get_response(request)
File "C:\Python35\lib\site-packages\django\core\handlers\base.py",
line 126, in _get_response
response = self.process_exception_by_middleware(e, request)
File "C:\Python35\lib\site-packages\django\core\handlers\base.py",
line 124, in _get_response
response = wrapped_callback(request, *callback_args,
**callback_kwargs)
File "C:\Python35\lib\site-
packages\django\views\decorators\csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "C:\Python35\lib\site-packages\rest_framework\viewsets.py",
line 116, in view
return self.dispatch(request, *args, **kwargs)
File "C:\Python35\lib\site-packages\rest_framework\views.py", line
495, in dispatch
response = self.handle_exception(exc)
File "C:\Python35\lib\site-packages\rest_framework\views.py", line
455, in handle_exception
self.raise_uncaught_exception(exc)
File "C:\Python35\lib\site-packages\rest_framework\views.py", line
492, in dispatch
response = handler(request, *args, **kwargs)
File "C:\Python35\lib\site-packages\drf_multiple_model\mixins.py",
line 112, in list
data = query_data['serializer_class'](queryset, many=True,
context=context).data
File "C:\Python35\lib\site-packages\rest_framework\serializers.py",
line 765, in data
ret = super(ListSerializer, self).data
File "C:\Python35\lib\site-packages\rest_framework\serializers.py",
line 262, in data
self._data = self.to_representation(self.instance)
File "C:\Python35\lib\site-packages\rest_framework\serializers.py",
line 683, in to_representation
self.child.to_representation(item) for item in iterable
File "C:\Python35\lib\site-packages\django\db\models\query.py", line
268, in __iter__
self._fetch_all()
File "C:\Python35\lib\site-packages\django\db\models\query.py", line
1186, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "C:\Python35\lib\site-packages\django\db\models\query.py", line
106, in __iter__
for row in compiler.results_iter(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size):
File "C:\Python35\lib\site-
packages\django\db\models\sql\compiler.py", line 1017, in results_iter
results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch,
chunk_size=chunk_size)
File "C:\Python35\lib\site-
packages\django\db\models\sql\compiler.py", line 1052, in execute_sql
sql, params = self.as_sql()
File "C:\Python35\lib\site-
packages\django\db\models\sql\compiler.py", line 449, in as_sql
extra_select, order_by, group_by = self.pre_sql_setup()
File "C:\Python35\lib\site-
packages\django\db\models\sql\compiler.py", line 55, in pre_sql_setup
group_by = self.get_group_by(self.select + extra_select, order_by)
File "C:\Python35\lib\site-
packages\django\db\models\sql\compiler.py", line 130, in get_group_by
if (sql, tuple(params)) not in seen:
TypeError: unhashable type: 'list'
}}}
Postgres query:
{{{
SELECT (Count(jsonb(conversa->'parse_data'->'intent'->'name'))) as
quantidade,
jsonb(conversa->'parse_data'->'intent'->'name') as intent,
to_char(data,'Mon') as mon
FROM conversas
where conversa->'text' is not null and conversa->'parse_data' is not
null
group by intent, mon
}}}
Django ORM:
Repository:
{{{
queryset =
Conversas.objects.extra(select={'mon':"to_char(data,'Mon')"}
).filter(conversa__text__isnull=False,
conversa__parse_data__isnull=False,
data__gte=last_day,
data__lte=today
).annotate(intent=KeyTransform('name',
KeyTransform('intent',
KeyTransform('parse_data','conversa'))),
quantidade=Count(Jsonb(KeyTransform('name',
KeyTransform('intent',
KeyTransform('parse_data','conversa')))))
).values('mon', 'intent','quantidade'
).order_by('mon','intent')
}}}
Jsonb class:
{{{
class Jsonb(Func):
function = 'jsonb'
template = '%(function)s(%(expressions)s)'
arity = 1
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30085>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.9cea6e846b0c09489fea5944315b44c2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
