diff options
Diffstat (limited to 'docs')
| -rw-r--r-- | docs/index.txt | 1 | ||||
| -rw-r--r-- | docs/ref/models/conditional-expressions.txt | 212 | ||||
| -rw-r--r-- | docs/ref/models/expressions.txt | 9 | ||||
| -rw-r--r-- | docs/ref/models/index.txt | 1 | ||||
| -rw-r--r-- | docs/releases/1.8.txt | 10 |
5 files changed, 230 insertions, 3 deletions
diff --git a/docs/index.txt b/docs/index.txt index 5204f00085..f4074ca5f2 100644 --- a/docs/index.txt +++ b/docs/index.txt @@ -87,6 +87,7 @@ manipulating the data of your Web application. Learn more about it below: :doc:`Multiple databases <topics/db/multi-db>` | :doc:`Custom lookups <howto/custom-lookups>` | :doc:`Query Expressions <ref/models/expressions>` | + :doc:`Conditional Expressions <ref/models/conditional-expressions>` | :doc:`Database Functions <ref/models/database-functions>` * **Other:** diff --git a/docs/ref/models/conditional-expressions.txt b/docs/ref/models/conditional-expressions.txt new file mode 100644 index 0000000000..5692bc1968 --- /dev/null +++ b/docs/ref/models/conditional-expressions.txt @@ -0,0 +1,212 @@ +======================= +Conditional Expressions +======================= + +.. currentmodule:: django.db.models.expressions + +.. versionadded:: 1.8 + +Conditional expressions let you use :keyword:`if` ... :keyword:`elif` ... +:keyword:`else` logic within filters, annotations, aggregations, and updates. A +conditional expression evaluates a series of conditions for each row of a +table and returns the matching result expression. Conditional expressions can +also be combined and nested like other :doc:`expressions <expressions>`. + +The conditional expression classes +================================== + +We'll be using the following model in the subsequent examples:: + + from django.db import models + + class Client(models.Model): + REGULAR = 'R' + GOLD = 'G' + PLATINUM = 'P' + ACCOUNT_TYPE_CHOICES = ( + (REGULAR, 'Regular'), + (GOLD, 'Gold'), + (PLATINUM, 'Platinum'), + ) + name = models.CharField(max_length=50) + registered_on = models.DateField() + account_type = models.CharField( + max_length=1, + choices=ACCOUNT_TYPE_CHOICES, + default=REGULAR, + ) + +When +---- + +.. class:: When(condition=None, then=Value(None), **lookups) + +A ``When()`` object is used to encapsulate a condition and its result for use +in the conditional expression. Using a ``When()`` object is similar to using +the :meth:`~django.db.models.query.QuerySet.filter` method. The condition can +be specified using :ref:`field lookups <field-lookups>` or +:class:`~django.db.models.Q` objects. The result is provided using the ``then`` +keyword. + +Some examples:: + + >>> from django.db.models import When, F, Q + >>> # String arguments refer to fields; the following two examples are equivalent: + >>> When(account_type=Client.GOLD, then='name') + >>> When(account_type=Client.GOLD, then=F('name')) + >>> # You can use field lookups in the condition + >>> from datetime import date + >>> When(registered_on__gt=date(2014, 1, 1), + ... registered_on__lt=date(2015, 1, 1), + ... then='account_type') + >>> # Complex conditions can be created using Q objects + >>> When(Q(name__startswith="John") | Q(name__startswith="Paul"), + ... then='name') + +Keep in mind that each of these values can be an expression. + +.. note:: + + Since the ``then`` keyword argument is reserved for the result of the + ``When()``, there is a potential conflict if a + :class:`~django.db.models.Model` has a field named ``then``. This can be + resolved in two ways:: + + >>> from django.db.models import Value + >>> When(then__exact=0, then=Value(1)) + >>> When(Q(then=0), then=Value(1)) + +Case +---- + +.. class:: Case(*cases, **extra) + +A ``Case()`` expression is like the :keyword:`if` ... :keyword:`elif` ... +:keyword:`else` statement in ``Python``. Each ``condition`` in the provided +``When()`` objects is evaluated in order, until one evaluates to a +truthful value. The ``result`` expression from the matching ``When()`` object +is returned. + +A simple example:: + + >>> + >>> from datetime import date, timedelta + >>> from django.db.models import CharField, Case, Value, When + >>> Client.objects.create( + ... name='Jane Doe', + ... account_type=Client.REGULAR, + ... registered_on=date.today() - timedelta(days=36)) + >>> Client.objects.create( + ... name='James Smith', + ... account_type=Client.GOLD, + ... registered_on=date.today() - timedelta(days=5)) + >>> Client.objects.create( + ... name='Jack Black', + ... account_type=Client.PLATINUM, + ... registered_on=date.today() - timedelta(days=10 * 365)) + >>> # Get the discount for each Client based on the account type + >>> Client.objects.annotate( + ... discount=Case( + ... When(account_type=Client.GOLD, then=Value('5%')), + ... When(account_type=Client.PLATINUM, then=Value('10%')), + ... default=Value('0%'), + ... output_field=CharField(), + ... ), + ... ).values_list('name', 'discount') + [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')] + +``Case()`` accepts any number of ``When()`` objects as individual arguments. +Other options are provided using keyword arguments. If none of the conditions +evaluate to ``TRUE``, then the expression given with the ``default`` keyword +argument is returned. If no ``default`` argument is provided, ``Value(None)`` +is used. + +If we wanted to change our previous query to get the discount based on how long +the ``Client`` has been with us, we could do so using lookups:: + + >>> a_month_ago = date.today() - timedelta(days=30) + >>> a_year_ago = date.today() - timedelta(days=365) + >>> # Get the discount for each Client based on the registration date + >>> Client.objects.annotate( + ... discount=Case( + ... When(registered_on__lte=a_year_ago, then=Value('10%')), + ... When(registered_on__lte=a_month_ago, then=Value('5%')), + ... default=Value('0%'), + ... output_field=CharField(), + ... ) + ... ).values_list('name', 'discount') + [('Jane Doe', '5%'), ('James Smith', '0%'), ('Jack Black', '10%')] + +.. note:: + + Remember that the conditions are evaluated in order, so in the above + example we get the correct result even though the second condition matches + both Jane Doe and Jack Black. This works just like an :keyword:`if` ... + :keyword:`elif` ... :keyword:`else` statement in ``Python``. + +Advanced queries +================ + +Conditional expressions can be used in annotations, aggregations, lookups, and +updates. They can also be combined and nested with other expressions. This +allows you to make powerful conditional queries. + +Conditional update +------------------ + +Let's say we want to change the ``account_type`` for our clients to match +their registration dates. We can do this using a conditional expression and the +:meth:`~django.db.models.query.QuerySet.update` method:: + + >>> a_month_ago = date.today() - timedelta(days=30) + >>> a_year_ago = date.today() - timedelta(days=365) + >>> # Update the account_type for each Client from the registration date + >>> Client.objects.update( + ... account_type=Case( + ... When(registered_on__lte=a_year_ago, + ... then=Value(Client.PLATINUM)), + ... When(registered_on__lte=a_month_ago, + ... then=Value(Client.GOLD)), + ... default=Value(Client.REGULAR) + ... ), + ... ) + >>> Client.objects.values_list('name', 'account_type') + [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')] + +Conditional aggregation +----------------------- + +What if we want to find out how many clients there are for each +``account_type``? We can nest conditional expression within +:ref:`aggregate functions <aggregation-functions>` to achieve this:: + + >>> # Create some more Clients first so we can have something to count + >>> Client.objects.create( + ... name='Jean Grey', + ... account_type=Client.REGULAR, + ... registered_on=date.today()) + >>> Client.objects.create( + ... name='James Bond', + ... account_type=Client.PLATINUM, + ... registered_on=date.today()) + >>> Client.objects.create( + ... name='Jane Porter', + ... account_type=Client.PLATINUM, + ... registered_on=date.today()) + >>> # Get counts for each value of account_type + >>> from django.db.models import IntegerField, Sum + >>> Client.objects.aggregate( + ... regular=Sum( + ... Case(When(account_type=Client.REGULAR, then=Value(1)), + ... output_field=IntegerField()) + ... ), + ... gold=Sum( + ... Case(When(account_type=Client.GOLD, then=Value(1)), + ... output_field=IntegerField()) + ... ), + ... platinum=Sum( + ... Case(When(account_type=Client.PLATINUM, then=Value(1)), + ... output_field=IntegerField()) + ... ) + ... ) + {'regular': 2, 'gold': 1, 'platinum': 3} diff --git a/docs/ref/models/expressions.txt b/docs/ref/models/expressions.txt index b36afe5633..b6b4000278 100644 --- a/docs/ref/models/expressions.txt +++ b/docs/ref/models/expressions.txt @@ -332,6 +332,15 @@ instantiating the model field as any arguments relating to data validation (``max_length``, ``max_digits``, etc.) will not be enforced on the expression's output value. +Conditional expressions +----------------------- + +.. versionadded:: 1.8 + +Conditional expressions allow you to use :keyword:`if` ... :keyword:`elif` ... +:keyword:`else` logic in queries. Django natively supports SQL ``CASE`` +expressions. For more details see :doc:`conditional-expressions`. + Technical Information ===================== diff --git a/docs/ref/models/index.txt b/docs/ref/models/index.txt index c860ee2e04..103775e269 100644 --- a/docs/ref/models/index.txt +++ b/docs/ref/models/index.txt @@ -16,4 +16,5 @@ Model API reference. For introductory material, see :doc:`/topics/db/models`. querysets lookups expressions + conditional-expressions database-functions diff --git a/docs/releases/1.8.txt b/docs/releases/1.8.txt index 3e22ab06de..95ce56f241 100644 --- a/docs/releases/1.8.txt +++ b/docs/releases/1.8.txt @@ -93,16 +93,20 @@ New data types backends. There is a corresponding :class:`form field <django.forms.DurationField>`. -Query Expressions and Database Functions -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +Query Expressions, Conditional Expressions, and Database Functions +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -:doc:`Query Expressions </ref/models/expressions>` allow users to create, +:doc:`Query Expressions </ref/models/expressions>` allow you to create, customize, and compose complex SQL expressions. This has enabled annotate to accept expressions other than aggregates. Aggregates are now able to reference multiple fields, as well as perform arithmetic, similar to ``F()`` objects. :meth:`~django.db.models.query.QuerySet.order_by` has also gained the ability to accept expressions. +:doc:`Conditional Expressions </ref/models/conditional-expressions>` allow +you to use :keyword:`if` ... :keyword:`elif` ... :keyword:`else` logic within +queries. + A collection of :doc:`database functions </ref/models/database-functions>` is also included with functionality such as :class:`~django.db.models.functions.Coalesce`, |