Pernyataan F()¶

class F[sumber]¶

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

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

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

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

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

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

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

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)[sumber]¶

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)[sumber]¶

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):
        return super().as_sql(
            compiler, connection,
            function='CONCAT_WS',
            template="%(function)s('', %(expressions)s)",
        )

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, filter=None, **extra)[sumber]¶

template¶

Sebuah atribut kelas, sebagai sebuah bentuk string, yang menggambarkan SQL yaitu dibangkitkan untuk pengumpulan ini. Awalan pada '%(function)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¶

New in Django 2.0:

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

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.

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.

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 Count(Aggregate):
    # supports COUNT(distinct field)
    function = 'COUNT'
    template = '%(function)s(%(distinct)s%(expressions)s)'

    def __init__(self, expression, distinct=False, **extra):
        super().__init__(
            expression,
            distinct='DISTINCT ' if distinct else '',
            output_field=IntegerField(),
            **extra
        )

Pernyataan Value()¶

class Value(value, output_field=None)[sumber]¶

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)[sumber]¶

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)[sumber]¶

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"

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

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

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

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

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"

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

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

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

Pernyataan SQL mentah¶

class RawSQL(sql, params, output_field=None)[sumber]¶

Sometimes database expressions can't easily express a complex WHERE clause. In these edge cases, use the RawSQL expression. For example:

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

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

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)[sumber]¶

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.

The frame parameter specifies which other rows that should be used in the computation. See Kerangka for details.

For example, to annotate each movie with the average rating for the movies by the same studio in the same genre and release year:

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

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

ValueRange(start=0, end=0)

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

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

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[sumber]¶

contains_aggregate¶

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

contains_over_clause¶

New in Django 2.0:

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

filterable¶

New in Django 2.0:

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

window_compatible¶

New in Django 2.0:

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.

get_source_expressions()¶

Mengembalikan daftar terurut dari pernyataan paling dalam. Sebagai contoh:

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

set_source_expressions(expressions)¶

Takes a list of expressions and stores them such that get_source_expressions() can return them.

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

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)

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

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

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