Python django.db.models.functions.Concat() 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 test_many(self):
        Author.objects.create(name='Jayden')
        Author.objects.create(name='John Smith', alias='smithj', goes_by='John')
        Author.objects.create(name='Margaret', goes_by='Maggie')
        Author.objects.create(name='Rhonda', alias='adnohR')
        authors = Author.objects.annotate(
            joined=Concat('name', V(' ('), 'goes_by', V(')'), output_field=CharField()),
        )
        self.assertQuerysetEqual(
            authors.order_by('name'), [
                'Jayden ()',
                'John Smith (John)',
                'Margaret (Maggie)',
                'Rhonda ()',
            ],
            lambda a: a.joined
        ) 

def get_queryset(self):
        if not (self.request.user.is_authenticated and self.request.user.is_staff):
            raise Http404

        queryset = Customer.objects.all()

        if self.q:
            queryset = queryset.annotate(
                first_last_company_name=Concat(
                    F("first_name"), Value(" "), F("last_name"), Value(" "), F("company")
                )
            )
            terms = self.q.split()

            query = reduce(
                operator.and_,
                (Q(first_last_company_name__icontains=term) for term in terms)
            )

            queryset = queryset.filter(query)

        return queryset 

def test_sql_generation_idempotency(self):
        qs = Article.objects.annotate(description=Concat('title', V(': '), 'summary'))
        # Multiple compilations should not alter the generated query.
        self.assertEqual(str(qs.query), str(qs.all().query)) 

def archive_log(request):
    """获取归档日志列表"""
    limit = int(request.GET.get('limit', 0))
    offset = int(request.GET.get('offset', 0))
    limit = offset + limit
    archive_id = request.GET.get('archive_id')

    archive_logs = ArchiveLog.objects.filter(archive=archive_id).annotate(
        info=Concat('cmd', V('\n'), 'statistics', output_field=TextField()))
    count = archive_logs.count()
    lists = archive_logs.order_by('-id')[offset:limit].values(
        'cmd', 'info', 'condition', 'mode', 'no_delete', 'select_cnt',
        'insert_cnt', 'delete_cnt', 'success', 'error_info', 'start_time', 'end_time'
    )
    # QuerySet 序列化
    rows = [row for row in lists]
    result = {"total": count, "rows": rows}
    # 返回查询结果
    return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
                        content_type='application/json') 

def get_queryset(self):
        if not (self.request.user.is_authenticated and self.request.user.is_staff):
            raise Http404

        queryset = Provider.objects.all()

        if self.q:
            queryset = queryset.annotate(
                name_company=Concat(
                    F("name"), Value(" "), F("company")
                )
            )
            terms = self.q.split()

            query = reduce(
                operator.and_,
                (Q(name_company__icontains=term) for term in terms)
            )

            queryset = queryset.filter(query)

        return queryset 

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 get_queryset(self):
        if not (self.request.user.is_authenticated and self.request.user.is_staff):
            raise Http404

        queryset = Plan.objects.exclude(enabled=False)

        if self.q:
            queryset = queryset.annotate(
                name_provider__name__company=Concat(
                    F("name"), Value(" "), F("provider__name"), Value(" "), F("provider__company")
                )
            )
            terms = self.q.split()

            query = reduce(
                operator.and_,
                (Q(name_provider__name__company__icontains=term) for term in terms)
            )

            queryset = queryset.filter(query)

        return queryset 

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 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 get_search_results(self, request, queryset, search_term):
        if '-' in search_term and search_term[-1].isdigit():
            return queryset \
                .annotate(_series_number=Concat(F('series'), Value('-'), F('number'),
                                                output_field=fields.CharField())) \
                .filter(_series_number=search_term), True

        return super(BillingDocumentAdmin, self).get_search_results(request, queryset, search_term) 

def test_mixed_char_text(self):
        Article.objects.create(title='The Title', text=lorem_ipsum, written=timezone.now())
        article = Article.objects.annotate(
            title_text=Concat('title', V(' - '), 'text', output_field=TextField()),
        ).get(title='The Title')
        self.assertEqual(article.title + ' - ' + article.text, article.title_text)
        # Wrap the concat in something else to ensure that text is returned
        # rather than bytes.
        article = Article.objects.annotate(
            title_text=Upper(Concat('title', V(' - '), 'text', output_field=TextField())),
        ).get(title='The Title')
        expected = article.title + ' - ' + article.text
        self.assertEqual(expected.upper(), article.title_text) 

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

def test_basic(self):
        Author.objects.create(name='Jayden')
        Author.objects.create(name='John Smith', alias='smithj', goes_by='John')
        Author.objects.create(name='Margaret', goes_by='Maggie')
        Author.objects.create(name='Rhonda', alias='adnohR')
        authors = Author.objects.annotate(joined=Concat('alias', 'goes_by'))
        self.assertQuerysetEqual(
            authors.order_by('name'), [
                '',
                'smithjJohn',
                'Maggie',
                'adnohR',
            ],
            lambda a: a.joined
        ) 

def autocomplete(self, query):
        return self.queryset.annotate(
            fullname=Concat(F("first_name"), Value(" "), F("last_name"))
        ).filter(
            Q(first_name__istartswith=query)
            | Q(last_name__istartswith=query)
            | Q(username__istartswith=query)
            | Q(fullname__istartswith=query)
        ) 

def update_status(self, status):
        if status:
            self.status = Case(
                When(status='', then=Value(status)),
                default=Concat('status', Value('<br />' + status))
            )
            self.save() 

def update_status(self, status):
        if status:
            return self.update(
                status=Case(
                    When(status='', then=Value(status)),
                    default=Concat('status', Value('<br />' + status))
                )
            ) 

def prepend_name(self, modifier):
        invalid_modifications = []

        # Ensure name manipulation does not create an invalid rule name
        for entry_id, entry_name in self.values_list('id', 'name'):
            new_name = modifier + entry_name

            if not check_lexical_convention(new_name):
                invalid_modifications.append(entry_id)

        self.exclude(id__in=invalid_modifications).update(name=Concat(Value(modifier), 'name')) 

def append_name(self, modifier):
        invalid_modifications = []

        # Ensure name manipulation does not create an invalid rule name
        for entry_id, entry_name in self.values_list('id', 'name'):
            new_name = entry_name + modifier

            if not check_lexical_convention(new_name):
                invalid_modifications.append(entry_id)

        self.exclude(id__in=invalid_modifications).update(name=Concat('name', Value(modifier))) 

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 lookups(self, request, model_admin):
        queryset = model_admin.get_queryset(request)

        invoices_queryset = Invoice.objects \
            .filter(invoice_transactions__in=queryset.distinct()) \
            .annotate(_series_number=Concat(F('series'), Value('-'), F('number'),
                                            output_field=fields.CharField())) \
            .values_list('id', '_series_number') \
            .distinct()

        return list(invoices_queryset) 

def lookups(self, request, model_admin):
        queryset = model_admin.get_queryset(request).distinct() \
            .annotate(
                _name_provider=Concat(
                    F('plan__name'), Value(' ('), F('plan__provider__name'), Value(')'),
                    output_field=fields.CharField()
                ),
        ) \
            .values_list('id', '_name_provider') \
            .distinct()

        return list(queryset) 

def update_output(cls, execution_id, stdout, stderr):
        """
        Append to stdout & stderr.
        Use concatenation to efficiently update the fields.
        """
        query = Execution.objects.filter(id=execution_id)
        query.update(
            stdout=functions.Concat('stdout', models.Value(stdout or '')),
            stderr=functions.Concat('stderr', models.Value(stderr or ''))
        ) 

def generate_long_code(apps):
    DocumentType = apps.get_model('document', 'DocumentType')
    DocumentField = apps.get_model('document', 'DocumentField')
    for document_type in DocumentType.objects.all():
        DocumentField.objects\
            .filter(document_type=document_type)\
            .update(long_code=Concat(Value(document_type.code + ': '), F('code')))

    DocumentField.objects.filter(document_type=None).update(long_code=F('code')) 

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

def test_Concat_expresion(self):

        # initialize
        ini_values_1 = 'a', 'b', 'c', 'd', 'e'
        ini_values_2 = 'v', 'w', 'x', 'y', 'z'

        people = Person.objects.order_by('pk').all()
        for value1, value2, person in zip(ini_values_1, ini_values_2, people):
            person.slug = value1
            person.name = value2
            person.save()

        # set
        people = Person.objects.order_by('pk').all()
        for person in people:
            person.text = Concat(F('slug'), Value('@'), F('name'), Value('|'))

        # update
        Person.objects.bulk_update(people)

        # check
        people = Person.objects.order_by('pk').all()

        expected_values = 'a@v|', 'b@w|', 'c@x|', 'd@y|', 'e@z|'
        for expected_value, person in zip(expected_values, people):
            saved_value = person.text
            self.assertEqual(saved_value, expected_value) 

def get(self, request: Request, *args, **kwargs):
        """
        Performs a simple regex search for a matching application based on the user's first and last name. Creates a
        new temporary column called "full_name" which is just "<FIRST_NAME> <LAST_NAME>", and then regex-searches the
        query against the column, and returns all matches.
        """
        query = request.GET.get("q")
        matches = list(
            Application.objects.annotate(
                full_name=Concat("first_name", Value(" "), "last_name")
            )
            .filter(full_name__icontains=query)
            .values("first_name", "last_name", email=F("user__email"))
        )
        return JsonResponse({"results": matches}) 

def test_annotation_with_only():
    class ReporterType(DjangoObjectType):
        full_name = String()

        class Meta:
            model = Reporter
            interfaces = (Node,)
            filter_fields = ()

    class Query(ObjectType):
        all_reporters = DjangoFilterConnectionField(ReporterType)

        def resolve_all_reporters(self, info, **args):
            return Reporter.objects.only("first_name", "last_name").annotate(
                full_name=Concat(
                    "first_name", Value(" "), "last_name", output_field=TextField()
                )
            )

    Reporter.objects.create(first_name="John", last_name="Doe")

    schema = Schema(query=Query)

    query = """
        query NodeFilteringQuery {
            allReporters(first: 1) {
                edges {
                    node {
                        fullName
                    }
                }
            }
        }
    """
    expected = {"allReporters": {"edges": [{"node": {"fullName": "John Doe"}}]}}

    result = schema.execute(query)

    assert not result.errors
    assert result.data == expected 

def test_annotation_is_preserved():
    class ReporterType(DjangoObjectType):
        full_name = String()

        def resolve_full_name(instance, info, **args):
            return instance.full_name

        class Meta:
            model = Reporter
            interfaces = (Node,)
            filter_fields = ()

    class Query(ObjectType):
        all_reporters = DjangoFilterConnectionField(ReporterType)

        def resolve_all_reporters(self, info, **args):
            return Reporter.objects.annotate(
                full_name=Concat(
                    "first_name", Value(" "), "last_name", output_field=TextField()
                )
            )

    Reporter.objects.create(first_name="John", last_name="Doe")

    schema = Schema(query=Query)

    query = """
        query NodeFilteringQuery {
            allReporters(first: 1) {
                edges {
                    node {
                        fullName
                    }
                }
            }
        }
    """
    expected = {"allReporters": {"edges": [{"node": {"fullName": "John Doe"}}]}}

    result = schema.execute(query)

    assert not result.errors
    assert result.data == expected