<commit_before>==================
Database Functions
==================

.. module:: django.db.models.functions
    :synopsis: Database Functions

The classes documented below provide a way for users to use functions provided
by the underlying database as annotations, aggregations, or filters in Django.
Functions are also :doc:`expressions <expressions>`, so they can be used and
combined with other expressions like :ref:`aggregate functions
<aggregation-functions>`.

We'll be using the following model in examples of each function::

    class Author(models.Model):
        name = models.CharField(max_length=50)
        age = models.PositiveIntegerField(null=True, blank=True)
        alias = models.CharField(max_length=50, null=True, blank=True)
        goes_by = models.CharField(max_length=50, null=True, blank=True)

We don't usually recommend allowing ``null=True`` for ``CharField`` since this
allows the field to have two "empty values", but it's important for the
``Coalesce`` example below.

``Cast``
========

.. class:: Cast(expression, output_field)

Forces the result type of ``expression`` to be the one from ``output_field``.

Usage example::

    >>> from django.db.models import FloatField
    >>> from django.db.models.functions import Cast
    >>> Value.objects.create(integer=4)
    >>> value = Value.objects.annotate(as_float=Cast('integer', FloatField())).get()
    >>> print(value.as_float)
    4.0

``Coalesce``
============

.. class:: Coalesce(*expressions, **extra)

Accepts a list of at least two field names or expressions and returns the
first non-null value (note that an empty string is not considered a null
value). Each argument must be of a similar type, so mixing text and numbers
will result in a database error.

Usage examples::

    >>> # Get a screen name from least to most public
    >>> from django.db.models import Sum, Value as V
    >>> from django.db.models.functions import Coalesce
    >>> Author.objects.create(name='<NAME>', goes_by='Maggie')
    >>> author = Author.objects.annotate(
    ...    screen_name=Coalesce('alias', 'goes_by', 'name')).get()
    >>> print(author.screen_name)
    Maggie

    >>> # Prevent an aggregate Sum() from returning None
    >>> aggregated = Author.objects.aggregate(
    ...    combined_age=Coalesce(Sum('age'), V(0)),
    ...    combined_age_default=Sum('age'))
    >>> print(aggregated['combined_age'])
    0
    >>> print(aggregated['combined_age_default'])
    None

.. warning::

    A Python value passed to ``Coalesce`` on MySQL may be converted to an
    incorrect type unless explicitly cast to the correct database type:

    >>> from django.db.models import DateTimeField
    >>> from django.db.models.functions import Cast, Coalesce
    >>> from django.utils import timezone
    >>> now = timezone.now()
    >>> Coalesce('updated', Cast(now, DateTimeField()))

``Concat``
==========

.. class:: Concat(*expressions, **extra)

Accepts a list of at least two text fields or expressions and returns the
concatenated text. Each argument must be of a text or char type. If you want
to concatenate a ``TextField()`` with a ``CharField()``, then be sure to tell
Django that the ``output_field`` should be a ``TextField()``. This is also
required when concatenating a ``Value`` as in the example below.

This function will never have a null result. On backends where a null argument
results in the entire expression being null, Django will ensure that each null
part is converted to an empty string first.

Usage example::

    >>> # Get the display name as "name (goes_by)"
    >>> from django.db.models import CharField, Value as V
    >>> from django.db.models.functions import Concat
    >>> Author.objects.create(name='<NAME>', goes_by='Maggie')
    >>> author = Author.objects.annotate(
    ...    screen_name=Concat('name', V(' ('), 'goes_by', V(')'),
    ...    output_field=CharField())).get()
    >>> print(author.screen_name)
    <NAME> (Maggie)
<commit_msg>Clarified Concat example in docs.<commit_after>==================
Database Functions
==================

.. module:: django.db.models.functions
    :synopsis: Database Functions

The classes documented below provide a way for users to use functions provided
by the underlying database as annotations, aggregations, or filters in Django.
Functions are also :doc:`expressions <expressions>`, so they can be used and
combined with other expressions like :ref:`aggregate functions
<aggregation-functions>`.

We'll be using the following model in examples of each function::

    class Author(models.Model):
        name = models.CharField(max_length=50)
        age = models.PositiveIntegerField(null=True, blank=True)
        alias = models.CharField(max_length=50, null=True, blank=True)
        goes_by = models.CharField(max_length=50, null=True, blank=True)

We don't usually recommend allowing ``null=True`` for ``CharField`` since this
allows the field to have two "empty values", but it's important for the
``Coalesce`` example below.

``Cast``
========

.. class:: Cast(expression, output_field)

Forces the result type of ``expression`` to be the one from ``output_field``.

Usage example::

    >>> from django.db.models import FloatField
    >>> from django.db.models.functions import Cast
    >>> Value.objects.create(integer=4)
    >>> value = Value.objects.annotate(as_float=Cast('integer', FloatField())).get()
    >>> print(value.as_float)
    4.0

``Coalesce``
============

.. class:: Coalesce(*expressions, **extra)

Accepts a list of at least two field names or expressions and returns the
first non-null value (note that an empty string is not considered a null
value). Each argument must be of a similar type, so mixing text and numbers
will result in a database error.

Usage examples::

    >>> # Get a screen name from least to most public
    >>> from django.db.models import Sum, Value as V
    >>> from django.db.models.functions import Coalesce
    >>> Author.objects.create(name='<NAME>', goes_by='Maggie')
    >>> author = Author.objects.annotate(
    ...    screen_name=Coalesce('alias', 'goes_by', 'name')).get()
    >>> print(author.screen_name)
    Maggie

    >>> # Prevent an aggregate Sum() from returning None
    >>> aggregated = Author.objects.aggregate(
    ...    combined_age=Coalesce(Sum('age'), V(0)),
    ...    combined_age_default=Sum('age'))
    >>> print(aggregated['combined_age'])
    0
    >>> print(aggregated['combined_age_default'])
    None

.. warning::

    A Python value passed to ``Coalesce`` on MySQL may be converted to an
    incorrect type unless explicitly cast to the correct database type:

    >>> from django.db.models import DateTimeField
    >>> from django.db.models.functions import Cast, Coalesce
    >>> from django.utils import timezone
    >>> now = timezone.now()
    >>> Coalesce('updated', Cast(now, DateTimeField()))

``Concat``
==========

.. class:: Concat(*expressions, **extra)

Accepts a list of at least two text fields or expressions and returns the
concatenated text. Each argument must be of a text or char type. If you want
to concatenate a ``TextField()`` with a ``CharField()``, then be sure to tell
Django that the ``output_field`` should be a ``TextField()``. Specifying an
``output_field`` is also required when concatenating a ``Value`` as in the
example below.

This function will never have a null result. On backends where a null argument
results in the entire expression being null, Django will ensure that each null
part is converted to an empty string first.

Usage example::

    >>> # Get the display name as "name (goes_by)"
    >>> from django.db.models import CharField, Value as V
    >>> from django.db.models.functions import Concat
    >>> Author.objects.create(name='<NAME>', goes_by='Maggie')
    >>> author = Author.objects.annotate(
    ...     screen_name=Concat(
    ...         'name', V(' ('), 'goes_by', V(')'),
    ...         output_field=CharField()
    ...     )
    ... ).get()
    >>> print(author.screen_name)
    <NAME> (Maggie)

{'fraction_non_alphanumeric': 0.13632398753894082, 'fraction_numerical': 0.0027414330218068538, 'mean_word_length': 3.957381099444101, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 169, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4425444', 'n_tokens_mistral': 2276, 'n_tokens_neox': 2230, 'n_words': 824}