Python functions.Coalesce方法代码示例

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
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)
        ) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def get_context_data(self, **kwargs):
        context = super(CommentedDetailView, self).get_context_data(**kwargs)
        queryset = Comment.objects.filter(hidden=False, page=self.get_comment_page())
        context['has_comments'] = queryset.exists()
        context['comment_lock'] = self.is_comment_locked()
        queryset = queryset.select_related('author__user').defer('author__about').annotate(revisions=Count('versions'))

        if self.request.user.is_authenticated:
            queryset = queryset.annotate(vote_score=Coalesce(RawSQLColumn(CommentVote, 'score'), Value(0)))
            profile = self.request.profile
            unique_together_left_join(queryset, CommentVote, 'comment', 'voter', profile.id)
            context['is_new_user'] = (not self.request.user.is_staff and
                                      not profile.submission_set.filter(points=F('problem__points')).exists())
        context['comment_list'] = queryset
        context['vote_hide_threshold'] = settings.DMOJ_COMMENT_VOTE_HIDE_THRESHOLD

        return context 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def get_queryset(self, base_queryset=RoundsAndLabsQueryset):
        funds = ApplicationBase.objects.filter(path=OuterRef('parent_path'))

        return base_queryset(self.model, using=self._db).type(SubmittableStreamForm).annotate(
            lead=Coalesce(
                F('roundbase__lead__full_name'),
                F('labbase__lead__full_name'),
            ),
            start_date=F('roundbase__start_date'),
            end_date=F('roundbase__end_date'),
            parent_path=Left(F('path'), Length('path') - ApplicationBase.steplen, output_field=CharField()),
            fund=Subquery(funds.values('title')[:1]),
            lead_pk=Coalesce(
                F('roundbase__lead__pk'),
                F('labbase__lead__pk'),
            ),
        ) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def test_nested_function_ordering(self):
        Author.objects.create(name='John Smith')
        Author.objects.create(name='Rhonda Simpson', alias='ronny')

        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')))
        self.assertQuerysetEqual(
            authors, [
                'Rhonda Simpson',
                'John Smith',
            ],
            lambda a: a.name
        )

        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')).desc())
        self.assertQuerysetEqual(
            authors, [
                'John Smith',
                'Rhonda Simpson',
            ],
            lambda a: a.name
        ) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def test_ordering(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')
        authors = Author.objects.order_by(Coalesce('alias', 'name'))
        self.assertQuerysetEqual(
            authors, ['Rhonda', 'John Smith'],
            lambda a: a.name
        )
        authors = Author.objects.order_by(Coalesce('alias', 'name').asc())
        self.assertQuerysetEqual(
            authors, ['Rhonda', 'John Smith'],
            lambda a: a.name
        )
        authors = Author.objects.order_by(Coalesce('alias', 'name').desc())
        self.assertQuerysetEqual(
            authors, ['John Smith', 'Rhonda'],
            lambda a: a.name
        ) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def names_for_bnf_codes(cls, bnf_codes):
        """
        Given a list of BNF codes return a dictionary mapping those codes to their
        DM&D names
        """
        name_map = cls.objects.filter(bnf_code__in=bnf_codes).values_list(
            "bnf_code", Coalesce("dmd_name", "name")
        )
        return dict(name_map)


# This model is no longer used at all in production. However several of our
# test fixtures depend on it to create prescribing data which is then copied
# into the MatrixStore (which is where all the prescribing data now lives in
# production) so it's easiest to leave it in place for now rather than rewrite
# a lot of old tests. 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def index(request, event=None):
    event = viewutil.get_event(event)
    eventParams = {}

    if event.id:
        eventParams['event'] = event.id

    agg = Donation.objects.filter(
        transactionstate='COMPLETED', testdonation=False, **eventParams
    ).aggregate(
        amount=Cast(Coalesce(Sum('amount'), 0), output_field=FloatField()),
        count=Count('amount'),
        max=Cast(Coalesce(Max('amount'), 0), output_field=FloatField()),
        avg=Cast(Coalesce(Avg('amount'), 0), output_field=FloatField()),
    )
    agg['target'] = float(event.targetamount)
    count = {
        'runs': filters.run_model_query('run', eventParams).count(),
        'prizes': filters.run_model_query('prize', eventParams).count(),
        'bids': filters.run_model_query('bid', eventParams).count(),
        'donors': filters.run_model_query('donorcache', eventParams)
        .values('donor')
        .distinct()
        .count(),
    }

    if 'json' in request.GET:
        return HttpResponse(
            json.dumps({'count': count, 'agg': agg}, ensure_ascii=False,),
            content_type='application/json;charset=utf-8',
        )

    return views_common.tracker_response(
        request, 'tracker/index.html', {'agg': agg, 'count': count, 'event': event}
    ) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def update(self):
        aggregate = Donation.objects.filter(
            donor=self.donor, transactionstate='COMPLETED'
        )
        if self.event:
            aggregate = aggregate.filter(event=self.event)
        aggregate = aggregate.aggregate(
            total=Coalesce(Sum('amount'), 0.0),
            count=Coalesce(Count('amount'), 0),
            max=Coalesce(Max('amount'), 0.0),
            avg=Coalesce(Avg('amount'), 0.0),
        )
        self.donation_total = aggregate['total']
        self.donation_count = aggregate['count']
        self.donation_max = aggregate['max']
        self.donation_avg = aggregate['avg'] 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def _create_accounts_mapping(self):
        """Returns a mapping of org ids to accounts."""
        account_mapping = {}
        with tenant_context(self.tenant):
            for source in self.data_sources:
                # Grab columns for this query
                account_info = source.get("account_alias_column")
                # Create filters & Query
                filters = QueryFilterCollection()
                no_org_units = QueryFilter(field=f"{account_info}", operation="isnull", parameter=False)
                filters.add(no_org_units)
                composed_filters = filters.compose()
                account_query = source.get("db_table").objects
                account_query = account_query.filter(composed_filters)
                account_query = account_query.exclude(deleted_timestamp__lte=self.start_datetime)
                account_query = account_query.exclude(created_timestamp__gt=self.end_datetime)
                if self.access:
                    accounts_to_filter = self.access.get("aws.account", {}).get("read", [])
                    if accounts_to_filter and "*" not in accounts_to_filter:
                        account_query = account_query.filter(account_alias__account_id__in=accounts_to_filter)
                account_query = account_query.order_by(f"{account_info}", "-created_timestamp")
                account_query = account_query.distinct(f"{account_info}")
                account_query = account_query.annotate(
                    alias=Coalesce(F(f"{account_info}__account_alias"), F(f"{account_info}__account_id"))
                )
                for account in account_query:
                    org_id = account.org_unit_id
                    alias = account.alias
                    if account_mapping.get(org_id):
                        account_list = account_mapping[org_id]
                        account_list.append(alias)
                        account_mapping[org_id] = account_list
                    else:
                        account_mapping[org_id] = [alias]
        return account_mapping 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
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")
        prefix_removed_parameters_list = list(
            map(
                lambda x: x if ":" not in x else x.split(":", maxsplit=1)[1],
                self.parameters.get("group_by", {}).keys(),
            )
        )
        for q_param, db_field in fields.items():
            if q_param in prefix_removed_parameters_list:
                annotations[q_param] = F(db_field)
        return annotations 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def _build_sum(self, query, annotations):
        """Build the sum results for the query."""
        sum_units = {}

        query_sum = self.initialize_totals()
        if not self.parameters.parameters.get("compute_count"):
            query_sum.pop("count", None)

        cost_units_fallback = self._mapper.report_type_map.get("cost_units_fallback")
        usage_units_fallback = self._mapper.report_type_map.get("usage_units_fallback")
        count_units_fallback = self._mapper.report_type_map.get("count_units_fallback")
        if query.exists():
            sum_annotations = {"cost_units": Coalesce(self._mapper.cost_units_key, Value(cost_units_fallback))}
            if self._mapper.usage_units_key:
                units_fallback = self._mapper.report_type_map.get("usage_units_fallback")
                sum_annotations["usage_units"] = Coalesce(self._mapper.usage_units_key, Value(units_fallback))
            sum_query = query.annotate(**sum_annotations)
            units_value = sum_query.values("cost_units").first().get("cost_units", cost_units_fallback)
            sum_units = {"cost_units": units_value}
            if self._mapper.usage_units_key:
                units_value = sum_query.values("usage_units").first().get("usage_units", usage_units_fallback)
                sum_units["usage_units"] = units_value
            if annotations.get("count_units"):
                sum_units["count_units"] = count_units_fallback
            query_sum = self.calculate_total(**sum_units)
        else:
            sum_units["cost_units"] = cost_units_fallback
            if annotations.get("count_units"):
                sum_units["count_units"] = count_units_fallback
            if annotations.get("usage_units"):
                sum_units["usage_units"] = usage_units_fallback
            query_sum.update(sum_units)
            self._pack_data_object(query_sum, **self._mapper.PACK_DEFINITIONS)
        return query_sum 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
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 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def _build_sum(self, query):
        """Build the sum results for the query."""
        sum_units = {}
        query_sum = self.initialize_totals()
        cost_units_fallback = self._mapper.report_type_map.get("cost_units_fallback")
        # usage_units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
        count_units_fallback = self._mapper.report_type_map.get("count_units_fallback")
        if query.exists():
            sum_annotations = {"cost_units": Coalesce(self._mapper.cost_units_key, Value(cost_units_fallback))}
            # if self._mapper.usage_units_key:
            #     units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
            #     sum_annotations['usage_units'] = Coalesce(self._mapper.usage_units_key,
            #                                               Value(units_fallback))
            sum_query = query.annotate(**sum_annotations)

            units_value = sum_query.values("cost_units").first().get("cost_units", cost_units_fallback)
            sum_units = {"cost_units": units_value}
            # if self._mapper.usage_units_key:
            #     units_value = sum_query.values('usage_units').first().get('usage_units',
            #                                                               usage_units_fallback)
            #     sum_units['usage_units'] = units_value
            if self._mapper.report_type_map.get("annotations", {}).get("count_units"):
                sum_units["count_units"] = count_units_fallback

            query_sum = self.calculate_total(**sum_units)
        else:
            sum_units["cost_units"] = cost_units_fallback
            if self._mapper.report_type_map.get("annotations", {}).get("count_units"):
                sum_units["count_units"] = count_units_fallback
            # if self._mapper.report_type_map.get('annotations', {}).get('usage_units'):
            #     sum_units['usage_units'] = usage_units_fallback
            query_sum.update(sum_units)
            self._pack_data_object(query_sum, **self._mapper.PACK_DEFINITIONS)
        return query_sum 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def test_filter_org_unit(self):
        """Check that the total is correct when filtering by org_unit_id."""
        with tenant_context(self.tenant):
            org_unit = "R_001"
            org_group_by_url = f"?filter[org_unit_id]={org_unit}"
            query_params = self.mocked_query_params(org_group_by_url, AWSCostView)
            handler = AWSReportQueryHandler(query_params)
            org_data = handler.execute_query()
            # grab the expected totals
            ten_days_ago = self.dh.n_days_ago(self.dh.today, 10)
            expected = AWSCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=ten_days_ago,
                usage_end__lte=self.dh.today,
                organizational_unit__org_unit_id__icontains=org_unit,
            ).aggregate(
                **{
                    "cost_total": Sum(
                        Coalesce(F("unblended_cost"), Value(0, output_field=DecimalField()))
                        + Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
                    )
                }
            )
            # grab the actual totals for the org_unit_id filter
            org_cost_total = org_data.get("total").get("cost").get("total").get("value")
            org_infra_total = org_data.get("total").get("infrastructure").get("total").get("value")
            # make sure they add up
            expected_cost_total = expected.get("cost_total") or 0
            # infra and total cost match
            self.assertEqual(org_cost_total, expected_cost_total)
            self.assertEqual(org_infra_total, expected_cost_total) 

# 需要导入模块: from django.db.models import functions [as 别名]
# 或者: from django.db.models.functions import Coalesce [as 别名]
def _populate_storage_daily_summary_table(self):
        """Populate the daily summary table."""
        included_fields = [
            "usage_start",
            "usage_end",
            "namespace",
            "report_period_id",
            "pod",
            "node",
            "persistentvolumeclaim",
            "persistentvolume",
            "storageclass",
            "cluster_id",
            "cluster_alias",
        ]
        annotations = {
            "volume_labels": Coalesce(F("persistentvolume_labels"), F("persistentvolumeclaim_labels")),
            "persistentvolumeclaim_capacity_gigabyte": ExpressionWrapper(
                F("persistentvolumeclaim_capacity_bytes") * math.pow(2, -30), output_field=DecimalField()
            ),
            "persistentvolumeclaim_capacity_gigabyte_months": ExpressionWrapper(
                F("persistentvolumeclaim_capacity_byte_seconds") / 86400 * 30 * math.pow(2, -30),
                output_field=DecimalField(),
            ),
            "volume_request_storage_gigabyte_months": ExpressionWrapper(
                F("volume_request_storage_byte_seconds") / 86400 * 30 * math.pow(2, -30), output_field=DecimalField()
            ),
            "persistentvolumeclaim_usage_gigabyte_months": ExpressionWrapper(
                F("persistentvolumeclaim_usage_byte_seconds") / 86400 * 30 * math.pow(2, -30),
                output_field=DecimalField(),
            ),
            "data_source": Value("Storage", output_field=CharField()),
        }

        entries = OCPStorageLineItemDaily.objects.values(*included_fields).annotate(**annotations)

        for entry in entries:
            summary = OCPUsageLineItemDailySummary(**entry)
            summary.save()