def test_order_by_exists(self):
author_without_posts = Author.objects.create(name="other author")
authors_by_posts = Author.objects.order_by(Exists(Post.objects.filter(author=OuterRef('pk'))).desc())
self.assertSequenceEqual(authors_by_posts, [self.author, author_without_posts])
authors_by_posts = Author.objects.order_by(Exists(Post.objects.filter(author=OuterRef('pk'))).asc())
self.assertSequenceEqual(authors_by_posts, [author_without_posts, self.author])
def effective_permissions(self) -> "QuerySet[Permission]":
if self._effective_permissions is None:
self._effective_permissions = get_permissions()
if not self.is_superuser:
UserPermission = User.user_permissions.through
user_permission_queryset = UserPermission.objects.filter(
user_id=self.pk).values("permission_id")
UserGroup = User.groups.through
GroupPermission = Group.permissions.through
user_group_queryset = UserGroup.objects.filter(
user_id=self.pk).values("group_id")
group_permission_queryset = GroupPermission.objects.filter(
Exists(
user_group_queryset.filter(group_id=OuterRef(
"group_id")))).values("permission_id")
self._effective_permissions = self._effective_permissions.filter(
Q(
Exists(
user_permission_queryset.filter(
permission_id=OuterRef("pk"))))
| Q(
Exists(
group_permission_queryset.filter(
permission_id=OuterRef("pk")))))
return self._effective_permissions
def test_exact_exists(self):
qs = Article.objects.filter(pk=OuterRef('pk'))
seasons = Season.objects.annotate(
pk_exists=Exists(qs),
).filter(
pk_exists=Exists(qs),
)
self.assertCountEqual(seasons, Season.objects.all())
def test_nested_outerref_lhs(self):
tag = Tag.objects.create(name=self.au1.alias)
tag.articles.add(self.a1)
qs = Tag.objects.annotate(has_author_alias_match=Exists(
Article.objects.annotate(author_exists=Exists(
Author.objects.filter(
alias=OuterRef(OuterRef('name')))), ).filter(
author_exists=True)), )
self.assertEqual(qs.get(has_author_alias_match=True), tag)
def get_gift_card_lines(line_pks: Iterable[int]):
product_types = ProductType.objects.filter(kind=ProductTypeKind.GIFT_CARD)
products = Product.objects.filter(
Exists(product_types.filter(pk=OuterRef("product_type_id"))))
variants = ProductVariant.objects.filter(
Exists(products.filter(pk=OuterRef("product_id"))))
gift_card_lines = OrderLine.objects.filter(id__in=line_pks).filter(
Exists(variants.filter(pk=OuterRef("variant_id"))))
return gift_card_lines
def _retrieve_people(self, filter: RetentionFilter, team: Team):
period = filter.period
trunc, fields = self._get_trunc_func("timestamp", period)
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
entity_condition, _ = self.get_entity_condition(
filter.target_entity, "events")
returning_condition, _ = self.get_entity_condition(
filter.returning_entity, "first_event_date")
_entity_condition = returning_condition if filter.selected_interval > 0 else entity_condition
events = Event.objects.filter(team_id=team.pk).add_person_id(team.pk)
filtered_events = events.filter(
filter.recurring_date_filter_Q()).filter(
properties_to_Q(filter.properties, team_id=team.pk))
inner_events = (Event.objects.filter(team_id=team.pk).filter(
properties_to_Q(filter.properties, team_id=team.pk)).add_person_id(
team.pk).filter(**{
"person_id": OuterRef("id")
}).filter(entity_condition).values("person_id").annotate(
first_date=Min(trunc)).filter(
filter.reference_date_filter_Q("first_date")).distinct(
) if is_first_time_retention else Event.objects.filter(
team_id=team.pk).filter(
filter.reference_date_filter_Q()).filter(
properties_to_Q(
filter.properties,
team_id=team.pk)).add_person_id(
team.pk).filter(
**{
"person_id": OuterRef("id")
}).filter(entity_condition))
filtered_events = (filtered_events.filter(_entity_condition).filter(
Exists(
Person.objects.filter(**{
"id": OuterRef("person_id"),
}).filter(Exists(inner_events)).only("id"))).values(
"person_id").distinct()).all()
people = Person.objects.filter(
team=team,
id__in=[
p["person_id"]
for p in filtered_events[filter.offset:filter.offset + 100]
],
)
people = people.prefetch_related(
Prefetch("persondistinctid_set", to_attr="distinct_ids_cache"))
from posthog.api.person import PersonSerializer
return PersonSerializer(people, many=True).data
def recipes_in_bookmarks(user, recipes):
bookmarks_subquery = Bookmark.objects.filter(recipe=OuterRef("pk"),
user__pk=user.pk)
in_bookmarks = dict(
recipes.annotate(in_bookmarks=Exists(bookmarks_subquery)).values_list(
"pk", "in_bookmarks"))
return {"bookmarks": in_bookmarks}
def with_is_favorite(self, user_id: Optional[int]):
return self.annotate(is_favorite=Exists(
Favorite.objects.filter(
user_id=user_id,
recipe_id=OuterRef('pk'),
),
))
def test_attname_should_not_mask_col_name(self):
def make_regions_cte(cte):
return Region.objects.filter(
name="moon"
).values(
"name",
"parent_id",
).union(
cte.join(Region, name=cte.col.parent_id).values(
"name",
"parent_id",
),
all=True,
)
cte = With.recursive(make_regions_cte)
regions = (
Region.objects.all()
.with_cte(cte)
.annotate(_ex=Exists(
cte.queryset()
.values(value=Value("1", output_field=int_field))
.filter(name=OuterRef("name"))
))
.filter(_ex=True)
.order_by("name")
)
print(regions.query)
data = [r.name for r in regions]
self.assertEqual(data, ['earth', 'moon', 'sun'])
def annotate_documentations(klass: Union[Event, LessonPeriod, ExtraLesson],
wanted_week: CalendarWeek,
pks: List[int]) -> QuerySet:
"""Return an annotated queryset of all provided register objects."""
if isinstance(klass, LessonPeriod):
prefetch = Prefetch(
"documentations",
queryset=LessonDocumentation.objects.filter(week=wanted_week.week,
year=wanted_week.year),
)
else:
prefetch = Prefetch("documentations")
instances = klass.objects.prefetch_related(prefetch).filter(pk__in=pks)
if klass == LessonPeriod:
instances = instances.annotate_week(wanted_week)
elif klass in (LessonPeriod, ExtraLesson):
instances = instances.order_by("period__weekday", "period__period")
else:
instances = instances.order_by("period_from__weekday",
"period_from__period")
instances = instances.annotate(has_documentation=Exists(
LessonDocumentation.objects.filter(
~Q(topic__exact=""),
week=wanted_week.week,
year=wanted_week.year,
).filter(**{klass.label_: OuterRef("pk")})))
return instances
def extend_queryset(self, queryset, preview_comments=False):
"""
adds post owner
annotates number of likes and if post is liked by the user
prefetches first N comments if required
"""
queryset = (queryset.select_related('owner').annotate(
nlikes=Count('likes'),
is_liked_by_me=Exists(
Like.objects.filter(
post_id=OuterRef('id'),
owner_id=self.request.user.id,
))))
if preview_comments:
enumerated_comments_cte = With(
Comment.objects.annotate(row_number=Window(
expression=RowNumber(),
partition_by=[F('post_id')],
order_by=[F('date_created').desc(),
F('id').desc()])))
queryset = queryset.prefetch_related(
Prefetch(
'comments',
queryset=(enumerated_comments_cte.queryset().with_cte(
enumerated_comments_cte).select_related(
'owner').filter(row_number__lte=settings.
NUM_OF_PREVIEW_COMMENTS).order_by(
'date_created', 'id')),
to_attr='preview_comments'))
return queryset
def validate(self, model, instance, exclude=None, using=DEFAULT_DB_ALIAS):
queryset = model._default_manager.using(using)
if self.fields:
lookup_kwargs = {}
for field_name in self.fields:
if exclude and field_name in exclude:
return
field = model._meta.get_field(field_name)
lookup_value = getattr(instance, field.attname)
if lookup_value is None or (
lookup_value == ""
and connections[using].features.interprets_empty_strings_as_nulls
):
# A composite constraint containing NULL value cannot cause
# a violation since NULL != NULL in SQL.
return
lookup_kwargs[field.name] = lookup_value
queryset = queryset.filter(**lookup_kwargs)
else:
# Ignore constraints with excluded fields.
if exclude:
for expression in self.expressions:
for expr in expression.flatten():
if isinstance(expr, F) and expr.name in exclude:
return
replacement_map = instance._get_field_value_map(
meta=model._meta, exclude=exclude
)
expressions = [
Exact(expr, expr.replace_references(replacement_map))
for expr in self.expressions
]
queryset = queryset.filter(*expressions)
model_class_pk = instance._get_pk_val(model._meta)
if not instance._state.adding and model_class_pk is not None:
queryset = queryset.exclude(pk=model_class_pk)
if not self.condition:
if queryset.exists():
if self.expressions:
raise ValidationError(self.get_violation_error_message())
# When fields are defined, use the unique_error_message() for
# backward compatibility.
for model, constraints in instance.get_constraints():
for constraint in constraints:
if constraint is self:
raise ValidationError(
instance.unique_error_message(model, self.fields)
)
else:
against = instance._get_field_value_map(meta=model._meta, exclude=exclude)
try:
if (self.condition & Exists(queryset.filter(self.condition))).check(
against, using=using
):
raise ValidationError(self.get_violation_error_message())
except FieldError:
pass
def test_with_case_when(self):
author = Author.objects.annotate(
has_post=Case(
When(Exists(Post.objects.filter(author=OuterRef('pk')).values('pk')), then=Value(1)),
default=Value(0),
output_field=IntegerField(),
)
).get()
self.assertEqual(author.has_post, 1)
def annotate_site_root_state(self):
"""
Performance optimisation for listing pages.
Annotates each object with whether it is a root page of any site.
Used by `is_site_root` method on `wagtailcore.models.Page`.
"""
return self.annotate(_is_site_root=Exists(
Site.objects.filter(
root_page__translation_key=OuterRef("translation_key"))))
def proyecto_abierto(self, request):
literales_abiertos = Literal.objects.using('read_only').filter(
proyecto_id=OuterRef('proyecto_id'), abierto=True)
lista = self.queryset.using('read_only').annotate(
proyecto_con_literales_abierto=Exists(literales_abiertos)).filter(
proyecto__abierto=True,
proyecto_con_literales_abierto=True).all()
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
def consulta_usando_exists():
editoriales = Editorial.objects.values('id')
editoriales = editoriales.filter(id=OuterRef('editorial_id'))
editoriales = editoriales.annotate(cantidad=Count('libro_editorial__isbn'))
editoriales = editoriales.filter(cantidad__gte=40)
libros = Libro.objects.annotate(cant=Exists(editoriales)).filter(
cant=True) # ~Exists
libros = libros.values('isbn', 'editorial', 'cant')
return libros
def deactivate_order_gift_cards(order_id: int, user: Optional["User"],
app: Optional["App"]):
gift_card_events = GiftCardEvent.objects.filter(type=GiftCardEvents.BOUGHT,
order_id=order_id)
gift_cards = GiftCard.objects.filter(
Exists(gift_card_events.filter(gift_card_id=OuterRef("id"))))
gift_cards.update(is_active=False)
events.gift_cards_deactivated_event(
gift_cards.values_list("id", flat=True), user, app)
def get_queryset(self) -> QuerySet[Achievement]:
assert isinstance(self.request.user, User)
return Achievement.objects.filter(active=True).annotate(
num_found=SubqueryAggregate('achievementunlock', aggregate=Count),
obtained=Exists(
Achievement.objects.filter(
pk=OuterRef('pk'), achievementunlock__user=self.request.user
)
),
).order_by('-obtained', '-num_found')
def test_q_annotation(self):
query = Query(None)
check = ExpressionWrapper(
Q(RawSQL("%s IS NULL", (None, ), BooleanField()))
| Q(Exists(Item.objects.all())),
BooleanField(),
)
query.add_annotation(check, "_check")
result = query.get_compiler(using=DEFAULT_DB_ALIAS).execute_sql(SINGLE)
self.assertEqual(result[0], 1)
def test_contains_subquery(self):
IntegerArrayModel.objects.create(field=[2, 3])
inner_qs = IntegerArrayModel.objects.values_list("field", flat=True)
self.assertSequenceEqual(
NullableIntegerArrayModel.objects.filter(field__contains=inner_qs[:1]),
self.objs[2:3],
)
inner_qs = IntegerArrayModel.objects.filter(field__contains=OuterRef("field"))
self.assertSequenceEqual(
NullableIntegerArrayModel.objects.filter(Exists(inner_qs)),
self.objs[1:3],
)
def ready_to_capture(self):
"""Return orders with payments to capture.
Orders ready to capture are those which are not draft or canceled and
have a preauthorized payment. The preauthorized payment can not
already be partially or fully captured.
"""
payments = Payment.objects.filter(
is_active=True,
charge_status=ChargeStatus.NOT_CHARGED).values("id")
qs = self.filter(Exists(payments.filter(order_id=OuterRef("id"))))
return qs.exclude(status={OrderStatus.DRAFT, OrderStatus.CANCELED})
def consulta_usando_exists_y_subquery():
editoriales = Editorial.objects.values('id')
editoriales = editoriales.filter(id=OuterRef('editorial_id'))
editoriales = editoriales.annotate(cantidad=Count('libro_editorial__isbn'))
editoriales = editoriales.filter(cantidad__gte=40)
libros = Libro.objects.annotate(cant=Exists(editoriales)).annotate(
cant_subq=Subquery(editoriales.values('cantidad'),
output_field=IntegerField()))
libros = libros.filter(cant=True)
libros = libros.values('isbn', 'editorial', 'cant_subq')
return libros
def test_group_by_exists_annotation(self):
"""
Exists annotations are included in the GROUP BY if they are
grouped against.
"""
long_books_qs = Book.objects.filter(
publisher=OuterRef('pk'),
pages__gt=800,
)
has_long_books_breakdown = Publisher.objects.values_list(
Exists(long_books_qs), ).annotate(total=Count('*'))
self.assertEqual(dict(has_long_books_breakdown), {True: 2, False: 3})
def annotate_approved_schedule(self):
"""
Performance optimisation for listing pages.
Annotates each page with the existence of an approved go live time.
Used by `approved_schedule` property on `wagtailcore.models.Page`.
"""
from .models import Revision
return self.annotate(_approved_schedule=Exists(
Revision.page_revisions.exclude(
approved_go_live_at__isnull=True).filter(
object_id=Cast(OuterRef("pk"), output_field=CharField()))))
def annotate_approved_schedule(self):
"""
Performance optimisation for listing pages.
Annotates each page with the existence of an approved go live time.
Used by `approved_schedule` property on `wagtailcore.models.Page`.
"""
from .models import PageRevision
return self.annotate(_approved_schedule=Exists(
PageRevision.objects.exclude(
approved_go_live_at__isnull=True).filter(
page__pk=OuterRef("pk"))))
def test_aggregation_exists_annotation(self):
published_books = Book.objects.filter(publisher=OuterRef('pk'))
publisher_qs = Publisher.objects.annotate(
published_book=Exists(published_books),
count=Count('book'),
).values_list('name', flat=True)
self.assertCountEqual(list(publisher_qs), [
'Apress',
'Morgan Kaufmann',
"Jonno's House of Books",
'Prentice Hall',
'Sams',
])
def get_viewable(self, user):
"""You may view other users in your organizations and projects,
and anybody with a public document
"""
from documentcloud.documents.models import Document
if user.is_authenticated:
# unions are much more performant than complex conditions
return self.annotate(public=Exists(
Document.objects.filter(user=OuterRef("pk"),
access=Access.public).values("pk")
)).filter(
Q(pk__in=self.filter(organizations__in=user.organizations.all(
)).order_by().values("pk").union(
self.filter(projects__in=user.projects.all()).order_by().
values("pk")))
| Q(public=True))
else:
return self.annotate(public=Exists(
Document.objects.filter(user=OuterRef("pk"),
access=Access.public).values(
"pk"))).filter(public=True)
def finish(self):
"""For every subquery, construct a filter to make sure the result set is non-empty"""
if len(self.variables) == 0:
return self.filter
objects = self.model.objects.all()
for subq in self.variables.values():
filtered = subq.model.objects.filter(subq.finish()).values("pk")
exists = Exists(filtered)
name = f"{self.name}__exists"
# https://docs.djangoproject.com/en/2.2/ref/models/expressions/#filtering-on-a-subquery-expression
objects = objects.annotate(**{name: exists}).filter(**{name: True})
self.filter &= Q(pk__in=objects.values("pk"))
return self.filter
def ready_to_fulfill(self):
"""Return orders that can be fulfilled.
Orders ready to fulfill are fully paid but unfulfilled (or partially
fulfilled).
"""
statuses = {OrderStatus.UNFULFILLED, OrderStatus.PARTIALLY_FULFILLED}
payments = Payment.objects.filter(is_active=True).values("id")
return self.filter(
Exists(payments.filter(order_id=OuterRef("id"))),
status__in=statuses,
total_gross_amount__lte=F("total_paid_amount"),
)
def set_is_gift_card_field(apps, schema_editor):
OrderLine = apps.get_model("order", "OrderLine")
ProductType = apps.get_model("product", "ProductType")
Product = apps.get_model("product", "Product")
ProductVariant = apps.get_model("product", "ProductVariant")
product_types = ProductType.objects.filter(kind="gift_card")
products = Product.objects.filter(
Exists(product_types.filter(pk=OuterRef("product_type_id")))
)
variants = ProductVariant.objects.filter(
Exists(products.filter(pk=OuterRef("product_id")))
)
gift_card_lines = OrderLine.objects.filter(
Exists(variants.filter(pk=OuterRef("variant_id")))
)
gift_card_lines.update(is_gift_card=True)
lines = OrderLine.objects.exclude(
Exists(variants.filter(pk=OuterRef("variant_id")))
)
lines.update(is_gift_card=False)