class B(models.Model):
pattern = models.CharField()
}}}
A single query that retrieves all A.tag, not matching B.pattern:
{{{
SELECT a.tag FROM app_a as a WHERE NOT EXISTS (SELECT 1 FROM app_b as b
WHERE a.tag ~ b.pattern);
}}}
Is it possibe to write a queryset for this, without .extra and .raw?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
* status: new => closed
* resolution: => invalid
Comment:
I think you can use the `Exists`
[https://docs.djangoproject.com/en/1.11/ref/models/expressions/#exists-
subqueries expression available since Django 1.11]
{{{#!python
from django.db.models import Exists, F, OuterRef
A.objects.annotate(
not_matches=~Exists(
B.objects.annotate(
tag=OuterRef('tag'),
).filter(tag__regex=F('pattern')),
),
).filter(not_matches=True)
}}}
Please re-open if this doesn't work.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:1>
* status: closed => new
* resolution: invalid =>
Comment:
I get error:
{{{
In [7]:
A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True)
---------------------------------------------------------------------------
AttributeError Traceback (most recent call
last)
<ipython-input-7-0a83a61e3d5b> in <module>()
----> 1
A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True)
django/db/models/manager.py in manager_method(self, *args, **kwargs)
83 def create_method(name, method):
84 def manager_method(self, *args, **kwargs):
---> 85 return getattr(self.get_queryset(), name)(*args,
**kwargs)
86 manager_method.__name__ = method.__name__
87 manager_method.__doc__ = method.__doc__
django/db/models/query.py in annotate(self, *args, **kwargs)
946
947 for alias, annotation in clone.query.annotations.items():
--> 948 if alias in annotations and
annotation.contains_aggregate:
949 if clone._fields is None:
950 clone.query.group_by = True
AttributeError: 'ResolvedOuterRef' object has no attribute
'contains_aggregate'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:2>
* type: Uncategorized => Bug
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:3>
* cc: Leo Antunes (added)
Comment:
Another slightly simpler example of the same problem, using a model that
only has a pk:
{{{#!python
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')%2).values('pk')))
}}}
Simply removing "%2" is enough to make it work (though not with the
expected results, of course):
{{{#!python
SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')).values('pk')))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:4>
Comment (by Matthew Schinckel):
The `relabeled_clone` issue is now fixed: I missed that there is also a
`contains_aggregate` part. I'll have a look at that now.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:5>
Comment (by Ben Davis):
I am also getting this error. Is there a workaround?
`AttributeError: 'ResolvedOuterRef' object has no attribute
'contains_aggregate'`
My query looks like this:
{{{#!python
class ProgramQuerySet(models.QuerySet):
def with_remaining_volunteers_needed(self):
sessions_query = (
Session.objects
.filter(program=OuterRef('id'))
.annotate(needed=OuterRef('volunteers_per_session') -
Count('volunteer_attendances'))
.order_by('-needed')
.values('needed')[:1]
)
return self.annotate(
volunteers_per_session=(F('num_student_seats') *
F('student_volunteer_ratio')),
needed=Subquery(sessions_query,
output_field=models.IntegerField())
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:6>
Comment (by Filip Kilibarda):
Replying to [comment:6 Ben Davis]:
> I am also getting this error. Is there a workaround?
>
> `AttributeError: 'ResolvedOuterRef' object has no attribute
'contains_aggregate'`
>
> My query looks like this:
> {{{#!python
> class ProgramQuerySet(models.QuerySet):
> def with_remaining_volunteers_needed(self):
> sessions_query = (
> Session.objects
> .filter(program=OuterRef('id'))
> .annotate(needed=OuterRef('volunteers_per_session') -
Count('volunteer_attendances'))
> .order_by('-needed')
> .values('needed')[:1]
> )
>
> return self.annotate(
> volunteers_per_session=(F('num_student_seats') *
F('student_volunteer_ratio')),
> needed=Subquery(sessions_query,
output_field=models.IntegerField())
> )
> }}}
I'm having exactly the same issue. Have you found a workaround for this?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:7>
* owner: nobody => felixxm
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:8>
Comment (by Shai Berger):
Replying to [comment:5 Matthew Schinckel]:
> The `relabeled_clone` issue is now fixed: I missed that there is also a
`contains_aggregate` part. I'll have a look at that now.
For anyone else who might be wondering, this refers to #29142
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:9>
* has_patch: 0 => 1
* version: 1.11 => master
Comment:
[https://github.com/django/django/pull/11130 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:10>
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:11>
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"2a431db0f5e91110b4fda05949de1f158a20ec5b" 2a431db]:
{{{
#!CommitTicketReference repository=""
revision="2a431db0f5e91110b4fda05949de1f158a20ec5b"
Fixed #28621 -- Fixed crash of annotations with OuterRef.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:12>
* cc: Dmitry Mugtasimov (added)
* status: closed => new
* resolution: fixed =>
Comment:
{{{
queryset.annotate(
has_role=Exists(
JobTitle.objects.annotate(
title_vector=SearchVector(
OuterRef('title'),
config=SIMPLE_PG_SEARCH_CONFIGURATION)
).filter(
title_vector=SearchQuery(
F('name'),
config=SIMPLE_PG_SEARCH_CONFIGURATION)
)
)
)
}}}
results in
{{{
'ResolvedOuterRef' object has no attribute 'contains_aggregate'
Request Method: GET
Request URL: http://127.0.0.1:8000/admin/jobs/job/
Django Version: 2.2.2
Exception Type: AttributeError
Exception Value:
'ResolvedOuterRef' object has no attribute 'contains_aggregate'
Exception Location: /usr/local/lib/python3.7/site-
packages/django/db/models/expressions.py in <genexpr>, line 213
Python Executable: /usr/local/bin/python
Python Version: 3.7.3
Python Path:
['/code',
'/code',
'/usr/local/lib/python37.zip',
'/usr/local/lib/python3.7',
'/usr/local/lib/python3.7/lib-dynload',
'/usr/local/lib/python3.7/site-packages']
Server time: Mon, 22 Jul 2019 09:22:47 +0000
}}}
{{{
Environment:
Request Method: GET
Request URL: http://127.0.0.1:8000/admin/jobs/job/
Django Version: 2.2.2
Python Version: 3.7.3
Installed Applications:
['core',
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'django.contrib.sites',
'django.contrib.postgres',
'allauth',
'allauth.account',
'drf_yasg',
'rest_auth',
'rest_auth.registration',
'rest_framework',
'rest_framework.authtoken',
'colorful',
'gm2m',
'explorer',
'allauth.socialaccount',
'accounts',
'questions',
'colleges',
'geo',
'jobs',
'advices',
'referral']
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 "/usr/local/lib/python3.7/site-
packages/django/core/handlers/exception.py" in inner
34. response = get_response(request)
File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py"
in _get_response
115. response = self.process_exception_by_middleware(e,
request)
File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py"
in _get_response
113. response = wrapped_callback(request,
*callback_args, **callback_kwargs)
File "/usr/local/lib/python3.7/contextlib.py" in inner
74. return func(*args, **kwds)
File "/usr/local/lib/python3.7/site-
packages/django/contrib/admin/options.py" in wrapper
606. return self.admin_site.admin_view(view)(*args,
**kwargs)
File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py"
in _wrapped_view
142. response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-
packages/django/views/decorators/cache.py" in _wrapped_view_func
44. response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-
packages/django/contrib/admin/sites.py" in inner
223. return view(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py"
in _wrapper
45. return bound_method(*args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py"
in _wrapped_view
142. response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-
packages/django/contrib/admin/options.py" in changelist_view
1672. cl = self.get_changelist_instance(request)
File "/usr/local/lib/python3.7/site-
packages/django/contrib/admin/options.py" in get_changelist_instance
744. sortable_by,
File "/usr/local/lib/python3.7/site-
packages/django/contrib/admin/views/main.py" in __init__
45. self.root_queryset = model_admin.get_queryset(request)
File "/code/jobs/admin.py" in get_queryset
76. return queryset.annotate_with_has_role()
File "/code/jobs/models/job.py" in annotate_with_has_role
68. config=SIMPLE_PG_SEARCH_CONFIGURATION)
File "/usr/local/lib/python3.7/site-packages/django/db/models/manager.py"
in manager_method
82. return getattr(self.get_queryset(), name)(*args,
**kwargs)
File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py" in
annotate
1059. if alias in annotations and
annotation.contains_aggregate:
File "/usr/local/lib/python3.7/site-packages/django/utils/functional.py"
in __get__
80. res = instance.__dict__[self.name] = self.func(instance)
File "/usr/local/lib/python3.7/site-
packages/django/db/models/expressions.py" in contains_aggregate
213. return any(expr and expr.contains_aggregate for expr in
self.get_source_expressions())
File "/usr/local/lib/python3.7/site-
packages/django/db/models/expressions.py" in <genexpr>
213. return any(expr and expr.contains_aggregate for expr in
self.get_source_expressions())
Exception Type: AttributeError at /admin/jobs/job/
Exception Value: 'ResolvedOuterRef' object has no attribute
'contains_aggregate'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:13>
* status: new => closed
* resolution: => fixed
Comment:
Please don't re-open already fixed ticket. This fix doesn't qualify for a
backport, hence it will be solved in Django 2.2.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:14>
Comment (by B Martsberger):
Out of curiosity, why does this fix not qualify for a backport?
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:15>
Comment (by felixxm):
This doesn't qualify for a backport because it is not a regression in the
Django 2.2 or a bug in a new feature.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:16>
Comment (by Dmitry Mugtasimov):
The fix does not qualify for backport because the code where it appears
was heavily refactored somewhere between 1.11 and 2.2. However the bug
remained in 2.2 and the bug qualifies to get fixed. The fix is already
implemented, but not released yet. This is how I understood it.
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:17>
Comment (by Dmitry Mugtasimov):
Here is explanation for humans:
https://code.djangoproject.com/ticket/30652#comment:7
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:18>
Comment (by GitHub <noreply@…>):
In [changeset:"ed6b14d4591e536985222b61cb8b83908d58140d" ed6b14d4]:
{{{
#!CommitTicketReference repository=""
revision="ed6b14d4591e536985222b61cb8b83908d58140d"
Refs #28621 -- Fixed crash of annotations with nested OuterRef.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28621#comment:19>