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)