[Django] #30085: Postgres query to Django ORM
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Reporter: keyloguer | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Django ORM, QUERY, | Triage Stage:
Postgresql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/30085#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: keyloguer | Owner: nobody
Component: contrib.postgres | Version: 2.1
Severity: Normal | Resolution: duplicate
Keywords: Django ORM, QUERY, | Triage Stage:
Postgresql | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
* component: Database layer (models, ORM) => contrib.postgres
Comment:
Looks like a duplicate of #29139. The traceback is similar. Feel free to
reopen if it looks like a different issue to you.
--
Ticket URL: <https://code.djangoproject.com/ticket/30085#comment:2>
Django
Reporter: keyloguer | Owner: nobody
Component: contrib.postgres | Version: 2.1
Severity: Normal | Resolution:
Postgresql | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: duplicate =>
Comment:
But the error
continues,[https://github.com/django/django/pull/10240/files], this link
is the PR with the solution, but is not implemented in library as can you
see in source code
[https://github.com/django/django/blob/master/django/contrib/postgres/fields/jsonb.py]
--
Ticket URL: <https://code.djangoproject.com/ticket/30085#comment:3>
Django
Reporter: keyloguer | Owner: nobody
Component: contrib.postgres | Version: 2.1
Severity: Normal | Resolution: duplicate
Keywords: Django ORM, QUERY, | Triage Stage:
Postgresql | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
#29139 is still open. We don't keep multiple tickets open for the same
issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/30085#comment:4>