Ejemplo n.º 1
0
 def qs_with_price(queryset: QuerySet, channel_slug: str) -> QuerySet:
     return queryset.annotate(
         min_variants_price_amount=Min(
             "variants__channel_listings__price_amount",
             filter=Q(variants__channel_listings__channel__slug=str(channel_slug))
             & Q(variants__channel_listings__price_amount__isnull=False),
         )
     )
Ejemplo n.º 2
0
 def qs_with_published(queryset: QuerySet, channel_slug: str) -> QuerySet:
     validate_channel_slug(channel_slug)
     subquery = Subquery(
         ProductChannelListing.objects.filter(
             product_id=OuterRef("pk"),
             channel__slug=channel_slug).values_list("is_published")[:1])
     return queryset.annotate(is_published=ExpressionWrapper(
         subquery, output_field=BooleanField()))
Ejemplo n.º 3
0
 def qs_with_availability(queryset: QuerySet,
                          channel_slug: str) -> QuerySet:
     subquery = Subquery(
         CollectionChannelListing.objects.filter(
             collection_id=OuterRef("pk"), channel__slug=str(
                 channel_slug)).values_list("is_published")[:1])
     return queryset.annotate(is_published=ExpressionWrapper(
         subquery, output_field=BooleanField()))
Ejemplo n.º 4
0
 def qs_with_published_at(queryset: QuerySet,
                          channel_slug: str) -> QuerySet:
     subquery = Subquery(
         ProductChannelListing.objects.filter(
             product_id=OuterRef("pk"), channel__slug=str(
                 channel_slug)).values_list("published_at")[:1])
     return queryset.annotate(published_at=ExpressionWrapper(
         subquery, output_field=DateTimeField()))
Ejemplo n.º 5
0
 def annotate(queryset: QuerySet) -> QuerySet:
     return queryset.annotate(payloadExtern=Case(
         When(datetime_started__isnull=False,
              then=Subquery(
                  TaskResult.objects.filter(
                      task_id=OuterRef('task')).values('result'))),
         output_field=TextField(),
     ), )
Ejemplo n.º 6
0
 def qs_with_publication_date(queryset: QuerySet,
                              channel_slug: str) -> QuerySet:
     subquery = Subquery(
         CollectionChannelListing.objects.filter(
             collection_id=OuterRef("pk"), channel__slug=str(
                 channel_slug)).values_list("publication_date")[:1])
     return queryset.annotate(publication_date=ExpressionWrapper(
         subquery, output_field=DateField()))
Ejemplo n.º 7
0
 def qs_with_collection(queryset: QuerySet, **_kwargs) -> QuerySet:
     return queryset.annotate(sort_order=Window(
         expression=DenseRank(),
         order_by=(
             F("collectionproduct__sort_order").asc(nulls_last=True),
             F("collectionproduct__id"),
         ),
     ))
Ejemplo n.º 8
0
def graph_content_search(queryset: QuerySet, search_text: str) -> QuerySet:
    return queryset.annotate(
        search=SearchVector(
            *(part % trans_type
              for trans_type in graph_info_translation_tables
              for part in graph_content_search_vector_parts),
            *content_search_vector_attrs
        )
    ).filter(search=search_text.split(' ,."\'\\/[]{}!@#$%^&*()<>?:|-_=+`~'))
Ejemplo n.º 9
0
    def annotate(queryset: QuerySet) -> QuerySet:
        queryset = Manager_Batches.annotate_assignments(queryset)

        return queryset.annotate(
            costs_max=F('count_assignments_total') *
            F('settings_batch__reward'),
            costs_so_far=F('count_assignments_approved') *
            F('settings_batch__reward'),
        )
Ejemplo n.º 10
0
def process_math(query: QuerySet, entity: Entity) -> QuerySet:
    if entity.math == "dau":
        # In daily active users mode count only up to 1 event per user per day
        query = query.annotate(count=Count("person_id", distinct=True))
    elif entity.math in MATH_TO_AGGREGATE_FUNCTION:
        # Run relevant aggregate function on specified event property, casting it to a double
        query = query.annotate(
            count=MATH_TO_AGGREGATE_FUNCTION[entity.math](
                Cast(RawSQL('"posthog_event"."properties"->>%s', (entity.math_property,)), output_field=FloatField(),)
            )
        )
        # Skip over events where the specified property is not set or not a number
        # It may not be ideally clear to the user what events were skipped,
        # but in the absence of typing, this is safe, cheap, and frictionless
        query = query.extra(
            where=['jsonb_typeof("posthog_event"."properties"->%s) = \'number\''], params=[entity.math_property],
        )
    return query
Ejemplo n.º 11
0
 def qs_with_product_count(queryset: QuerySet) -> QuerySet:
     return queryset.annotate(product_count=Coalesce(
         Subquery(
             Category.tree.add_related_count(
                 queryset, Product, "category", "p_c", cumulative=True).
             values("p_c").filter(pk=OuterRef("pk"))[:1]),
         0,
         output_field=IntegerField(),
     ))
Ejemplo n.º 12
0
 def search_by_description(adverts: QuerySet, search_text: str) -> QuerySet:
     if search_text and search_text != "None":
         vector = SearchVector("description")
         query = SearchQuery(search_text)
         adverts = (adverts.annotate(
             search=vector, rank=SearchRank(
                 vector,
                 query)).filter(search=search_text).order_by("-rank"))
     return adverts
Ejemplo n.º 13
0
 def qs_with_payment(queryset: QuerySet) -> QuerySet:
     subquery = Subquery(
         Payment.objects.filter(order_id=OuterRef("pk"))
         .order_by("-pk")
         .values_list("charge_status")[:1]
     )
     return queryset.annotate(
         last_charge_status=ExpressionWrapper(subquery, output_field=CharField())
     )
Ejemplo n.º 14
0
def graph_content_search(queryset: QuerySet, search_text: str) -> QuerySet:
    return queryset.annotate(
        search=SearchVector(
            *(part % trans_type
              for trans_type in graph_info_translation_tables
              for part in graph_content_search_vector_parts),
            *content_search_vector_attrs
        )
    ).filter(search=search_text)
Ejemplo n.º 15
0
def annotate_groups_restricted(groups: QuerySet, job):
    unrestricted_users = get_unrestricted_users(User.objects.all(), job)
    unrestricted_groups = groups.filter(
        Q(group_permissions__user__in=unrestricted_users)
        | Q(group_permissions__user__isnull=True))
    groups = groups.annotate(
        restricted=Case(When(id__in=unrestricted_groups, then=False),
                        default=Value(True),
                        output_field=models.BooleanField()))
    return groups
Ejemplo n.º 16
0
 def annotate_usages_price(self, subscriptions: QuerySet) -> QuerySet:
     subscription_model = subscriptions.model
     data_usage_price = subscriptions.annotate(
         data_usage_price=Cast(
             Sum('datausagerecord__kilobytes_used') * subscription_model.ONE_KILOBYTE_PRICE,
             DecimalField()
         )
     )
     voice_usage_price = subscriptions.annotate(
         voice_usage_price=Cast(
             Sum('voiceusagerecord__seconds_used') * subscription_model.ONE_SECOND_PRICE,
             DecimalField()
         )
     )
     subscriptions = subscriptions.annotate(
         data_usage_price=Subquery(data_usage_price.values('data_usage_price'), output_field=DecimalField()),
         voice_usage_price=Subquery(voice_usage_price.values('voice_usage_price'), output_field=DecimalField())
     )
     return subscriptions
Ejemplo n.º 17
0
def aggregate_by_interval(
    filtered_events: QuerySet,
    team_id: int,
    entity: Entity,
    filter: Filter,
    interval: str,
    params: dict,
    breakdown: Optional[str] = None,
) -> Dict[str, Any]:
    interval_annotation = get_interval_annotation(interval)
    values = [interval]
    if breakdown:
        breakdown_type = params.get("breakdown_type")
        if breakdown_type == "cohort":
            cohort_annotations = add_cohort_annotations(
                team_id, json.loads(params.get("breakdown", "[]")))
            values.extend(cohort_annotations.keys())
            filtered_events = filtered_events.annotate(**cohort_annotations)
            breakdown = "cohorts"
        elif breakdown_type == "person":
            person_annotations = add_person_properties_annotations(
                team_id, params.get("breakdown", ""))
            filtered_events = filtered_events.annotate(**person_annotations)
            values.append(breakdown)
        else:
            values.append(breakdown)
    aggregates = (filtered_events.annotate(**interval_annotation).values(
        *values).annotate(count=Count(1)).order_by())

    if breakdown:
        aggregates = aggregates.order_by("-count")

    aggregates = process_math(aggregates, entity)

    dates_filled = group_events_to_date(
        date_from=filter.date_from,
        date_to=filter.date_to,
        aggregates=aggregates,
        interval=interval,
        breakdown=breakdown,
    )

    return dates_filled
Ejemplo n.º 18
0
def aggregate_by_interval(
    filtered_events: QuerySet,
    team_id: int,
    entity: Entity,
    filter: Filter,
    breakdown: Optional[str] = None,
) -> Dict[str, Any]:
    interval = filter.interval if filter.interval else "day"
    interval_annotation = get_interval_annotation(interval)
    values = [interval]
    if breakdown:
        if filter.breakdown_type == "cohort":
            cohort_annotations = add_cohort_annotations(
                team_id, filter.breakdown if filter.breakdown
                and isinstance(filter.breakdown, list) else [])
            values.extend(cohort_annotations.keys())
            filtered_events = filtered_events.annotate(**cohort_annotations)
            breakdown = "cohorts"
        elif filter.breakdown_type == "person":
            person_annotations = add_person_properties_annotations(
                team_id, filter.breakdown if filter.breakdown
                and isinstance(filter.breakdown, str) else "")
            filtered_events = filtered_events.annotate(**person_annotations)
            values.append(breakdown)
        else:
            values.append(breakdown)
    aggregates = filtered_events.annotate(**interval_annotation).values(
        *values).annotate(count=Count(1)).order_by()

    if breakdown:
        aggregates = aggregates.order_by("-count")

    aggregates = process_math(aggregates, entity)

    dates_filled = group_events_to_date(
        date_from=filter.date_from,
        date_to=filter.date_to,
        aggregates=aggregates,
        interval=interval,
        breakdown=breakdown,
    )

    return dates_filled
Ejemplo n.º 19
0
 def base_currency_latest_values_filter(queryset: QuerySet, name: str,
                                        value: str) -> QuerySet:
     """
     Returns a queryset of latest valeus for a base currency
     """
     queryset = queryset.filter(base_currency=value)
     latest = queryset.filter(
         base_currency=OuterRef('base_currency')).order_by('-value_date')
     return queryset.annotate(base_currency_latest=Subquery(
         latest.values('value_date')[:1])).filter(
             value_date=models.F('base_currency_latest'))
Ejemplo n.º 20
0
def get_actions(queryset: QuerySet, params: dict, team_id: int) -> QuerySet:
    if params.get(TREND_FILTER_TYPE_ACTIONS):
        queryset = queryset.filter(
            pk__in=[action.id for action in Filter({"actions": json.loads(params.get("actions", "[]"))}).actions]
        )

    if params.get("include_count"):
        queryset = queryset.annotate(count=Count(TREND_FILTER_TYPE_EVENTS))

    queryset = queryset.prefetch_related(Prefetch("steps", queryset=ActionStep.objects.order_by("id")))
    return queryset.filter(team_id=team_id).order_by("-id")
Ejemplo n.º 21
0
 def with_subtask_verification_time(cls, query_set: QuerySet) -> QuerySet:
     task_to_compute_timestamp = Func(Value('epoch'),
                                      F('task_to_compute__timestamp'),
                                      function='DATE_PART')
     subtask_timeout = Func(
         Value('epoch'), F('computation_deadline'),
         function='DATE_PART') - task_to_compute_timestamp
     subtask_verification_time = (4 * settings.CONCENT_MESSAGING_TIME) + (
         3 * F('maximum_download_time')) + (0.5 * subtask_timeout)
     return query_set.annotate(subtask_verification_time=ExpressionWrapper(
         subtask_verification_time, output_field=IntegerField()))
Ejemplo n.º 22
0
    def _unique_objects_per_day(self, query_objects: QuerySet) -> QuerySet:
        """Count the unique reports per version per day.

        Args:
            query_objects: The reports to count.
        Returns: The unique reports grouped per version per day.

        """
        return (query_objects.annotate(_report_day=TruncDate("date")).values(
            self.version_field_name,
            "_report_day").annotate(count=Count("date")))
def get_personal_notes(
        self,
        persons: QuerySet,
        wanted_week: Optional[CalendarWeek] = None) -> PersonalNoteQuerySet:
    """Get all personal notes for that register object in a specified week.

    The week is optional for extra lessons and events as they have own date information.

    Returns all linked `PersonalNote` objects,
    filtered by the given week for `LessonPeriod` objects,
    creating those objects that haven't been created yet.

    ..note:: Only available when AlekSIS-App-Alsijil is installed.

    :Date: 2019-11-09
    :Authors:
        - Dominik George <*****@*****.**>
    """
    # Find all persons in the associated groups that do not yet have a personal note for this lesson
    if isinstance(self, LessonPeriod):
        q_attrs = dict(week=wanted_week.week,
                       year=wanted_week.year,
                       lesson_period=self)
    elif isinstance(self, Event):
        q_attrs = dict(event=self)
    else:
        q_attrs = dict(extra_lesson=self)

    missing_persons = persons.annotate(no_personal_notes=~Exists(
        PersonalNote.objects.filter(person__pk=OuterRef("pk"), **q_attrs))
                                       ).filter(
                                           member_of__in=Group.objects.filter(
                                               pk__in=self.get_groups().all()),
                                           is_active=True,
                                           no_personal_notes=True,
                                       )

    # Create all missing personal notes
    new_personal_notes = [
        PersonalNote(
            person=person,
            **q_attrs,
        ) for person in missing_persons
    ]
    PersonalNote.objects.bulk_create(new_personal_notes)

    for personal_note in new_personal_notes:
        personal_note.groups_of_person.set(
            personal_note.person.member_of.all())

    return (PersonalNote.objects.filter(**q_attrs, person__in=persons).
            select_related(None).prefetch_related(None).select_related(
                "person", "excuse_type").prefetch_related("extra_marks"))
Ejemplo n.º 24
0
 def sort_by_payment(queryset: QuerySet, sort_by: SortInputObjectType) -> QuerySet:
     last_payments = (
         queryset.exclude(payments__isnull=True)
         .annotate(payment_id=Max("payments__pk"))
         .values_list("payment_id", flat=True)
     )
     queryset = queryset.annotate(
         last_payment=FilteredRelation(
             "payments", condition=Q(payments__pk__in=last_payments)
         )
     )
     return queryset.order_by(f"{sort_by.direction}last_payment__charge_status")
Ejemplo n.º 25
0
 def apply(self, queryset: QuerySet) -> QuerySet:
     """Return a new `QuerySet` with this `Annotation` applied."""
     if self.filters:
         kwargs = {
             self.to: self.func(
                 self.field, filter=reduce(lambda i, j: i & j, (f.get() for f in self.filters))
             )
         }
     else:
         kwargs = {self.to: self.func(self.field)}
     
     return queryset.annotate(**kwargs)
Ejemplo n.º 26
0
def filter_by_stoken(
    stoken: t.Optional[str], queryset: QuerySet,
    stoken_annotation: StokenAnnotation
) -> t.Tuple[QuerySet, t.Optional[Stoken]]:
    stoken_rev = get_stoken_obj(stoken)

    queryset = queryset.annotate(
        max_stoken=stoken_annotation).order_by("max_stoken")

    if stoken_rev is not None:
        queryset = queryset.filter(max_stoken__gt=stoken_rev.id)

    return queryset, stoken_rev
Ejemplo n.º 27
0
    def _get_sorting_annotation(queryset: models.QuerySet, field_name: str,
                                annotations: dict) -> models.Case:
        queryset = queryset.values(field_name)
        queryset = queryset.annotate(**annotations)
        queryset = queryset.order_by('value')

        return models.Case(*[
            models.When(**{
                "{}".format(field_name): group[field_name],
                "then": index
            }) for index, group in enumerate(queryset)
        ],
                           output_field=models.IntegerField())
Ejemplo n.º 28
0
    def filter_vertrouwelijkheidaanduiding(self, base: QuerySet, value) -> QuerySet:
        if value is None:
            return base

        order_provided = VertrouwelijkheidsAanduiding.get_choice(value).order
        order_case = VertrouwelijkheidsAanduiding.get_order_expression(
            "max_vertrouwelijkheidaanduiding"
        )

        # In this case we are filtering Autorisatie model to look for auth which meets our needs.
        # Therefore we're only considering authorizations here that have a max_vertrouwelijkheidaanduiding
        # bigger or equal than what we're checking for the object.
        # In cases when we are filtering data objects (Zaak, InformatieObject etc) it's the other way around

        return base.annotate(max_vertr=order_case).filter(max_vertr__gte=order_provided)
Ejemplo n.º 29
0
def filter_in_range(
    queryset: QuerySet,
    latitude: float,
    longitude: float,
    range_in_meters: int,
    latitude_column_name: str = "latitude",
    longitude_column_name: str = "longitude",
    field_name: str = "earthbox",
    lookup_exp: str = "in_georange",
):
    earthbox = {
        field_name: EarthBox(LLToEarth(latitude, longitude), range_in_meters)
    }
    lookup = "%s__%s" % (field_name, lookup_exp)
    in_range = {lookup: LLToEarth(latitude_column_name, longitude_column_name)}
    return queryset.annotate(**earthbox).filter(**in_range)
Ejemplo n.º 30
0
def add_sound_effect_count_annotation(user: User, qs: QuerySet):
    month_ago = timezone.now() - timezone.timedelta(days=30)
    my_total_play_count = Count("play_history",
                                filter=Q(play_history__played_by=user),
                                distinct=False)
    my_play_count_month = Count("play_history",
                                filter=Q(play_history__played_by=user),
                                distinct=False)
    total_play_count = Count("play_history", distinct=False)
    play_count_month = Count("play_history",
                             filter=Q(play_history__played_at__gte=month_ago),
                             distinct=False)
    return qs.annotate(my_total_play_count=my_total_play_count,
                       my_play_count_month=my_play_count_month,
                       total_play_count=total_play_count,
                       play_count_month=play_count_month)