Python django.db.models.Value() Examples

def get_queryset(self):
        date = self._extract_date()
        user = self.request.user
        queryset = get_user_model().objects.values("id")
        queryset = queryset.annotate(date=Value(date, DateField()))
        # last_reported_date filter is set, a date can only be calucated
        # for users with either at least one absence or report
        if date is None:
            users_with_reports = Report.objects.values("user").distinct()
            users_with_absences = Absence.objects.values("user").distinct()
            active_users = users_with_reports.union(users_with_absences)
            queryset = queryset.filter(id__in=active_users)

        queryset = queryset.annotate(
            pk=Concat("id", Value("_"), "date", output_field=CharField())
        )

        if not user.is_superuser:
            queryset = queryset.filter(Q(id=user.id) | Q(supervisors=user))

        return queryset 

def get_queryset(self):
        date = self._extract_date()
        user = self._extract_user()

        queryset = models.AbsenceType.objects.values("id")
        queryset = queryset.annotate(date=Value(date, DateField()))
        queryset = queryset.annotate(user=Value(user.id, IntegerField()))
        queryset = queryset.annotate(
            pk=Concat(
                "user", Value("_"), "id", Value("_"), "date", output_field=CharField()
            )
        )

        # only myself, superuser and supervisors may see by absence balances
        current_user = self.request.user

        if not current_user.is_superuser:
            if current_user.id != user.id:
                if not current_user.supervisees.filter(id=user.id).exists():
                    return models.AbsenceType.objects.none()

        return queryset 

def mark_invalid_emails(cls, emails=None):
        """
        Adds the 'invalid' marker to all email addresses in the given list.
        """
        models = {cls: None, get_user_model(): None}
        for model in models:
            models[model] = (
                model.objects
                .filter(email__in=emails)
                .exclude(email='')
                .exclude(email__startswith=settings.INVALID_PREFIX)
                .update(email=Concat(V(settings.INVALID_PREFIX), F('email')))
            )
        return models 

def for_user(self, user, start, end):
        """Get employments in given time frame for current user.

        This includes overlapping employments.

        :param User user: The user of the searched employments
        :param datetime.date start: start of time frame
        :param datetime.date end: end of time frame
        :returns: queryset of employments
        """
        # end date NULL on database is like employment is ending today
        queryset = self.annotate(
            end=functions.Coalesce("end_date", models.Value(date.today()))
        )
        return queryset.filter(user=user).exclude(
            models.Q(end__lt=start) | models.Q(start_date__gt=end)
        ) 

def __init__(self, expression, pos, length=None, **extra):
        """
        expression: the name of a field, or an expression returning a string
        pos: an integer > 0, or an expression returning an integer
        length: an optional number of characters to return
        """
        if not hasattr(pos, 'resolve_expression'):
            if pos < 1:
                raise ValueError("'pos' must be greater than 0")
            pos = Value(pos)
        expressions = [expression, pos]
        if length is not None:
            if not hasattr(length, 'resolve_expression'):
                length = Value(length)
            expressions.append(length)
        super(Substr, self).__init__(*expressions, **extra) 

def pre_filter(self, queryset, user):
        ''' Returns all of the items from queryset where the date falls into
        any specified range, but not yet where the stock limit is not yet
        reached.'''

        now = timezone.now()

        # Keep items with no start time, or start time not yet met.
        queryset = queryset.filter(Q(start_time=None) | Q(start_time__lte=now))
        queryset = queryset.filter(Q(end_time=None) | Q(end_time__gte=now))

        # Filter out items that have been reserved beyond the limits
        quantity_or_zero = self._calculate_quantities(user)

        remainder = Case(
            When(limit=None, then=Value(_BIG_QUANTITY)),
            default=F("limit") - Sum(quantity_or_zero),
        )

        queryset = queryset.annotate(remainder=remainder)
        queryset = queryset.filter(remainder__gt=0)

        return queryset 

def get_queue_size_params(queue, queue_size, num_in_queue, batch_size, irr_queue):
    '''
    Get the sql parameters for the number of items to add to the queue
    '''
    # if there is less space in the queue than the default number of
    # elements to add, or we are trying to fill the queue to the top
    # (irr_queue=None in the second case)
    if (queue_size - num_in_queue < batch_size) or not(irr_queue):
        sample_size_sql, sample_size_params = (Queue.objects.filter(pk=queue.pk)
                                               .annotate(sample_size=F('length') - Count('data'))
                                               .values('sample_size')
                                               .query.sql_with_params())
    else:
        # just add the number requested (some percent of the batch size)
        sample_size_sql, sample_size_params = (Queue.objects.filter(pk=queue.pk)
                                               .annotate(sample_size=Value(batch_size, IntegerField()))
                                               .values('sample_size')
                                               .query.sql_with_params())
    return sample_size_sql, sample_size_params 

def __init__(self, *expressions, **kwargs):
        separator = kwargs.pop("separator", ",")
        if len(kwargs) > 0:
            raise ValueError(
                "Invalid keyword arguments for ConcatWS: {}".format(
                    ",".join(kwargs.keys())
                )
            )

        if len(expressions) < 2:
            raise ValueError("ConcatWS must take at least two expressions")

        if not hasattr(separator, "resolve_expression"):
            separator = Value(separator)

        # N.B. if separator is "," we could potentially use list field
        output_field = TextField()
        super().__init__(separator, *expressions, output_field=output_field) 

def redirect_staff(self):
        target_work_staff = set()
        kept_staff_ids = []
        # Only one query: put self.target_work's Staff objects first in the list
        queryset = (Staff.objects.filter(work__in=self.works_to_merge)
            .annotate(belongs_to_target_work=Case(
                When(work_id=self.target_work.id, then=Value(1)),
                     default=Value(0), output_field=IntegerField()))
            .order_by('-belongs_to_target_work')
            .values_list('id', 'work_id', 'artist_id', 'role_id'))
        for staff_id, work_id, artist_id, role_id in queryset:
            if work_id == self.target_work.id:  # This condition will be met for the first iterations
                target_work_staff.add((artist_id, role_id))
            # Now we are sure we know every staff of the final work
            elif (artist_id, role_id) not in target_work_staff:
                kept_staff_ids.append(staff_id)
        Staff.objects.filter(work__in=self.works_to_merge).exclude(work_id=self.target_work.id).exclude(
            id__in=kept_staff_ids).delete()
        Staff.objects.filter(id__in=kept_staff_ids).update(work_id=self.target_work.id) 

def as_vector(self, texts, for_autocomplete=False):
        """
        Converts an array of strings into a SearchVector that can be indexed.
        """
        texts = [(text.strip(), weight) for text, weight in texts]
        texts = [(text, weight) for text, weight in texts if text]

        if not texts:
            return EMPTY_VECTOR

        search_config = self.autocomplete_config if for_autocomplete else self.config

        return ADD([
            SearchVector(Value(text, output_field=TextField()), weight=weight, config=search_config)
            for text, weight in texts
        ]) 

def __init__(self, expression, *paths, output_field=None):
        from django_mysql.models.fields import JSONField

        exprs = [expression]
        for path in paths:
            if not hasattr(path, "resolve_expression"):
                path = Value(path)
            exprs.append(path)

        if output_field is not None:
            if len(paths) > 1:
                raise TypeError(
                    "output_field won't work with more than one path, as a "
                    "JSON Array will be returned"
                )
        else:
            output_field = JSONField()

        super().__init__(*exprs, output_field=output_field) 

def credit_notes(request, form):
    ''' Shows all of the credit notes in the system. '''

    notes = commerce.CreditNote.objects.all().select_related(
        "creditnoterefund",
        "creditnoteapplication",
        "invoice",
        "invoice__user__attendee__attendeeprofilebase",
    )

    return QuerysetReport(
        "Credit Notes",
        ["id",
         "invoice__user__attendee__attendeeprofilebase__invoice_recipient",
         "status", "value"],
        notes,
        headings=["id", "Owner", "Status", "Value"],
        link_view=views.credit_note,
    ) 

def _populate_daily_summary_table(self):
        included_fields = ["usage_start", "usage_end", "usage_account_id", "availability_zone", "tags"]
        annotations = {
            "product_family": Concat("cost_entry_product__product_family", Value("")),
            "product_code": Concat("cost_entry_product__service_code", Value("")),
            "region": Concat("cost_entry_product__region", Value("")),
            "instance_type": Concat("cost_entry_product__instance_type", Value("")),
            "unit": Concat("cost_entry_pricing__unit", Value("")),
            "usage_amount": Sum("usage_amount"),
            "normalization_factor": Max("normalization_factor"),
            "normalized_usage_amount": Sum("normalized_usage_amount"),
            "currency_code": Max("currency_code"),
            "unblended_rate": Max("unblended_rate"),
            "unblended_cost": Sum("unblended_cost"),
            "blended_rate": Max("blended_rate"),
            "blended_cost": Sum("blended_cost"),
            "public_on_demand_cost": Sum("public_on_demand_cost"),
            "public_on_demand_rate": Max("public_on_demand_rate"),
            "resource_count": Count("resource_id", distinct=True),
            "resource_ids": ArrayAgg("resource_id", distinct=True),
        }

        entries = AWSCostEntryLineItemDaily.objects.values(*included_fields).annotate(**annotations)
        for entry in entries:
            alias = AWSAccountAlias.objects.filter(account_id=entry["usage_account_id"])
            alias = list(alias).pop() if alias else None
            summary = AWSCostEntryLineItemDailySummary(**entry, account_alias=alias)
            summary.save()
            self.current_month_total += entry["unblended_cost"] + entry["unblended_cost"] * Decimal(0.1)
        AWSCostEntryLineItemDailySummary.objects.update(markup_cost=F("unblended_cost") * 0.1) 

def __init__(self, expression, data):
        from django_mysql.models.fields import JSONField

        if not data:
            raise ValueError('"data" cannot be empty')

        exprs = [expression]

        for path, value in data.items():
            if not hasattr(path, "resolve_expression"):
                path = Value(path)

            exprs.append(path)

            if not hasattr(value, "resolve_expression"):
                value = JSONValue(value)

            exprs.append(value)

        super().__init__(*exprs, output_field=JSONField()) 

def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        units_fallback = self._mapper.report_type_map.get("cost_units_fallback")
        annotations = {
            "date": self.date_trunc("usage_start"),
            "cost_units": Coalesce(self._mapper.cost_units_key, Value(units_fallback)),
        }
        if self._mapper.usage_units_key:
            units_fallback = self._mapper.report_type_map.get("usage_units_fallback")
            annotations["usage_units"] = Coalesce(self._mapper.usage_units_key, Value(units_fallback))

        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get("annotations")
        for q_param, db_field in fields.items():
            annotations[q_param] = Concat(db_field, Value(""))
        return annotations 

def __init__(self, expression, path=None, **extra):
        output_field = extra.pop("output_field", IntegerField())

        exprs = [expression]
        if path is not None:
            if not hasattr(path, "resolve_expression"):
                path = Value(path)
            exprs.append(path)

        super().__init__(*exprs, output_field=output_field) 

def pivot(queryset, rows, column, data, aggregation=Sum, choices='auto', display_transform=lambda s: s, default=None, row_range=()):
    """
    Takes a queryset and pivots it. The result is a table with one record
    per unique value in the `row` column, a column for each unique value in the `column` column
    and values in the table aggregated by the data column.

    :param queryset: a QuerySet, Model, or Manager
    :param rows: list of strings, name of columns that will key the rows
    :param column: string, name of column that will define columns
    :param data: column name or Combinable
    :param aggregation: aggregation function to apply to data column
    :param display_transform: function that takes a string and returns a string
    :param default: default value to pass to the aggregate function when no record is found
    :param row_range: iterable with the expected range of rows in the result
    :return: ValuesQueryset
    """
    values = [rows] if isinstance(rows, six.string_types) else list(rows)

    queryset = _get_queryset(queryset)

    column_values = get_column_values(queryset, column, choices)

    annotations = _get_annotations(column, column_values, data, aggregation, display_transform, default=default)
    for row in values:
        row_choices = get_field_choices(queryset, row)
        if row_choices:
            whens = (When(Q(**{row: value}), then=Value(display_value, output_field=CharField())) for value, display_value in row_choices)
            row_display = Case(*whens)
            queryset = queryset.annotate(**{'get_' + row + '_display': row_display})
            values.append('get_' + row + '_display')

    values_list = queryset.values(*values).annotate(**annotations)

    if row_range:
        attributes = [value[0] for value in column_values]
        values_list = default_fill(values_list, values[0], row_range, fill_value=default, fill_attributes=attributes)

    return values_list 

def simple_histogram(queryset, column, bins):
    """
    Return a histogram from data in queryset.

    :param queryset: A Queryet, Model, or Manager
    :param column: The column we are aggregating into a histogram
    :param bins: An ordered iterable of left endpoints of the bins. Must have at least two elements.
    The endpoints must be a convertible to strings by force_text
    :return: A dictionary with bin endpoints converted to strings as keys and
    """
    queryset = _get_queryset(queryset)

    queryset = queryset.annotate(column_name=Value(column, output_field=CharField()))

    return multi_histogram(queryset, column, bins, slice_on='column_name', choices=((column, column),)) 

def __init__(self, xml_frag, xpath_expr):
        if not hasattr(xpath_expr, "resolve_expression"):
            xpath_expr = Value(xpath_expr)

        return super().__init__(xml_frag, xpath_expr, output_field=TextField())


# Encryption Functions 

def __init__(self, expression, path=None):
        from django_mysql.models.fields import JSONField

        exprs = [expression]
        if path is not None:
            if not hasattr(path, "resolve_expression"):
                path = Value(path)
            exprs.append(path)

        super().__init__(*exprs, output_field=JSONField()) 

def coalesce(self):
        # null on either side results in null for expression, wrap with coalesce
        c = self.copy()
        expressions = [
            Coalesce(expression, Value('')) for expression in c.get_source_expressions()
        ]
        c.set_source_expressions(expressions)
        return c 

def coalesce(self):
        # null on either side results in null for expression, wrap with coalesce
        c = self.copy()
        expressions = [
            Coalesce(expression, Value('')) for expression in c.get_source_expressions()
        ]
        c.set_source_expressions(expressions)
        return c 

def order_for_display(queryset):
        return queryset.annotate(
            display_order=Case(
                When(depth=1, then=Value('')),
                default='name')
        ).order_by('display_order') 

def _update_descendant_url_paths(self, old_url_path, new_url_path):
        (Page.objects
            .filter(path__startswith=self.path)
            .exclude(pk=self.pk)
            .update(url_path=Concat(
                Value(new_url_path),
                Substr('url_path', len(old_url_path) + 1))))

    #: Return this page in its most specific subclassed form. 

def expired(self):
        expires = timezone.now() - jwt_settings.JWT_REFRESH_EXPIRATION_DELTA
        return self.annotate(
            expired=Case(
                When(created__lt=expires, then=V(True)),
                output_field=models.BooleanField(),
                default=V(False),
            ),
        ) 

def get_substr(self):
        return Substr(self.source_expressions[0], self.source_expressions[1] * Value(-1)) 

def __init__(self, expression, text, replacement=Value(''), **extra):
        super().__init__(expression, text, replacement, **extra) 

def __init__(self, expression, length, fill_text=Value(' '), **extra):
        if not hasattr(length, 'resolve_expression') and length < 0:
            raise ValueError("'length' must be greater or equal to 0.")
        super().__init__(expression, length, fill_text, **extra) 

def get_substr(self):
        return Substr(self.source_expressions[0], Value(1), self.source_expressions[1]) 

def save(self, *args, **kwargs):
        with transaction.atomic():
            super().save(*args, **kwargs)
            DocumentField.objects \
                .filter(document_type=self) \
                .update(long_code=Concat(Value(self.code + ': '), F('code')))