Opened 2 months ago

Last modified 7 weeks ago

#28621 new Bug

Crash in QuerySet.annotate() with OuterRef

Reported by: Дилян Палаузов Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

class A(models.Model):
  tag = models.CharField()

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?

Change History (3)

comment:1 Changed 2 months ago by Simon Charette

Resolution: invalid
Status: newclosed

I think you can use the Exists expression available since Django 1.11

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.

comment:2 Changed 2 months ago by Дилян Палаузов

Resolution: invalid
Status: closednew

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'

comment:3 Changed 7 weeks ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: WHERE NOT EXISTS (... ~ ...) without extra() ?Crash in QuerySet.annotate() with OuterRef
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug
Note: See TracTickets for help on using tickets.
Back to Top