Python django.db.models.functions.Coalesce() Examples

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 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. 

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'),
            ),
        ) 

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
        ) 

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 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
        ) 

def get_queryset(self, request):
        return self.model.objects.annotate(annotated_subtotal=F('total')-Coalesce(F('vat'), 0)) 

def with_amount_paid(self):
        return self.annotate(
            amount_paid=Coalesce(Sum('payment_requests__paid_value'), Value(0)),
        ) 

def total_value(self, field):
        return self.aggregate(total=Coalesce(Sum(field), Value(0)))['total'] 

def _get_points_earned_trials_count(self):
        """Get points earned and trials count from the sequence.

        :return tuple([trials_count, points_earned])
        """
        # Note(idegtiarov) With the first non-problem activity in the sequence and default value of the threshold
        # item_result returns None, 0 which are not appropriate for the grade calculation method, valid default values
        # are provided to fix this issue.
        items_result = self.sequence.items.exclude(is_problem=False).aggregate(
            points_earned=Coalesce(Sum('score'), 0), trials_count=Greatest(Count('score'), 1)
        )
        return items_result['trials_count'], items_result['points_earned'] 

def get_queryset(self):
        qs = super().get_queryset()
        qs = qs.annotate(score=Coalesce(Avg('vote__vote'), 0))
        return qs 

def query_sum(queryset, field):
    return queryset.aggregate(s=Coalesce(Sum(field), 0))['s'] 

def items(self):
        return NewsPage.objects.live().public().annotate(
            date=Coalesce('publication_date', 'first_published_at')
        ).order_by('-date')[:20] 

def _get_annotations(column, column_values, data, aggregation, display_transform=lambda s: s, default=None):
    value = data if hasattr(data, 'resolve_expression') else F(data)
    return {
        display_transform(display_value): Coalesce(aggregation(Case(When(Q(**{column: column_value}), then=value))), default)
        for column_value, display_value in column_values
    } 

def add_problem_i18n_name(self, key, language, name_field=None):
        queryset = self._clone() if name_field is None else self.annotate(_name=F(name_field))
        alias = unique_together_left_join(queryset, ProblemTranslation, 'problem', 'language', language,
                                          parent_model=Problem)
        # You must specify name_field if Problem is not yet joined into the QuerySet.
        kwargs = {key: Coalesce(RawSQL('%s.name' % alias, ()),
                                F(name_field) if name_field else RawSQLColumn(Problem, 'name'),
                                output_field=models.CharField())}
        return queryset.annotate(**kwargs) 

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 

def getAllocatedQuantity(self, part):
        """ Calculate the total number of <part> currently allocated to this build
        """

        allocated = BuildItem.objects.filter(build=self.id, stock_item__part=part.id).aggregate(q=Coalesce(Sum('quantity'), 0))

        return allocated['q'] 

def with_progress(self):
        submissions = ApplicationSubmission.objects.filter(Q(round=OuterRef('pk')) | Q(page=OuterRef('pk'))).current()
        closed_submissions = submissions.inactive()

        return self.get_queryset(RoundsAndLabsProgressQueryset).annotate(
            total_submissions=Coalesce(
                Subquery(
                    submissions.values('round').annotate(count=Count('pk')).values('count'),
                    output_field=IntegerField(),
                ),
                0,
            ),
            closed_submissions=Coalesce(
                Subquery(
                    closed_submissions.values('round').annotate(count=Count('pk')).values('count'),
                    output_field=IntegerField(),
                ),
                0,
            ),
        ).annotate(
            progress=Case(
                When(total_submissions=0, then=None),
                default=(F('closed_submissions') * 100) / F('total_submissions'),
                output_fields=FloatField(),
            )

        ) 

def allocated_quantity(self):
        """ Return the total stock quantity allocated to this LineItem.

        This is a summation of the quantity of each attached StockItem
        """

        query = self.allocations.aggregate(allocated=Coalesce(Sum('quantity'), Decimal(0)))

        return query['allocated'] 

def get_context(self, request, *args, **kwargs):
        news = NewsPage.objects.live().public().descendant_of(self).annotate(
            date=Coalesce('publication_date', 'first_published_at')
        ).order_by('-date').prefetch_related(
            'news_types__news_type',
            'authors__author',
        )

        if request.GET.get('news_type') and request.GET.get('news_type').isdigit():
            news = news.filter(news_types__news_type=request.GET.get('news_type'))

        # Pagination
        page = request.GET.get('page', 1)
        paginator = Paginator(news, settings.DEFAULT_PER_PAGE)
        try:
            news = paginator.page(page)
        except PageNotAnInteger:
            news = paginator.page(1)
        except EmptyPage:
            news = paginator.page(paginator.num_pages)

        context = super().get_context(request, *args, **kwargs)
        context.update(
            news=news,
            # Only show news types that have been used
            news_types=NewsPageNewsType.objects.all().values_list(
                'news_type__pk', 'news_type__title'
            ).distinct()
        )
        return context 

def with_last_update(self):
        """Annotate `last_update` field that displays the creation or modification date"""
        return self.annotate(last_update=Coalesce(F('modified'), F('created'))) 

def add_last_activity(self):
        """Annotates `last_activity` onto each Inbox and then orders by that column"""
        qs = self.annotate(last_activity=Coalesce(Max("email__received_date",
                                                      filter=Q(email__deleted=False)), "created"))
        return qs


##
# Email managers
## 

def test_basic(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')
        authors = Author.objects.annotate(display_name=Coalesce('alias', 'name'))
        self.assertQuerysetEqual(
            authors.order_by('name'), ['smithj', 'Rhonda'],
            lambda a: a.display_name
        ) 

def test_gt_two_expressions(self):
        with self.assertRaisesMessage(ValueError, 'Coalesce must take at least two expressions'):
            Author.objects.annotate(display_name=Coalesce('alias')) 

def test_mixed_values(self):
        a1 = Author.objects.create(name='John Smith', alias='smithj')
        a2 = Author.objects.create(name='Rhonda')
        ar1 = Article.objects.create(
            title='How to Django',
            text=lorem_ipsum,
            written=timezone.now(),
        )
        ar1.authors.add(a1)
        ar1.authors.add(a2)
        # mixed Text and Char
        article = Article.objects.annotate(
            headline=Coalesce('summary', 'text', output_field=TextField()),
        )
        self.assertQuerysetEqual(
            article.order_by('title'), [lorem_ipsum],
            lambda a: a.headline
        )
        # mixed Text and Char wrapped
        article = Article.objects.annotate(
            headline=Coalesce(Lower('summary'), Lower('text'), output_field=TextField()),
        )
        self.assertQuerysetEqual(
            article.order_by('title'), [lorem_ipsum.lower()],
            lambda a: a.headline
        ) 

def _divide(numerator_field, denominator_field):
    """
    SQL division function which handles NULLs and divide-by-zero gracefully
    """
    numerator = Coalesce(numerator_field, Value(0.0))
    denominator = Func(denominator_field, Value(0.0), function="NULLIF")
    return numerator / denominator 

def NotNullSum(column):
    """Like Sum, but returns 0 if the aggregate is None."""
    return Coalesce(Sum(column), Value(0)) 

def filter_date(self, queryset, name, value):
        queryset = queryset.annotate(end=Coalesce("end_date", Value(date.today())))

        queryset = queryset.filter(start_date__lte=value, end__gte=value)

        return queryset 

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'] 

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