クエリ式
クエリ式は、更新、作成、フィルタリング、順序付け、注釈、または集計の一部として使用できる値または計算を記述します。 式がブール値を出力する場合、フィルターで直接使用できます。 クエリの作成に役立つ組み込み式(以下に記載)がいくつかあります。 式を組み合わせたり、場合によってはネストして、より複雑な計算を形成することができます。
サポートされている算術
Djangoは、Pythonの定数、変数、さらには他の式を使用して、否定、加算、減算、乗算、除算、モジュロ算術、およびクエリ式の累乗演算子をサポートしています。
いくつかの例
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')
# Boolean expression can be used directly in filters.
from django.db.models import Exists
Company.objects.filter(
Exists(Employee.objects.filter(company=OuterRef('pk'), salary__gt=10))
)
組み込み式
ノート
これらの式はdjango.db.models.expressions
とdjango.db.models.aggregates
で定義されていますが、便宜上、 django.db.models から利用できます。
F()式
- class F
F()
オブジェクトは、モデルフィールドまたは注釈付き列の値を表します。 モデルフィールド値を参照し、それらを使用してデータベース操作を実行することができます。実際にデータベースからPythonメモリにプルする必要はありません。
代わりに、DjangoはF()
オブジェクトを使用して、データベースレベルで必要な操作を説明するSQL式を生成します。
例を挙げてこれを試してみましょう。 通常、次のようなことを行う可能性があります。
# Tintin filed a news story!
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed += 1
reporter.save()
ここでは、reporter.stories_filed
の値をデータベースからメモリにプルし、使い慣れたPython演算子を使用して操作してから、オブジェクトをデータベースに保存し直しました。 しかし、代わりに次のこともできます。
from django.db.models import F
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()
reporter.stories_filed = F('stories_filed') + 1
は、インスタンス属性への通常のPythonの値の割り当てのように見えますが、実際には、データベースでの操作を記述するSQL構造です。
DjangoがF()
のインスタンスに遭遇すると、標準のPython演算子をオーバーライドして、カプセル化されたSQL式を作成します。 この場合、reporter.stories_filed
で表されるデータベースフィールドをインクリメントするようにデータベースに指示するもの。
reporter.stories_filed
にある、またはあった値が何であれ、Pythonはそれを知ることはありません。データベースによって完全に処理されます。 Pythonが行うのは、DjangoのF()
クラスを介して、フィールドを参照し、操作を説明するSQL構文を作成することだけです。
この方法で保存された新しい値にアクセスするには、オブジェクトを再ロードする必要があります。
reporter = Reporters.objects.get(pk=reporter.pk)
# Or, more succinctly:
reporter.refresh_from_db()
F()
は、上記のような単一インスタンスでの操作に使用できるだけでなく、update()
を使用してオブジェクトインスタンスのQuerySets
でも使用できます。 これにより、上記で使用していた2つのクエリget()
と save()が1つに減ります。
reporter = Reporters.objects.filter(name='Tintin')
reporter.update(stories_filed=F('stories_filed') + 1)
update()を使用して、複数のオブジェクトのフィールド値をインクリメントすることもできます。これは、データベースからすべてのオブジェクトをPythonにプルし、ループして、各オブジェクトのフィールド値をインクリメントするよりもはるかに高速です。それぞれをデータベースに保存し直します。
Reporter.objects.all().update(stories_filed=F('stories_filed') + 1)
したがって、F()
は、次のようなパフォーマンス上の利点を提供できます。
- Pythonではなくデータベースを使用して作業を行う
- 一部の操作に必要なクエリの数を減らす
F()を使用した競合状態の回避
F()
のもう1つの便利な利点は、Pythonではなくデータベースでフィールドの値を更新することで競合状態を回避できることです。
2つのPythonスレッドが上記の最初の例のコードを実行する場合、一方のスレッドは、もう一方のスレッドがデータベースからフィールドの値を取得した後に、フィールドの値を取得、インクリメント、および保存できます。 2番目のスレッドが保存する値は、元の値に基づきます。 最初のスレッドの作業は失われます。
データベースがフィールドの更新を担当している場合、プロセスはより堅牢になります。 save()またはupdate()
の場合にのみ、データベース内のフィールドの値に基づいてフィールドが更新されます。 ]は、インスタンスが取得されたときの値に基づくのではなく、実行されます。
F()の割り当ては、Model.save()の後も持続します
モデルフィールドに割り当てられたF()
オブジェクトは、モデルインスタンスを保存した後も存続し、各 save()に適用されます。 例えば:
reporter = Reporters.objects.get(name='Tintin')
reporter.stories_filed = F('stories_filed') + 1
reporter.save()
reporter.name = 'Tintin Jr.'
reporter.save()
この場合、stories_filed
は2回更新されます。 最初が1
の場合、最終的な値は3
になります。 この永続性は、たとえば refresh_from_db()を使用して、モデルオブジェクトを保存した後に再ロードすることで回避できます。
フィルタでF()を使用する
F()
は、QuerySet
フィルターでも非常に役立ちます。このフィルターを使用すると、Python値ではなく、フィールド値に基づいて基準に対してオブジェクトのセットをフィルター処理できます。
これは、クエリでF()式を使用するに記載されています。
F()を注釈付きで使用する
F()
を使用すると、さまざまなフィールドを算術演算と組み合わせて、モデルに動的フィールドを作成できます。
company = Company.objects.annotate(
chairs_needed=F('num_employees') - F('num_chairs'))
組み合わせるフィールドのタイプが異なる場合は、返されるフィールドの種類をDjangoに通知する必要があります。 F()
はoutput_field
を直接サポートしていないため、式を ExpressionWrapper でラップする必要があります。
from django.db.models import DateTimeField, ExpressionWrapper, F
Ticket.objects.annotate(
expires=ExpressionWrapper(
F('active_at') + F('duration'), output_field=DateTimeField()))
ForeignKey
などのリレーショナルフィールドを参照する場合、F()
はモデルインスタンスではなく主キー値を返します。
>> car = Company.objects.annotate(built_by=F('manufacturer'))[0]
>> car.manufacturer
<Manufacturer: Toyota>
>> car.built_by
3
F()を使用してnull値を並べ替える
F()
およびnulls_first
またはnulls_last
キーワード引数を Expression.asc()または desc()に使用して、順序を制御しますフィールドのnull値の。 デフォルトでは、順序はデータベースによって異なります。
たとえば、連絡を受けていない会社(last_contacted
はnull)を、連絡を受けた会社の後に並べ替えるには、次のようにします。
from django.db.models import F
Company.objects.order_by(F('last_contacted').desc(nulls_last=True))
Func()式
Func()
式は、COALESCE
やLOWER
などのデータベース関数、またはSUM
などの集計を含むすべての式の基本タイプです。 それらは直接使用できます:
from django.db.models import F, Func
queryset.annotate(field_lower=Func(F('field'), function='LOWER'))
または、データベース関数のライブラリを構築するために使用できます。
class Lower(Func):
function = 'LOWER'
queryset.annotate(field_lower=Lower('field'))
ただし、どちらの場合も、おおよそ次のSQLから生成された追加の属性field_lower
で各モデルに注釈が付けられたクエリセットが生成されます。
SELECT
...
LOWER("db_table"."field") as "field_lower"
組み込みデータベース関数の一覧については、データベース関数を参照してください。
Func
APIは次のとおりです。
- class Func(*expressions, **extra)
- function
生成される関数を説明するクラス属性。 具体的には、
function
は、テンプレート内のfunction
プレースホルダーとして補間されます。 デフォルトはNone
です。
- template
この関数用に生成されるSQLを説明するフォーマット文字列としてのクラス属性。 デフォルトは
'%(function)s(%(expressions)s)'
です。strftime('%W', 'date')
のようなSQLを作成していて、クエリでリテラル%
文字が必要な場合は、template
属性で4倍(%%%%
)にします。は2回補間されます。1回はas_sql()
のテンプレート補間中に、もう1回はデータベースカーソルのクエリパラメータを使用したSQL補間中に行われます。
- arg_joiner
expressions
のリストを結合するために使用される文字を示すクラス属性。 デフォルトは', '
です。
- arity
関数が受け入れる引数の数を示すクラス属性。 この属性が設定されていて、関数が異なる数の式で呼び出された場合、
TypeError
が発生します。 デフォルトはNone
です。
- as_sql(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)
データベース関数のSQLフラグメントを生成します。 タプル
(sql, params)
を返します。ここで、sql
はSQL文字列であり、params
はクエリパラメータのリストまたはタプルです。as_vendor()
メソッドは、function
、template
、arg_joiner
、およびその他の**extra_context
パラメーターを使用して、必要に応じてSQLをカスタマイズする必要があります。 例えば: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 )
SQLインジェクションの脆弱性を回避するには、
extra_context
に信頼できないユーザー入力を含めないでください。これらの値は、データベースドライバーがエスケープするクエリパラメーターとして渡されるのではなく、SQL文字列に補間されます。
*expressions
引数は、関数が適用される位置式のリストです。 式は文字列に変換され、arg_joiner
で結合されてから、expressions
プレースホルダーとしてtemplate
に補間されます。
位置引数には、式またはPython値を指定できます。 文字列は列参照と見なされ、F()
式でラップされ、他の値はValue()
式でラップされます。
**extra
kwargsは、template
属性に補間できるkey=value
ペアです。 SQLインジェクションの脆弱性を回避するには、extra
に信頼できないユーザー入力を含めないでください。これらの値は、データベースドライバーがエスケープするクエリパラメーターとして渡されるのではなく、SQL文字列に補間されます。
function
、template
、およびarg_joiner
キーワードを使用すると、独自のクラスを定義しなくても、同じ名前の属性を置き換えることができます。 output_field
を使用して、期待されるリターンタイプを定義できます。
Aggregate()式
集計式は、 Func()式の特殊なケースであり、GROUP BY
句が必要であることをクエリに通知します。 Sum()
やCount()
など、すべての集計関数は、Aggregate()
から継承します。
Aggregate
は式とラップ式であるため、いくつかの複雑な計算を表すことができます。
from django.db.models import Count
Company.objects.annotate(
managers_required=(Count('num_employees') / 4) + Count('num_managers'))
Aggregate
APIは次のとおりです。
- class Aggregate(*expressions, output_field=None, distinct=False, filter=None, **extra)
- template
この集計用に生成されるSQLを説明するフォーマット文字列としてのクラス属性。 デフォルトは
'%(function)s(%(distinct)s%(expressions)s)'
です。
- function
生成される集計関数を説明するクラス属性。 具体的には、
function
は、テンプレート内のfunction
プレースホルダーとして補間されます。 デフォルトはNone
です。
- window_compatible
ほとんどの集計関数は Window のソース式として使用できるため、デフォルトは
True
です。
- allow_distinct
バージョン2.2の新機能。
この集計関数が
distinct
キーワード引数の受け渡しを許可するかどうかを決定するクラス属性。False
(デフォルト)に設定されている場合、distinct=True
が渡されると、TypeError
が発生します。
expressions
の位置引数には、式またはモデルフィールドの名前を含めることができます。 これらは文字列に変換され、template
内のexpressions
プレースホルダーとして使用されます。
output_field
引数には、IntegerField()
やBooleanField()
などのモデルフィールドインスタンスが必要です。Djangoは、データベースから値を取得した後、このインスタンスに値を読み込みます。 データ検証に関連する引数(max_length
、max_digits
など)は式の出力値に適用されないため、通常、モデルフィールドをインスタンス化するときに引数は必要ありません。
output_field
は、Djangoが結果のフィールドタイプを判別できない場合にのみ必要であることに注意してください。 フィールドタイプを混合する複雑な式は、目的のoutput_field
を定義する必要があります。 たとえば、IntegerField()
とFloatField()
を一緒に追加すると、おそらくoutput_field=FloatField()
が定義されているはずです。
distinct
引数は、expressions
の個別の値(または複数のexpressions
の場合は値のセット)ごとに集計関数を呼び出すかどうかを決定します。 この引数は、 allow_distinct がTrue
に設定されているアグリゲートでのみサポートされます。
filter
引数は、集約される行をフィルタリングするために使用される Qオブジェクトを取ります。 使用例については、条件付き集計および注釈のフィルタリングを参照してください。
**extra
kwargsは、template
属性に補間できるkey=value
ペアです。
バージョン2.2の新機能: allow_distinct
属性とdistinct
引数が追加されました。
独自の集計関数を作成する
独自の集計関数を作成することもできます。 少なくともfunction
を定義する必要がありますが、生成されるSQLを完全にカスタマイズすることもできます。 簡単な例を次に示します。
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
)
Value()式
- class Value(value, output_field=None)
Value()
オブジェクトは、式の可能な最小のコンポーネントである単純な値を表します。 式内で整数、ブール値、または文字列の値を表す必要がある場合は、その値をValue()
内でラップできます。
Value()
を直接使用する必要はほとんどありません。 式F('field') + 1
を記述すると、Djangoは暗黙的に1
をValue()
でラップし、より複雑な式で単純な値を使用できるようにします。 式に文字列を渡す場合は、Value()
を使用する必要があります。 ほとんどの式は、Lower('name')
のように、文字列引数をフィールドの名前として解釈します。
value
引数は、1
、True
、None
など、式に含まれる値を記述します。 Djangoは、これらのPython値を対応するデータベースタイプに変換する方法を知っています。
output_field
引数は、IntegerField()
やBooleanField()
などのモデルフィールドインスタンスである必要があります。このインスタンスに、Djangoはデータベースから取得した後に値を読み込みます。 データ検証に関連する引数(max_length
、max_digits
など)は式の出力値に適用されないため、通常、モデルフィールドをインスタンス化するときに引数は必要ありません。
ExpressionWrapper()式
- class ExpressionWrapper(expression, output_field)
ExpressionWrapper
は別の式を囲み、output_field
などの他の式では使用できないプロパティへのアクセスを提供します。 注釈付きのF()の使用で説明されているように、異なるタイプのF()
式で算術演算を使用する場合は、ExpressionWrapper
が必要です。
Subquery()式
- class Subquery(queryset, output_field=None)
Subquery
式を使用して、QuerySet
に明示的なサブクエリを追加できます。
たとえば、各投稿に、その投稿に対する最新のコメントの作成者の電子メールアドレスで注釈を付けるには、次のようにします。
>>> 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]))
PostgreSQLでは、SQLは次のようになります。
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"
ノート
このセクションの例は、Djangoにサブクエリを強制的に実行させる方法を示すように設計されています。 場合によっては、同じタスクをより明確または効率的に実行する同等のクエリセットを作成できる可能性があります。
外部クエリセットからの列の参照
- class OuterRef(field)
Subquery
のクエリセットが外部クエリのフィールドを参照する必要がある場合は、OuterRef
を使用します。 F 式のように機能しますが、有効なフィールドを参照しているかどうかのチェックは、外部クエリセットが解決されるまで行われません。
OuterRef
のインスタンスは、Subquery
のネストされたインスタンスと組み合わせて使用して、直接の親ではない包含クエリセットを参照できます。 たとえば、正しく解決するには、このクエリセットがSubquery
インスタンスのネストされたペア内にある必要があります。
>>> Book.objects.filter(author=OuterRef(OuterRef('pk')))
サブクエリを単一の列に制限する
たとえば、__in
ルックアップのターゲットとしてSubquery
を使用するために、Subquery
から単一の列を返す必要がある場合があります。 最終日以内に公開された投稿に対するすべてのコメントを返すには:
>>> 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')))
この場合、サブクエリは values()を使用して、投稿の主キーである単一の列のみを返す必要があります。
サブクエリを単一の行に制限する
サブクエリが複数の行を返さないようにするために、クエリセットのスライス([:1]
)が使用されます。
>>> subquery = Subquery(newest.values('email')[:1])
>>> Post.objects.annotate(newest_commenter_email=subquery)
この場合、サブクエリは単一の列との単一の行(最後に作成されたコメントの電子メールアドレス)のみを返す必要があります。
(スライスの代わりに get()を使用すると、クエリセットがSubquery
内で使用されるまでOuterRef
を解決できないため、失敗します。)
Exists()サブクエリ
- class Exists(queryset)
Exists
は、SQL EXISTS
ステートメントを使用する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))
PostgreSQLでは、SQLは次のようになります。
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"
Exists
に単一の列を参照させる必要はありません。列が破棄され、ブール結果が返されるためです。 同様に、SQL EXISTS
サブクエリ内では順序付けは重要ではなく、パフォーマンスを低下させるだけなので、自動的に削除されます。
NOT EXISTS
と~Exists()
を使用してクエリを実行できます。
Subquery()またはExists()式でのフィルタリング
ブール値を返すSubquery()
とExists()
は、 When 式のcondition
として、またはクエリセットを直接フィルタリングするために使用できます。
>>> recent_comments = Comment.objects.filter(...) # From above
>>> Post.objects.filter(Exists(recent_comments))
これにより、サブクエリがSELECT
列に追加されなくなり、パフォーマンスが向上する可能性があります。
バージョン3.0で変更: Djangoの以前のバージョンでは、最初に注釈を付けてから、注釈に対してフィルタリングする必要がありました。 これにより、注釈付きの値が常にクエリ結果に存在し、クエリの実行に時間がかかることがよくありました。
Subquery式内での集計の使用
集計はSubquery
内で使用できますが、 filter()、 values()、および annotate()の特定の組み合わせが必要です。サブクエリのグループ化を正しく行うため。
両方のモデルにlength
フィールドがあると仮定して、投稿の長さがすべての結合されたコメントの全長よりも長い投稿を検索するには、次のようにします。
>>> 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))
最初のfilter(...)
は、サブクエリを関連するパラメータに制限します。 order_by()
は、Comment
モデルのデフォルトの注文(存在する場合)を削除します。 values('post')
は、コメントをPost
で集約します。 最後に、annotate(...)
が集計を実行します。 これらのクエリセットメソッドが適用される順序は重要です。 この場合、サブクエリは単一の列に制限する必要があるため、values('total')
が必要です。
これは、Subquery
内で集計を実行する唯一の方法です。これは、 Aggregate()を使用してクエリセットを評価しようとするためです(OuterRef
がある場合、これは行われません。解決可能)。
生のSQL式
- class RawSQL(sql, params, output_field=None)
データベース式では、複雑なWHERE
句を簡単に表現できない場合があります。 これらのエッジケースでは、RawSQL
式を使用してください。 例えば:
>>> from django.db.models.expressions import RawSQL
>>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))
これらの追加のルックアップは、(SQLコードを明示的に記述しているため)異なるデータベースエンジンに移植できず、DRYの原則に違反する可能性があるため、可能であれば回避する必要があります。
警告
SQLインジェクション攻撃から保護するには、params
を使用してユーザーが制御できるパラメーターをエスケープする必要があります。 params
は、SQLをユーザー提供のデータで補間していないことを確認するために必要な引数です。
また、SQL文字列でプレースホルダーを引用符で囲まないでください。 この例は、%s
を引用符で囲んでいるため、SQLインジェクションに対して脆弱です。
RawSQL("select col from sometable where othercol = '%s'") # unsafe!
Djangoの SQLインジェクション保護がどのように機能するかについて詳しく読むことができます。
ウィンドウ関数
ウィンドウ関数は、パーティションに関数を適用する方法を提供します。 group byで定義された各セットの最終結果を計算する通常の集計関数とは異なり、ウィンドウ関数はフレームとパーティションで動作し、各行の結果を計算します。
同じクエリで複数のウィンドウを指定できます。これは、Django ORMでは、 QuerySet.annotate()呼び出しに複数の式を含めるのと同じです。 ORMは名前付きウィンドウを使用せず、代わりに選択された列の一部です。
- class Window(expression, partition_by=None, order_by=None, frame=None, output_field=None)
- filterable
デフォルトは
False
です。 SQL標準では、WHERE
句でウィンドウ関数を参照することは許可されておらず、Djangoはそれを行うQuerySet
を構築するときに例外を発生させます。
- template
デフォルトは
%(expression)s OVER (%(window)s)'
です。expression
引数のみが指定されている場合、window句は空白になります。
Window
クラスは、OVER
句の主な式です。
expression
引数は、ウィンドウ関数、集計関数、またはウィンドウ句で互換性のある式のいずれかです。
partition_by
引数は、行の分割を制御する式のリストです(列名はF
オブジェクトでラップする必要があります)。 パーティション化により、結果セットの計算に使用される行が絞り込まれます。
output_field
は、引数または式のいずれかで指定されます。
order_by
引数は、 asc()および desc()を呼び出すことができる一連の式を受け入れます。 順序は、式が適用される順序を制御します。 たとえば、パーティション内の行を合計すると、最初の結果は最初の行の値になり、2番目の結果は最初と2番目の行の合計になります。
frame
パラメーターは、計算で使用する必要がある他の行を指定します。 詳細については、フレームを参照してください。
たとえば、同じジャンルとリリース年の同じスタジオによる映画の平均評価で各映画に注釈を付けるには、次のようにします。
>>> 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(),
>>> ),
>>> )
これにより、映画の評価が他の映画よりも良いか悪いかを確認できます。
同じウィンドウ、つまり同じパーティションとフレームに複数の式を適用したい場合があります。 たとえば、前の例を変更して、同じクエリで3つのウィンドウ関数を使用することにより、各映画のグループ(同じスタジオ、ジャンル、リリース年)に最高と最低の評価を含めることができます。 前の例のパーティションと順序は、繰り返しを減らすために辞書に抽出されます。
>>> 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,
>>> ),
>>> )
Djangoの組み込みデータベースバックエンドの中で、MySQL 8.0.2 +、PostgreSQL、およびOracleはウィンドウ式をサポートしています。 さまざまなウィンドウ式機能のサポートは、データベースによって異なります。 たとえば、 asc()および desc()のオプションはサポートされていない可能性があります。 必要に応じて、データベースのドキュメントを参照してください。
フレーム
ウィンドウフレームの場合、範囲ベースの行のシーケンスまたは通常の行のシーケンスのいずれかを選択できます。
- class ValueRange(start=None, end=None)
- frame_type
この属性は
'RANGE'
に設定されます。
PostgreSQLは
ValueRange
のサポートが制限されており、CURRENT ROW
やUNBOUNDED FOLLOWING
などの標準の開始点と終了点の使用のみをサポートします。
- class RowRange(start=None, end=None)
- ;; frame_type
- この属性は
'ROWS'
に設定されます。
どちらのクラスも、テンプレートを使用してSQLを返します。
%(frame_type)s BETWEEN %(start)s AND %(end)s
フレームは、結果の計算に使用される行を絞り込みます。 それらは、ある開始点からある指定された終了点にシフトします。 フレームはパーティションの有無にかかわらず使用できますが、決定論的な結果を保証するために、ウィンドウの順序を指定することをお勧めします。 フレーム内のフレーム内のピアは、同等の値を持つ行、または順序句が存在しない場合はすべての行です。
フレームのデフォルトの開始点は、パーティションの最初の行であるUNBOUNDED PRECEDING
です。 エンドポイントは常にORMによって生成されたSQLに明示的に含まれ、デフォルトではUNBOUNDED FOLLOWING
です。 デフォルトのフレームには、パーティションからセットの最後の行までのすべての行が含まれます。
start
およびend
引数に受け入れられる値は、None
、整数、またはゼロです。 start
の負の整数は、N preceding
になり、None
はUNBOUNDED PRECEDING
になります。 start
とend
の両方で、ゼロはCURRENT ROW
を返します。 end
には正の整数を使用できます。
CURRENT ROW
に含まれるものには違いがあります。 ROWS
モードで指定すると、フレームは現在の行で開始または終了します。 RANGE
モードで指定すると、フレームはordering句に従って最初または最後のピアで開始または終了します。 したがって、RANGE CURRENT ROW
は、順序付けで指定された値と同じ値を持つ行の式を評価します。 テンプレートにはstart
ポイントとend
ポイントの両方が含まれているため、これは次のように表すことができます。
ValueRange(start=0, end=0)
映画の「ピア」が同じ年に同じジャンルの同じスタジオからリリースされた映画として説明されている場合、このRowRange
の例では、各映画に、映画の前の2つのピアと次の2つのピアの平均評価を付けます。
>>> 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),
>>> ),
>>> )
データベースがサポートしている場合は、パーティション内の式の値に基づいて開始点と終了点を指定できます。 Movie
モデルのreleased
フィールドに各映画のリリース月が保存されている場合、このValueRange
の例では、12か月前にリリースされた映画のピアの平均評価で各映画に注釈を付けます。そして各映画の12ヶ月後。
>>> 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と例は、Djangoが提供する組み込み機能を拡張できる汎用クエリ式の作成に役立ちます。
式API
クエリ式はクエリ式API を実装しますが、以下にリストされているいくつかの追加のメソッドと属性も公開します。 すべてのクエリ式は、Expression()
または関連するサブクラスから継承する必要があります。
クエリ式が別の式をラップする場合、ラップされた式で適切なメソッドを呼び出す必要があります。
- class Expression
- contains_aggregate
この式に集計が含まれていること、および
GROUP BY
句をクエリに追加する必要があることをDjangoに通知します。
- contains_over_clause
この式に Window 式が含まれていることをDjangoに通知します。 たとえば、データを変更するクエリでウィンドウ関数式を禁止するために使用されます。
- filterable
この式は QuerySet.filter()で参照できることをDjangoに通知します。 デフォルトは
True
です。
- window_compatible
この式を Window のソース式として使用できることをDjangoに通知します。 デフォルトは
False
です。
- resolve_expression(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)
式をクエリに追加する前に、式の前処理または検証を行う機会を提供します。
resolve_expression()
は、ネストされた式でも呼び出す必要があります。self
のcopy()
は、必要な変換とともに返される必要があります。query
はバックエンドクエリの実装です。allow_joins
は、クエリでの結合の使用を許可または拒否するブール値です。reuse
は、マルチ結合シナリオ用の再利用可能な結合のセットです。summarize
はブール値であり、True
の場合、計算中のクエリがターミナル集約クエリであることを通知します。for_save
はブール値であり、True
の場合、実行中のクエリが作成または更新を実行していることを通知します。
- get_source_expressions()
内部式の順序付きリストを返します。 例えば:
>>> Sum(F('foo')).get_source_expressions() [F('foo')]
- set_source_expressions(expressions)
式のリストを取得し、
get_source_expressions()
が返すことができるようにそれらを格納します。
- relabeled_clone(change_map)
self
のクローン(コピー)を返し、列エイリアスのラベルを付け直します。 サブクエリが作成されると、列エイリアスの名前が変更されます。relabeled_clone()
は、ネストされた式でも呼び出され、クローンに割り当てられる必要があります。change_map
は、古いエイリアスを新しいエイリアスにマッピングする辞書です。例:
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)
式が
value
をより適切なタイプに強制変換できるようにするフック。expression
はself
と同じです。
- get_group_by_cols(alias=None)
この式によって列参照のリストを返す責任があります。
get_group_by_cols()
は、ネストされた式で呼び出す必要があります。 特に、F()
オブジェクトは、列への参照を保持します。alias
パラメーターは、式に注釈が付けられ、グループ化に使用されていない限り、None
になります。バージョン3.0で変更:
alias
パラメーターが追加されました。
- asc(nulls_first=False, nulls_last=False)
昇順でソートする準備ができている式を返します。
nulls_first
およびnulls_last
は、null値のソート方法を定義します。 使用例については、 F()を使用したnull値の並べ替えを参照してください。
- desc(nulls_first=False, nulls_last=False)
降順でソートする準備ができている式を返します。
nulls_first
およびnulls_last
は、null値のソート方法を定義します。 使用例については、 F()を使用したnull値の並べ替えを参照してください。
- reverse_ordering()
order_by
呼び出し内でソート順を逆にするために必要な変更を加えて、self
を返します。 例として、NULLS LAST
を実装する式は、その値をNULLS FIRST
に変更します。 変更は、OrderBy
のようなソート順を実装する式にのみ必要です。 このメソッドは、クエリセットで reverse()が呼び出されたときに呼び出されます。
独自のクエリ式を作成する
他のクエリ式を使用し、他のクエリ式と統合できる独自のクエリ式クラスを作成できます。 組み込みの Func()式を使用せずに、COALESCE
SQL関数の実装を記述して例を見ていきましょう。
COALESCE
SQL関数は、列または値のリストを取得するものとして定義されています。 NULL
ではない最初の列または値を返します。
まず、SQL生成に使用するテンプレートと、いくつかの属性を設定するための__init__()
メソッドを定義します。
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
少なくとも2つの列または値を必要とし、それらが式であることを確認するなど、パラメーターに対していくつかの基本的な検証を行います。 Djangoが最終的な結果を割り当てるモデルフィールドの種類を認識できるように、ここではoutput_field
が必要です。
次に、前処理と検証を実装します。 この時点では独自の検証がないため、ネストされた式に委任します。
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
次に、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, sql_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()
メソッドはカスタムキーワード引数をサポートできるため、as_vendorname()
メソッドはSQL文字列の生成に使用されるデータをオーバーライドできます。 カスタマイズにas_sql()
キーワード引数を使用することは、as_vendorname()
メソッド内でself
を変更するよりも望ましい方法です。後者は、異なるデータベースバックエンドで実行するとエラーが発生する可能性があるためです。 クラスがデータを定義するためにクラス属性に依存している場合は、as_sql()
メソッドでオーバーライドを許可することを検討してください。
compiler.compile()
メソッドを使用してexpressions
ごとにSQLを生成し、結果をコンマで結合します。 次に、テンプレートにデータが入力され、SQLとパラメーターが返されます。
また、Oracleバックエンドに固有のカスタム実装も定義しました。 Oracleバックエンドが使用されている場合、as_sql()
の代わりにas_oracle()
関数が呼び出されます。
最後に、クエリ式が他のクエリ式とうまく連携できるようにする残りのメソッドを実装します。
def get_source_expressions(self):
return self.expressions
def set_source_expressions(self, expressions):
self.expressions = expressions
それがどのように機能するか見てみましょう:
>>> 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
SQLインジェクションの回避
Func
の__init__()
(**extra
)およびas_sql()
(**extra_context
)のキーワード引数は、SQL文字列ではなく補間されるためです。クエリパラメータ(データベースドライバがそれらをエスケープする場所)として渡される場合、信頼できないユーザー入力を含めることはできません。
たとえば、substring
がユーザー提供の場合、この関数は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 an SQL injection vulnerability!
super().__init__(expression, substring=substring)
この関数は、パラメーターなしでSQL文字列を生成します。 substring
はキーワード引数としてsuper().__init__()
に渡されるため、クエリがデータベースに送信される前にSQL文字列に補間されます。
修正された書き直しは次のとおりです。
class Position(Func):
function = 'POSITION'
arg_joiner = ' IN '
def __init__(self, expression, substring):
super().__init__(substring, expression)
代わりにsubstring
が位置引数として渡されると、データベースクエリのパラメーターとして渡されます。
サードパーティのデータベースバックエンドでのサポートの追加
特定の関数に異なるSQL構文を使用するデータベースバックエンドを使用している場合は、関数のクラスに新しいメソッドをモンキーパッチすることで、そのサポートを追加できます。
Length 関数にLENGTH
の代わりにSQL LEN
を使用するMicrosoftのSQLServerのバックエンドを作成しているとしましょう。 as_sqlserver()
という新しいメソッドを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
as_sql()
のtemplate
パラメーターを使用してSQLをカスタマイズすることもできます。
django.db.connection.vendor
はバックエンドにsqlserver
を返すため、as_sqlserver()
を使用します。
サードパーティのバックエンドは、バックエンドパッケージのトップレベル__init__.py
ファイル、またはトップレベル [からインポートされたトップレベルexpressions.py
ファイル(またはパッケージ)に機能を登録できます。 X191X]。
使用しているバックエンドにパッチを適用したいユーザープロジェクトの場合、このコードは AppConfig.ready()メソッドに存在する必要があります。