# dokumentasi Django 2.2.9.dev20191202104323

# Pernyataan Permintaan

Query expressions describe a value or a computation that can be used as part of an update, create, filter, order by, annotation, or aggregate. There are a number of built-in expressions (documented below) that can be used to help you write queries. Expressions can be combined, or in some cases nested, to form more complex computations.

## Arimatika didukung

Django supports negation, addition, subtraction, multiplication, division, modulo arithmetic, and the power operator on query expressions, using Python constants, variables, and even other expressions.

Changed in Django 2.1:

Dukungan untuk negasi telah ditambahkan.

## Beberapa contoh

    from django.db.models import Count, F, Value
    from django.db.models.functions import Length, Upper

    # Find companies that have more employees than chairs.
    Company.objects.filter(num_employees__gt=F('num_chairs'))

    # Find companies that have at least twice as many employees
    # as chairs. Both the querysets below are equivalent.
    Company.objects.filter(num_employees__gt=F('num_chairs') * 2)
    Company.objects.filter(
        num_employees__gt=F('num_chairs') + F('num_chairs'))

    # How many chairs are needed for each company to seat all employees?
    >>> company = Company.objects.filter(
    ...    num_employees__gt=F('num_chairs')).annotate(
    ...    chairs_needed=F('num_employees') - F('num_chairs')).first()
    >>> company.num_employees
    120
    >>> company.num_chairs
    50
    >>> company.chairs_needed
    70

    # Create a new company using expressions.
    >>> company = Company.objects.create(name='Google', ticker=Upper(Value('goog')))
    # Be sure to refresh it if you need to access the field.
    >>> company.refresh_from_db()
    >>> company.ticker
    'GOOG'

    # Annotate models with an aggregated value. Both forms
    # below are equivalent.
    Company.objects.annotate(num_products=Count('products'))
    Company.objects.annotate(num_products=Count(F('products')))

    # Aggregates can contain complex computations also
    Company.objects.annotate(num_offerings=Count(F('products') + F('services')))

    # Expressions can also be used in order_by(), either directly
    Company.objects.order_by(Length('name').asc())
    Company.objects.order_by(Length('name').desc())
    # or using the double underscore lookup syntax.
    from django.db.models import CharField
    from django.db.models.functions import Length
    CharField.register_lookup(Length)
    Company.objects.order_by('name__length')

## Pernyataan Siap-pakai

Catatan

These expressions are defined in `django.db.models.expressions` and `django.db.models.aggregates`, but for convenience they're available and usually imported from `django.db.models`.

### Pernyataan `F()`

class `F`

An `F()` object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

Instead, Django uses the `F()` object to generate an SQL expression that describes the required operation at the database level.

Ini adalah paling mudah untuk memahami melalui sebuah contoh. Biasanya, satu mungkin melakukan sesuatu seperti ini:

    # Tintin filed a news story!
    reporter = Reporters.objects.get(name='Tintin')
    reporter.stories_filed += 1
    reporter.save()

Here, we have pulled the value of `reporter.stories_filed` from the database into memory and manipulated it using familiar Python operators, and then saved the object back to the database. But instead we could also have done:

    from django.db.models import F

    reporter = Reporters.objects.get(name='Tintin')
    reporter.stories_filed = F('stories_filed') + 1
    reporter.save()

Although `reporter.stories_filed = F('stories_filed') + 1` looks like a normal Python assignment of value to an instance attribute, in fact it's an SQL construct describing an operation on the database.

When Django encounters an instance of `F()`, it overrides the standard Python operators to create an encapsulated SQL expression; in this case, one which instructs the database to increment the database field represented by `reporter.stories_filed`.

Whatever value is or was on `reporter.stories_filed`, Python never gets to know about it - it is dealt with entirely by the database. All Python does, through Django's `F()` class, is create the SQL syntax to refer to the field and describe the operation.

To access the new value saved this way, the object must be reloaded:

    reporter = Reporters.objects.get(pk=reporter.pk)
    # Or, more succinctly:
    reporter.refresh_from_db()

As well as being used in operations on single instances as above, `F()` can be used on `QuerySets` of object instances, with `update()`. This reduces the two queries we were using above - the `get()` and the `save()` - to just one:

    reporter = Reporters.objects.filter(name='Tintin')
    reporter.update(stories_filed=F('stories_filed') + 1)

We can also use `update()` to increment the field value on multiple objects - which could be very much faster than pulling them all into Python from the database, looping over them, incrementing the field value of each one, and saving each one back to the database:

    Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)

`F()` karena itu menawarkan keuntungan penampilan oleh:

- mendapatkan basisdata, daripada Python, untuk melakukan pekerjaan
- mengurangi sejumlah permintaan beebrapa tindakan diperlukan

#### Menghindari kondisi balapan menggunakan `F()`

Manfaat berguna lainnya dari `F()` adalah bahwa memiliki basisdata - daripada Python - memperbaharui sebuah nilai bidang menghindari kondisi balapan.

If two Python threads execute the code in the first example above, one thread could retrieve, increment, and save a field's value after the other has retrieved it from the database. The value that the second thread saves will be based on the original value; the work of the first thread will simply be lost.

If the database is responsible for updating the field, the process is more robust: it will only ever update the field based on the value of the field in the database when the `save()` or `update()` is executed, rather than based on its value when the instance was retrieved.

#### `F()` diberikan berlanjut setelah `Model.save()`

`F()` objects assigned to model fields persist after saving the model instance and will be applied on each `save()`. For example:

    reporter = Reporters.objects.get(name='Tintin')
    reporter.stories_filed = F('stories_filed') + 1
    reporter.save()

    reporter.name = 'Tintin Jr.'
    reporter.save()

`stories_filed` will be updated twice in this case. If it's initially `1`, the final value will be `3`. This persistence can be avoided by reloading the model object after saving it, for example, by using `refresh_from_db()`.

#### Menggunakan `F()` dalam penyaringan

`F()` is also very useful in `QuerySet` filters, where they make it possible to filter a set of objects against criteria based on their field values, rather than on Python values.

Ini didokumentasikan dalam using F() expressions in queries.

#### Menggunakan `F()` dengan keterangan

`F()` dapat digunakan untuk membuat bidang-bidang dinamis pada model anda dengan emmadukan bidang-bidang berbeda dengan aritmatik:

    company = Company.objects.annotate(
        chairs_needed=F('num_employees') - F('num_chairs'))

If the fields that you're combining are of different types you'll need to tell Django what kind of field will be returned. Since `F()` does not directly support `output_field` you will need to wrap the expression with `ExpressionWrapper`:

    from django.db.models import DateTimeField, ExpressionWrapper, F

    Ticket.objects.annotate(
        expires=ExpressionWrapper(
            F('active_at') + F('duration'), output_field=DateTimeField()))

Ketika mengacukan biadng-bidang terkait seperti `ForeignKey`, `F()` mengembalikan nilai primary key daripada instance model:

    >> car = Company.objects.annotate(built_by=F('manufacturer'))[0]
    >> car.manufacturer
    <Manufacturer: Toyota>
    >> car.built_by
    3

#### Menggunakan `F()` untuk mengurutkan nilai null

Use `F()` and the `nulls_first` or `nulls_last` keyword argument to `Expression.asc()` or `desc()` to control the ordering of a field's null values. By default, the ordering depends on your database.

For example, to sort companies that haven't been contacted (`last_contacted` is null) after companies that have been contacted:

    from django.db.models import F
    Company.object.order_by(F('last_contacted').desc(nulls_last=True))

### Pernyataan `Func()`

`Func()` expressions are the base type of all expressions that involve database functions like `COALESCE` and `LOWER`, or aggregates like `SUM`. They can be used directly:

    from django.db.models import F, Func

    queryset.annotate(field_lower=Func(F('field'), function='LOWER'))

atau mereka dapat digunakan untuk membangun pustaka dari fungsi-fungsi basisdata:

    class Lower(Func):
        function = 'LOWER'

    queryset.annotate(field_lower=Lower('field'))

But both cases will result in a queryset where each model is annotated with an extra attribute `field_lower` produced, roughly, from the following SQL:

    SELECT
        ...
        LOWER("db_table"."field") as "field_lower"

Lihat Fungsi Basisdata untuk daftar fungsi-fungsi basisdata siap-pakai.

API `Func` sebagai berikut:

class `Func`(*expressions, **extra)
  `function`

    A class attribute describing the function that will be generated. Specifically, the `function` will be interpolated as the `function` placeholder within `template`. Defaults to `None`.

  `template`

    A class attribute, as a format string, that describes the SQL that is generated for this function. Defaults to `'%(function)s(%(expressions)s)'`.

    If you're constructing SQL like `strftime('%W', 'date')` and need a literal `%` character in the query, quadruple it (`%%%%`) in the `template` attribute because the string is interpolated twice: once during the template interpolation in `as_sql()` and once in the SQL interpolation with the query parameters in the database cursor.

  `arg_joiner`

    Sebuah atribut kelas yang menyatakan karakter digunakan utuk menggabungkan daftar dari `expressions` bersama-sama. Awalan pada `', '`.

  `arity`

    Sebuah atribut kelas yang menyatakan sejumlah fungsi argumen menerima. Jika atribut ini disetel dan fungsi dipanggil dengan angka berbeda dari pernyataan, `TypeError` akan dimunculkan. Awalan pada `None`.

  `as_sql`(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)

    Membangkitkan SQL untuk fungsi basisdata.

    Metode `as_vendor()` harus menggunakan `function`, `template`, `arg_joiner`, dan parameter `**extra_context` apapun lainnya untuk menyesuaikan SQL sesuai kebutuhan. Sebagai contoh:

    django/db/models/functions.py

        class ConcatPair(Func):
            ...
            function = 'CONCAT'
            ...

            def as_mysql(self, compiler, connection, **extra_context):
                return super().as_sql(
                    compiler, connection,
                    function='CONCAT_WS',
                    template="%(function)s('', %(expressions)s)",
                    **extra_context
                )

    To avoid a SQL injection vulnerability, `extra_context` must not contain untrusted user input as these values are interpolated into the SQL string rather than passed as query parameters, where the database driver would escape them.

The `*expressions` argument is a list of positional expressions that the function will be applied to. The expressions will be converted to strings, joined together with `arg_joiner`, and then interpolated into the `template` as the `expressions` placeholder.

Argumen-argumen penempatan dapat berupa pernyataan atau nilai-nilai Python. String dianggap menjadi acuan kolom dan akan dibungkus dalam pernyataan `F()` selagi nilai-nilai lain akan dibungkus dalam pernyataan `Value()`.

kwarg `**extra` adalah pasangan `key=value` yang dapat dimasukakn kedalam artibut `template`. Untuk menghindari kerentanan suntikan SQL, `extra` must not contain untrusted user input sebagai nilai-nilai ini dimasukkan kedalam string SQL daripada dilewatkan sebagai parameter permintaan, dimana pengendali basisdata akan meloloskan mereka.

Kata kunci `function`, `template`, dan `arg_joiner` dapat digunakan untuk mengganti atribut-atribut dari nama sama tanpa harus menentukan kelas anda sendiri. `output_field` dapat digunakan untuk menentukkan jenis kembalian yang diharapkan.

### Pernyataan `Aggregate()`

Sebuah pernyataan pengumpulan adalah kasus khusus dari Func() expression yang menginformasikan permintaan bahwa sebuah klausa `GROUP BY` diwajibkan. Semua dari aggregate functions, seperti `Sum()` dan `Count()`, mewarisi dari `Aggregate()`.

Karena `Aggregate` adalah pernyataan dan membungkus pernyataan, anda dapat mewakili beberapa perhitungan rumit:

    from django.db.models import Count

    Company.objects.annotate(
        managers_required=(Count('num_employees') / 4) + Count('num_managers'))

API ``Aggregate` sebagai berikut:

class `Aggregate`(*expressions, output_field=None, distinct=False, filter=None, **extra)
  `template`

    A class attribute, as a format string, that describes the SQL that is generated for this aggregate. Defaults to `'%(function)s(%(distinct)s%(expressions)s)'`.

  `function`

    Sebuah atribut kelas menggambarkan fungsi pengumpulan yang akan dibangkitkan. Khususnya, `function` akan dimasukakn sebagai placeholder `function` dalam `template`. Awalan pada `None`.

  `window_compatible`

    Awalan menjadi `True` sejak kebanyakan fungsi pengumpulan dapat digunakan sebagai sumber pernyataan dalam `Window`.

  `allow_distinct`
    New in Django 2.2.

    A class attribute determining whether or not this aggregate function allows passing a `distinct` keyword argument. If set to `False` (default), `TypeError` is raised if `distinct=True` is passed.

The `expressions` positional arguments can include expressions or the names of model fields. They will be converted to a string and used as the `expressions` placeholder within the `template`.

Arfumen `output_field` membutuhkan contoh sebuah bidang model, seperti `IntegerField()` atau `BooleanField()`, menjadi Django akan memuat nilai setelah itu diambil dari basisdata. Biasanya tidak ada argumen dibutuhkan ketika menginstansiasi bidang model sebagai argumen apapun terkait pada pengesahan data (`max_length`, `max_digits`, dll.) tidak akan dipaksa pada nilai keluaran pernyataan.

Catat bahwa `output_field` hanya diwajibkan ketika Django tidak dapat menentukan jenis bidang apa hasil seharusnya. Pernyataan rumit yang mencampurkan jenis-jenis bidang harus menentukan `output_field` diharapkan. Sebagai contoh, menambahkan sebuah `IntegerField()` dan sebuah `FloatField()` bersama-sama harus mungkin memiliki `output_field=FloatField()` ditentukan.

The `distinct` argument determines whether or not the aggregate function should be invoked for each distinct value of `expressions` (or set of values, for multiple `expressions`). The argument is only supported on aggregates that have `allow_distinct` set to `True`.

Argumen `filter` mengambil sebuah `Q object` yang digunakan untuk menyaring baris yang dikumpulkan. Lihat Pengumpulan bersyarat dan Penyaringan pada keterangan untuk contoh penggunaan.

kwarg `**extra` adalah pasangan `key=value` yang dapat ditambahkan kedalam atribut `template`.

New in Django 2.2:

The `allow_distinct` attribute and `distinct` argument were added.

### Membuat Fungsi-fungsi Pengumpulan anda sendiri

Creating your own aggregate is extremely easy. At a minimum, you need to define `function`, but you can also completely customize the SQL that is generated. Here's a brief example:

    from django.db.models import Aggregate

    class Sum(Aggregate):
        # Supports SUM(ALL field).
        function = 'SUM'
        template = '%(function)s(%(all_values)s%(expressions)s)'
        allow_distinct = False

        def __init__(self, expression, all_values=False, **extra):
            super().__init__(
                expression,
                all_values='ALL ' if all_values else '',
                **extra
            )

### Pernyataan `Value()`

class `Value`(value, output_field=None)

A `Value()` object represents the smallest possible component of an expression: a simple value. When you need to represent the value of an integer, boolean, or string within an expression, you can wrap that value within a `Value()`.

You will rarely need to use `Value()` directly. When you write the expression `F('field') + 1`, Django implicitly wraps the `1` in a `Value()`, allowing simple values to be used in more complex expressions. You will need to use `Value()` when you want to pass a string to an expression. Most expressions interpret a string argument as the name of a field, like `Lower('name')`.

The `value` argument describes the value to be included in the expression, such as `1`, `True`, or `None`. Django knows how to convert these Python values into their corresponding database type.

The `output_field` argument should be a model field instance, like `IntegerField()` or `BooleanField()`, into which Django will load the value after it's retrieved from the database. Usually no arguments are needed when 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.

### Pernyataan `ExpressionWrapper()`

class `ExpressionWrapper`(expression, output_field)

`ExpressionWrapper` simply surrounds another expression and provides access to properties, such as `output_field`, that may not be available on other expressions. `ExpressionWrapper` is necessary when using arithmetic on `F()` expressions with different types as described in Menggunakan F() dengan keterangan.

### Pernyataan bersyarat

Conditional expressions allow you to use `if` ... `elif` ... `else` logic in queries. Django natively supports SQL `CASE` expressions. For more details see Pernyataan Bersyarat.

### Pernyataan `Subquery()`

class `Subquery`(queryset, output_field=None)

You can add an explicit subquery to a `QuerySet` using the `Subquery` expression.

For example, to annotate each post with the email address of the author of the newest comment on that post:

    >>> from django.db.models import OuterRef, Subquery
    >>> newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
    >>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

Pada PostgreSQL, SQL terlihat seperti:

    SELECT "post"."id", (
        SELECT U0."email"
        FROM "comment" U0
        WHERE U0."post_id" = ("post"."id")
        ORDER BY U0."created_at" DESC LIMIT 1
    ) AS "newest_commenter_email" FROM "post"

Catatan

The examples in this section are designed to show how to force Django to execute a subquery. In some cases it may be possible to write an equivalent queryset that performs the same task more clearly or efficiently.

#### Mengacu kolom dari himpunan permintaan terluar

class `OuterRef`(field)

Use `OuterRef` when a queryset in a `Subquery` needs to refer to a field from the outer query. It acts like an `F` expression except that the check to see if it refers to a valid field isn't made until the outer queryset is resolved.

Instances of `OuterRef` may be used in conjunction with nested instances of `Subquery` to refer to a containing queryset that isn't the immediate parent. For example, this queryset would need to be within a nested pair of `Subquery` instances to resolve correctly:

    >>> Book.objects.filter(author=OuterRef(OuterRef('pk')))

#### Membatasi sub permintaan pada kolom tunggal

There are times when a single column must be returned from a `Subquery`, for instance, to use a `Subquery` as the target of an `__in` lookup. To return all comments for posts published within the last day:

    >>> from datetime import timedelta
    >>> from django.utils import timezone
    >>> one_day_ago = timezone.now() - timedelta(days=1)
    >>> posts = Post.objects.filter(published_at__gte=one_day_ago)
    >>> Comment.objects.filter(post__in=Subquery(posts.values('pk')))

In this case, the subquery must use `values()` to return only a single column: the primary key of the post.

#### Membatasi sub permintaan pada baris tunggal

To prevent a subquery from returning multiple rows, a slice (`[:1]`) of the queryset is used:

    >>> subquery = Subquery(newest.values('email')[:1])
    >>> Post.objects.annotate(newest_commenter_email=subquery)

In this case, the subquery must only return a single column and a single row: the email address of the most recently created comment.

(Using `get()` instead of a slice would fail because the `OuterRef` cannot be resolved until the queryset is used within a `Subquery`.)

#### Subpermintaan `Exists()`

class `Exists`(queryset)

`Exists` is a `Subquery` subclass that uses an SQL `EXISTS` statement. In many cases it will perform better than a subquery since the database is able to stop evaluation of the subquery when a first matching row is found.

For example, to annotate each post with whether or not it has a comment from within the last day:

    >>> from django.db.models import Exists, OuterRef
    >>> from datetime import timedelta
    >>> from django.utils import timezone
    >>> one_day_ago = timezone.now() - timedelta(days=1)
    >>> recent_comments = Comment.objects.filter(
    ...     post=OuterRef('pk'),
    ...     created_at__gte=one_day_ago,
    ... )
    >>> Post.objects.annotate(recent_comment=Exists(recent_comments))

Pada PostgreSQL, SQL terlihat seperti:

    SELECT "post"."id", "post"."published_at", EXISTS(
        SELECT U0."id", U0."post_id", U0."email", U0."created_at"
        FROM "comment" U0
        WHERE (
            U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
            U0."post_id" = ("post"."id")
        )
    ) AS "recent_comment" FROM "post"

It's unnecessary to force `Exists` to refer to a single column, since the columns are discarded and a boolean result is returned. Similarly, since ordering is unimportant within an SQL `EXISTS` subquery and would only degrade performance, it's automatically removed.

Anda dapat meminta menggunakan `NOT EXISTS` dengan `~Exists()`.

#### menyaring pada pernyataan `Subquery`

Itu tidak memungkinkan menyaring langsung menggunakan `Subquery` dan `Exists`, misalnya:

    >>> Post.objects.filter(Exists(recent_comments))
    ...
    TypeError: 'Exists' object is not iterable

You must filter on a subquery expression by first annotating the queryset and then filtering based on that annotation:

    >>> Post.objects.annotate(
    ...     recent_comment=Exists(recent_comments),
    ... ).filter(recent_comment=True)

#### Using aggregates within a `Subquery` expression

Aggregates may be used within a `Subquery`, but they require a specific combination of `filter()`, `values()`, and `annotate()` to get the subquery grouping correct.

Assuming both models have a `length` field, to find posts where the post length is greater than the total length of all combined comments:

    >>> from django.db.models import OuterRef, Subquery, Sum
    >>> comments = Comment.objects.filter(post=OuterRef('pk')).order_by().values('post')
    >>> total_comments = comments.annotate(total=Sum('length')).values('total')
    >>> Post.objects.filter(length__gt=Subquery(total_comments))

The initial `filter(...)` limits the subquery to the relevant parameters. `order_by()` removes the default `ordering` (if any) on the `Comment` model. `values('post')` aggregates comments by `Post`. Finally, `annotate(...)` performs the aggregation. The order in which these queryset methods are applied is important. In this case, since the subquery must be limited to a single column, `values('total')` is required.

This is the only way to perform an aggregation within a `Subquery`, as using `aggregate()` attempts to evaluate the queryset (and if there is an `OuterRef`, this will not be possible to resolve).

### Pernyataan SQL mentah

class `RawSQL`(sql, params, output_field=None)

Terkadang pernyataan basisdata tidak dapat dengan mudah menyatakan klausa `WHERE` rumit. Dalam sisi kasus ini, pernyataan `RawSQL`. Sebagai contoh:

    >>> from django.db.models.expressions import RawSQL
    >>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))

These extra lookups may not be portable to different database engines (because you're explicitly writing SQL code) and violate the DRY principle, so you should avoid them if possible.

Peringatan

Untuk melindungi terhadap SQL injection attacks, anda harus meloloskan parameter apapun yang pengguna dapat mengendalikan menggunakan `params`. `params` adalah argumen wajib memaksa anda untuk mengakui bahwa anda tidak menambahkan SQL anda dengan data disediakan-pengguna.

You also must not quote placeholders in the SQL string. This example is vulnerable to SQL injection because of the quotes around `%s`:

    RawSQL("select col from sometable where othercol = '%s'")  # unsafe!

Anda dapat membaca lebih tentang bagaimana SQL injection protection Django bekerja.

### Fungsi Windows

Window functions provide a way to apply functions on partitions. Unlike a normal aggregation function which computes a final result for each set defined by the group by, window functions operate on frames and partitions, and compute the result for each row.

You can specify multiple windows in the same query which in Django ORM would be equivalent to including multiple expressions in a QuerySet.annotate() call. The ORM doesn't make use of named windows, instead they are part of the selected columns.

class `Window`(expression, partition_by=None, order_by=None, frame=None, output_field=None)
  `filterable`

    Defaults to `False`. The SQL standard disallows referencing window functions in the `WHERE` clause and Django raises an exception when constructing a `QuerySet` that would do that.

  `template`

    Defaults to `%(expression)s OVER (%(window)s)'`. If only the `expression` argument is provided, the window clause will be blank.

Kelas `Windows` adalah pernyataan utama untuk klausa `OVER`.

The `expression` argument is either a window function, an aggregate function, or an expression that's compatible in a window clause.

The `partition_by` argument is a list of expressions (column names should be wrapped in an `F`-object) that control the partitioning of the rows. Partitioning narrows which rows are used to compute the result set.

`output_field` ditentukan baik sebagai argumen atau oleh pernyataan.

The `order_by` argument accepts a sequence of expressions on which you can call `asc()` and `desc()`. The ordering controls the order in which the expression is applied. For example, if you sum over the rows in a partition, the first result is just the value of the first row, the second is the sum of first and second row.

Parameter `frame` menentukan baris lain mana yang harus digunakan dalam perhitungan. Lihat Kerangka untuk rincian.

Sebagai contoh, untuk memberi keterangan setiap film dengan penilaian rerata untuk film berdasarkan studio sama dalam aliran dan tahun terbitan yang sama:

    >>> from django.db.models import Avg, F, Window
    >>> from django.db.models.functions import ExtractYear
    >>> Movie.objects.annotate(
    >>>     avg_rating=Window(
    >>>         expression=Avg('rating'),
    >>>         partition_by=[F('studio'), F('genre')],
    >>>         order_by=ExtractYear('released').asc(),
    >>>     ),
    >>> )

Ini membuatnya mudah untuk memeriksa jika sebuah film dinilai lebih baik atau buruk daripada rekan-rekannya.

You may want to apply multiple expressions over the same window, i.e., the same partition and frame. For example, you could modify the previous example to also include the best and worst rating in each movie's group (same studio, genre, and release year) by using three window functions in the same query. The partition and ordering from the previous example is extracted into a dictionary to reduce repetition:

    >>> from django.db.models import Avg, F, Max, Min, Window
    >>> from django.db.models.functions import ExtractYear
    >>> window = {
    >>>    'partition_by': [F('studio'), F('genre')],
    >>>    'order_by': ExtractYear('released').asc(),
    >>> }
    >>> Movie.objects.annotate(
    >>>     avg_rating=Window(
    >>>         expression=Avg('rating'), **window,
    >>>     ),
    >>>     best=Window(
    >>>         expression=Max('rating'), **window,
    >>>     ),
    >>>     worst=Window(
    >>>         expression=Min('rating'), **window,
    >>>     ),
    >>> )

Among Django's built-in database backends, MySQL 8.0.2+, PostgreSQL, and Oracle support window expressions. Support for different window expression features varies among the different databases. For example, the options in `asc()` and `desc()` may not be supported. Consult the documentation for your database as needed.

#### Kerangka

Untuk sebuah kerangka jendela, anda dapat milih salah satu urutan berdasarkan-jangkauan dari baris atau sebuah urutan biasa dari baris.

class `ValueRange`(start=None, end=None)
  `frame_type`

    Atribut ini disetel menjadi `'RANGE'`.

  PostgreSQL mempunyai dukungan terbatas untuk `ValueRange` dan hanya mendukung menggunakan dari titik standar awal dan akhir, seperti `CURRENT ROW` dan `UNBOUNDED FOLLOWING`.

class `RowRange`(start=None, end=None)
  `frame_type`

    Atribut ini disetel menjadi `'ROWS'`.

Kedua kelas mengembalikan SQL dengan cetakan:

    %(frame_type)s BETWEEN %(start)s AND %(end)s

Frames narrow the rows that are used for computing the result. They shift from some start point to some specified end point. Frames can be used with and without partitions, but it's often a good idea to specify an ordering of the window to ensure a deterministic result. In a frame, a peer in a frame is a row with an equivalent value, or all rows if an ordering clause isn't present.

The default starting point for a frame is `UNBOUNDED PRECEDING` which is the first row of the partition. The end point is always explicitly included in the SQL generated by the ORM and is by default `UNBOUNDED FOLLOWING`. The default frame includes all rows from the partition to the last row in the set.

The accepted values for the `start` and `end` arguments are `None`, an integer, or zero. A negative integer for `start` results in `N preceding`, while `None` yields `UNBOUNDED PRECEDING`. For both `start` and `end`, zero will return `CURRENT ROW`. Positive integers are accepted for `end`.

There's a difference in what `CURRENT ROW` includes. When specified in `ROWS` mode, the frame starts or ends with the current row. When specified in `RANGE` mode, the frame starts or ends at the first or last peer according to the ordering clause. Thus, `RANGE CURRENT ROW` evaluates the expression for rows which have the same value specified by the ordering. Because the template includes both the `start` and `end` points, this may be expressed with:

    ValueRange(start=0, end=0)

If a movie's "peers" are described as movies released by the same studio in the same genre in the same year, this `RowRange` example annotates each movie with the average rating of a movie's two prior and two following peers:

    >>> from django.db.models import Avg, F, RowRange, Window
    >>> from django.db.models.functions import ExtractYear
    >>> Movie.objects.annotate(
    >>>     avg_rating=Window(
    >>>         expression=Avg('rating'),
    >>>         partition_by=[F('studio'), F('genre')],
    >>>         order_by=ExtractYear('released').asc(),
    >>>         frame=RowRange(start=-2, end=2),
    >>>     ),
    >>> )

If the database supports it, you can specify the start and end points based on values of an expression in the partition. If the `released` field of the `Movie` model stores the release month of each movies, this `ValueRange` example annotates each movie with the average rating of a movie's peers released between twelve months before and twelve months after the each movie.

    >>> from django.db.models import Avg, ExpressionList, F, ValueRange, Window
    >>> Movie.objects.annotate(
    >>>     avg_rating=Window(
    >>>         expression=Avg('rating'),
    >>>         partition_by=[F('studio'), F('genre')],
    >>>         order_by=F('released').asc(),
    >>>         frame=ValueRange(start=-12, end=12),
    >>>     ),
    >>> )

## Informasi teknis

Below you'll find technical implementation details that may be useful to library authors. The technical API and examples below will help with creating generic query expressions that can extend the built-in functionality that Django provides.

### API Pernyataan

Query expressions implement the query expression API, but also expose a number of extra methods and attributes listed below. All query expressions must inherit from `Expression()` or a relevant subclass.

Ketika pernyataan permintaan membungkus pernyataan lain, itu adalah tanggung jawab untuk memanggil metode-metode sesuai pada pernyataan dibungkus.

class `Expression`
  `contains_aggregate`

    Beritahu Django bahwa pernyataan ini mengandung sebuah pengumpulan dan bahwa sebuah klausa `GROUP BY` butuh ditambahkan ke permintaan.

  `contains_over_clause`

    Beritahu Django bahwa pernyataan ini mengandung pernyataan `Window`. Itu digunakan, sebagai contoh, melarang pernyataan fungsi-fungsi jendela dalam permintaan yang merubah data.

  `filterable`

    Memberitahu Django bahwa pernyataan ini dapat diacukan dalam `QuerySet.filter()`. Awalan ke `True`.

  `window_compatible`

    Beritahu Django bahwa pernyataan ini dapat digunakan sebagai pernyataan sumber dalam `Window`. Awalan menjadi `False`.

  `resolve_expression`(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)

    Provides the chance to do any pre-processing or validation of the expression before it's added to the query. `resolve_expression()` must also be called on any nested expressions. A `copy()` of `self` should be returned with any necessary transformations.

    `query` adalah backend penerapan query.

    `allow_joins` adalah boolean yang mengizinkan atau menolak penggunaan join dalam permintaan.

    `reuse` adalah kumpula dari penggunaan kembali join untuk skenario multi-join.

    `summarize` adalah boolean yang, ketika `True`, sinyal-sinyal yang meminta sedang dihitung adalah permintaan keseluruhan terminal.

    `for_save` is a boolean that, when `True`, signals that the query being executed is performing a create or update.

  `get_source_expressions`()

    Mengembalikan daftar terurut dari pernyataan paling dalam. Sebagai contoh:

        >>> Sum(F('foo')).get_source_expressions()
        [F('foo')]

  `set_source_expressions`(expressions)

    Mengambil daftar dari pernyataan dan menyimpan mereka seperti itu `get_source_expressions()` dapat mengembalikan mereka.

  `relabeled_clone`(change_map)

    Returns a clone (copy) of `self`, with any column aliases relabeled. Column aliases are renamed when subqueries are created. `relabeled_clone()` should also be called on any nested expressions and assigned to the clone.

    "change_map" adalah sebuah dictionary memetakan nama lain lama ke nama lain baru.

    Contoh:

        def relabeled_clone(self, change_map):
            clone = copy.copy(self)
            clone.expression = self.expression.relabeled_clone(change_map)
            return clone

  `convert_value`(value, expression, connection)

    Sebuah kaitan mengizinkan pernyataan untuk memaksa `value` menjadi lebih jenis yang sesuai.

  `get_group_by_cols`()

    Bertanggungjawab untuk mengembalikan daftar acuan kolom dengan pernyataan ini. `get_group_by_cols()` harus dipanggil pada pernyataan bersarang apapun. Obyek `F()`, khususnya, menahan acuan pada kolom.

  `asc`(nulls_first=False, nulls_last=False)

    Mengembalikan pernyataan siap untuk diurutkan dalam urutan menaik.

    `nulls_first` dan `nulls_last` menentukan bagaimana nilai-nilai null diurutkan. Lihat Menggunakan F() untuk mengurutkan nilai null untuk contoh penggunaan.

  `desc`(nulls_first=False, nulls_last=False)

    Mengembalikan pernyataan siap untuk diurutkan dalam urutan menurun.

    `nulls_first` dan `nulls_last` menentukan bagaimana nilai-nilai null diurutkan. Lihat Menggunakan F() untuk mengurutkan nilai null untuk contoh penggunaan.

  `reverse_ordering`()

    Mengembalikan `selft` dengan perubahan apapun diwajibkan untuk mengembalikan pilihan pengurutan dalam sebuah panggilan `order_by`. Sebagai sebuah contoh, sebuah pernyataan menerapkan `NULLS LAST` akan merubah nilainya menjadi `NULLS FIRST`. Perubahan-perubahan hanya diwajibkan untuk pernyataan yang menerapkan pilihat pengurutan seperti `OrderBy`. Metode ini dipanggil ketika `reverse()` pada sebuah kumpulan permintaan.

### menulis Pernyataan Permintaan anda sendiri

You can write your own query expression classes that use, and can integrate with, other query expressions. Let's step through an example by writing an implementation of the `COALESCE` SQL function, without using the built-in Func() expressions.

Fungsi SQL `COALESCE` ditentukan sebagai mengambil daftar kolom atau nilai. Itu akan mengembalikan kolom pertama atau nilai yang bukan `NULL`.

Kami akan memulai dengan menentukan cetakan untuk digunakan pembangkitan SQL dan sebuah metode `__init__()` untuk mensetel beberapa atribut:

    import copy
    from django.db.models import Expression

    class Coalesce(Expression):
        template = 'COALESCE( %(expressions)s )'

        def __init__(self, expressions, output_field):
          super().__init__(output_field=output_field)
          if len(expressions) < 2:
              raise ValueError('expressions must have at least 2 elements')
          for expression in expressions:
              if not hasattr(expression, 'resolve_expression'):
                  raise TypeError('%r is not an Expression' % expression)
          self.expressions = expressions

We do some basic validation on the parameters, including requiring at least 2 columns or values, and ensuring they are expressions. We are requiring `output_field` here so that Django knows what kind of model field to assign the eventual result to.

Now we implement the pre-processing and validation. Since we do not have any of our own validation at this point, we just delegate to the nested expressions:

    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
        c = self.copy()
        c.is_summary = summarize
        for pos, expression in enumerate(self.expressions):
            c.expressions[pos] = expression.resolve_expression(query, allow_joins, reuse, summarize, for_save)
        return c

Selanjutnya, kami menulis metode yang bertanggungjawab untuk membangkitkan SQL:

    def as_sql(self, compiler, connection, template=None):
        sql_expressions, sql_params = [], []
        for expression in self.expressions:
            sql, params = compiler.compile(expression)
            sql_expressions.append(sql)
            sql_params.extend(params)
        template = template or self.template
        data = {'expressions': ','.join(sql_expressions)}
        return template % data, params

    def as_oracle(self, compiler, connection):
        """
        Example of vendor specific handling (Oracle in this case).
        Let's make the function name lowercase.
        """
        return self.as_sql(compiler, connection, template='coalesce( %(expressions)s )')

`as_sql()` methods can support custom keyword arguments, allowing `as_vendorname()` methods to override data used to generate the SQL string. Using `as_sql()` keyword arguments for customization is preferable to mutating `self` within `as_vendorname()` methods as the latter can lead to errors when running on different database backends. If your class relies on class attributes to define data, consider allowing overrides in your `as_sql()` method.

We generate the SQL for each of the `expressions` by using the `compiler.compile()` method, and join the result together with commas. Then the template is filled out with our data and the SQL and parameters are returned.

We've also defined a custom implementation that is specific to the Oracle backend. The `as_oracle()` function will be called instead of `as_sql()` if the Oracle backend is in use.

Akhirnya, kami menerapkan sisa dari metode yang mengizinkan pernyataan permintaan kami bermain bagus dengan pernyataan permintaan lain:

    def get_source_expressions(self):
        return self.expressions

    def set_source_expressions(self, expressions):
        self.expressions = expressions

Mari kita lihat bagaimana itu bekerja:

    >>> from django.db.models import F, Value, CharField
    >>> qs = Company.objects.annotate(
    ...    tagline=Coalesce([
    ...        F('motto'),
    ...        F('ticker_name'),
    ...        F('description'),
    ...        Value('No Tagline')
    ...        ], output_field=CharField()))
    >>> for c in qs:
    ...     print("%s: %s" % (c.name, c.tagline))
    ...
    Google: Do No Evil
    Apple: AAPL
    Yahoo: Internet Company
    Django Software Foundation: No Tagline

#### Menghindari penyuntikan SQL

Since a `Func`'s keyword arguments for `__init__()` (`**extra`) and `as_sql()` (`**extra_context`) are interpolated into the SQL string rather than passed as query parameters (where the database driver would escape them), they must not contain untrusted user input.

Sebagai contoh, jika `substring` adalah disediakan-pengguna, fungsi ini rentan pada penyuntikan SQL:

    from django.db.models import Func

    class Position(Func):
        function = 'POSITION'
        template = "%(function)s('%(substring)s' in %(expressions)s)"

        def __init__(self, expression, substring):
            # substring=substring is a SQL injection vulnerability!
            super().__init__(expression, substring=substring)

This function generates a SQL string without any parameters. Since `substring` is passed to `super().__init__()` as a keyword argument, it's interpolated into the SQL string before the query is sent to the database.

Ini adalah tulisan kembali yang sudah diperiksa:

    class Position(Func):
        function = 'POSITION'
        arg_joiner = ' IN '

        def __init__(self, expression, substring):
            super().__init__(substring, expression)

Dengan `substring` bukannya dilewatkan sebagai argumen penempatan, itu akan dilewatkan sebagai parameter dalam permintaan basisdata.

### Menambahkan dukungan dalam backend basisdata pihak-ketiga

Jika anda sedang menggunakan backend basisdata yang menggunakan sintaksis SQL berbeda untuk fungsi tertentu, anda dapat menambah dukungan untuk itu dengan menambal metode baru kedalam kelas fungsi.

Mari kita katakan kami sedang menulis sebuah backend untuk SQL Server Microsoft yang menggunakan SQL `LEN` daripada `LENGTH` untuk fungsi `Length`. Kami akan membuat penambalan sebuah metode baru disebut `as_sqlserver()` ke dalam kelas `Length`:

    from django.db.models.functions import Length

    def sqlserver_length(self, compiler, connection):
        return self.as_sql(compiler, connection, function='LEN')

    Length.as_sqlserver = sqlserver_length

Anda dapat menyesuaikan SQL menggunakan parameter `template` dari `as_sql()`.

Kami menggunakan `as_sqlserver()` karena `django.db.connection.vendor` mengembalikan `sqlserver` untuk backend.

Backend pihak-ketiga dapat mendaftarkan fungsi0fungsi mereka dalam berkas `__init__.py` tingkat atas dari paket backend atau dalam berkas `expressions.py` (atau paket) tingkat atas yang diimpor dari `__init__.py` tngkat atas.

Untuk pengguna proyek yang berharap menambal backend yang mereka gunakan, kode ini harus berada dalam sebuah metode `AppConfig.ready()`.