expressions.txt 52 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383
  1. =================
  2. Query Expressions
  3. =================
  4. .. currentmodule:: django.db.models
  5. Query expressions describe a value or a computation that can be used as part of
  6. an update, create, filter, order by, annotation, or aggregate. When an
  7. expression outputs a boolean value, it may be used directly in filters. There
  8. are a number of built-in expressions (documented below) that can be used to
  9. help you write queries. Expressions can be combined, or in some cases nested,
  10. to form more complex computations.
  11. Supported arithmetic
  12. ====================
  13. Django supports negation, addition, subtraction, multiplication, division,
  14. modulo arithmetic, and the power operator on query expressions, using Python
  15. constants, variables, and even other expressions.
  16. .. _output-field:
  17. Output field
  18. ============
  19. Many of the expressions documented in this section support an optional
  20. ``output_field`` parameter. If given, Django will load the value into that
  21. field after retrieving it from the database.
  22. ``output_field`` takes a model field instance, like ``IntegerField()`` or
  23. ``BooleanField()``. Usually, the field doesn't need any arguments, like
  24. ``max_length``, since field arguments relate to data validation which will not
  25. be performed on the expression's output value.
  26. ``output_field`` is only required when Django is unable to automatically
  27. determine the result's field type, such as complex expressions that mix field
  28. types. For example, adding a ``DecimalField()`` and a ``FloatField()`` requires
  29. an output field, like ``output_field=FloatField()``.
  30. Some examples
  31. =============
  32. .. code-block:: pycon
  33. >>> from django.db.models import Count, F, Value
  34. >>> from django.db.models.functions import Length, Upper
  35. >>> from django.db.models.lookups import GreaterThan
  36. # Find companies that have more employees than chairs.
  37. >>> Company.objects.filter(num_employees__gt=F("num_chairs"))
  38. # Find companies that have at least twice as many employees
  39. # as chairs. Both the querysets below are equivalent.
  40. >>> Company.objects.filter(num_employees__gt=F("num_chairs") * 2)
  41. >>> Company.objects.filter(num_employees__gt=F("num_chairs") + F("num_chairs"))
  42. # How many chairs are needed for each company to seat all employees?
  43. >>> company = (
  44. ... Company.objects.filter(num_employees__gt=F("num_chairs"))
  45. ... .annotate(chairs_needed=F("num_employees") - F("num_chairs"))
  46. ... .first()
  47. ... )
  48. >>> company.num_employees
  49. 120
  50. >>> company.num_chairs
  51. 50
  52. >>> company.chairs_needed
  53. 70
  54. # Create a new company using expressions.
  55. >>> company = Company.objects.create(name="Google", ticker=Upper(Value("goog")))
  56. # Be sure to refresh it if you need to access the field.
  57. >>> company.refresh_from_db()
  58. >>> company.ticker
  59. 'GOOG'
  60. # Annotate models with an aggregated value. Both forms
  61. # below are equivalent.
  62. >>> Company.objects.annotate(num_products=Count("products"))
  63. >>> Company.objects.annotate(num_products=Count(F("products")))
  64. # Aggregates can contain complex computations also
  65. >>> Company.objects.annotate(num_offerings=Count(F("products") + F("services")))
  66. # Expressions can also be used in order_by(), either directly
  67. >>> Company.objects.order_by(Length("name").asc())
  68. >>> Company.objects.order_by(Length("name").desc())
  69. # or using the double underscore lookup syntax.
  70. >>> from django.db.models import CharField
  71. >>> from django.db.models.functions import Length
  72. >>> CharField.register_lookup(Length)
  73. >>> Company.objects.order_by("name__length")
  74. # Boolean expression can be used directly in filters.
  75. >>> from django.db.models import Exists, OuterRef
  76. >>> Company.objects.filter(
  77. ... Exists(Employee.objects.filter(company=OuterRef("pk"), salary__gt=10))
  78. ... )
  79. # Lookup expressions can also be used directly in filters
  80. >>> Company.objects.filter(GreaterThan(F("num_employees"), F("num_chairs")))
  81. # or annotations.
  82. >>> Company.objects.annotate(
  83. ... need_chairs=GreaterThan(F("num_employees"), F("num_chairs")),
  84. ... )
  85. Built-in Expressions
  86. ====================
  87. .. note::
  88. These expressions are defined in ``django.db.models.expressions`` and
  89. ``django.db.models.aggregates``, but for convenience they're available and
  90. usually imported from :mod:`django.db.models`.
  91. ``F()`` expressions
  92. -------------------
  93. .. class:: F
  94. An ``F()`` object represents the value of a model field, transformed value of a
  95. model field, or annotated column. It makes it possible to refer to model field
  96. values and perform database operations using them without actually having to
  97. pull them out of the database into Python memory.
  98. Instead, Django uses the ``F()`` object to generate an SQL expression that
  99. describes the required operation at the database level.
  100. Let's try this with an example. Normally, one might do something like this::
  101. # Tintin filed a news story!
  102. reporter = Reporters.objects.get(name="Tintin")
  103. reporter.stories_filed += 1
  104. reporter.save()
  105. Here, we have pulled the value of ``reporter.stories_filed`` from the database
  106. into memory and manipulated it using familiar Python operators, and then saved
  107. the object back to the database. But instead we could also have done::
  108. from django.db.models import F
  109. reporter = Reporters.objects.get(name="Tintin")
  110. reporter.stories_filed = F("stories_filed") + 1
  111. reporter.save()
  112. Although ``reporter.stories_filed = F('stories_filed') + 1`` looks like a
  113. normal Python assignment of value to an instance attribute, in fact it's an SQL
  114. construct describing an operation on the database.
  115. When Django encounters an instance of ``F()``, it overrides the standard Python
  116. operators to create an encapsulated SQL expression; in this case, one which
  117. instructs the database to increment the database field represented by
  118. ``reporter.stories_filed``.
  119. Whatever value is or was on ``reporter.stories_filed``, Python never gets to
  120. know about it - it is dealt with entirely by the database. All Python does,
  121. through Django's ``F()`` class, is create the SQL syntax to refer to the field
  122. and describe the operation.
  123. To access the new value saved this way, the object must be reloaded::
  124. reporter = Reporters.objects.get(pk=reporter.pk)
  125. # Or, more succinctly:
  126. reporter.refresh_from_db()
  127. As well as being used in operations on single instances as above, ``F()`` can
  128. be used on ``QuerySets`` of object instances, with ``update()``. This reduces
  129. the two queries we were using above - the ``get()`` and the
  130. :meth:`~Model.save()` - to just one::
  131. reporter = Reporters.objects.filter(name="Tintin")
  132. reporter.update(stories_filed=F("stories_filed") + 1)
  133. We can also use :meth:`~django.db.models.query.QuerySet.update()` to increment
  134. the field value on multiple objects - which could be very much faster than
  135. pulling them all into Python from the database, looping over them, incrementing
  136. the field value of each one, and saving each one back to the database::
  137. Reporter.objects.update(stories_filed=F("stories_filed") + 1)
  138. ``F()`` therefore can offer performance advantages by:
  139. * getting the database, rather than Python, to do work
  140. * reducing the number of queries some operations require
  141. .. _slicing-using-f:
  142. Slicing ``F()`` expressions
  143. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  144. .. versionadded:: 5.1
  145. For string-based fields, text-based fields, and
  146. :class:`~django.contrib.postgres.fields.ArrayField`, you can use Python's
  147. array-slicing syntax. The indices are 0-based and the ``step`` argument to
  148. ``slice`` is not supported. For example:
  149. .. code-block:: pycon
  150. >>> # Replacing a name with a substring of itself.
  151. >>> writer = Writers.objects.get(name="Priyansh")
  152. >>> writer.name = F("name")[1:5]
  153. >>> writer.save()
  154. >>> writer.refresh_from_db()
  155. >>> writer.name
  156. 'riya'
  157. .. _avoiding-race-conditions-using-f:
  158. Avoiding race conditions using ``F()``
  159. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  160. Another useful benefit of ``F()`` is that having the database - rather than
  161. Python - update a field's value avoids a *race condition*.
  162. If two Python threads execute the code in the first example above, one thread
  163. could retrieve, increment, and save a field's value after the other has
  164. retrieved it from the database. The value that the second thread saves will be
  165. based on the original value; the work of the first thread will be lost.
  166. If the database is responsible for updating the field, the process is more
  167. robust: it will only ever update the field based on the value of the field in
  168. the database when the :meth:`~Model.save()` or ``update()`` is executed, rather
  169. than based on its value when the instance was retrieved.
  170. ``F()`` assignments persist after ``Model.save()``
  171. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  172. ``F()`` objects assigned to model fields persist after saving the model
  173. instance and will be applied on each :meth:`~Model.save()`. For example::
  174. reporter = Reporters.objects.get(name="Tintin")
  175. reporter.stories_filed = F("stories_filed") + 1
  176. reporter.save()
  177. reporter.name = "Tintin Jr."
  178. reporter.save()
  179. ``stories_filed`` will be updated twice in this case. If it's initially ``1``,
  180. the final value will be ``3``. This persistence can be avoided by reloading the
  181. model object after saving it, for example, by using
  182. :meth:`~Model.refresh_from_db()`.
  183. Using ``F()`` in filters
  184. ~~~~~~~~~~~~~~~~~~~~~~~~
  185. ``F()`` is also very useful in ``QuerySet`` filters, where they make it
  186. possible to filter a set of objects against criteria based on their field
  187. values, rather than on Python values.
  188. This is documented in :ref:`using F() expressions in queries
  189. <using-f-expressions-in-filters>`.
  190. .. _using-f-with-annotations:
  191. Using ``F()`` with annotations
  192. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  193. ``F()`` can be used to create dynamic fields on your models by combining
  194. different fields with arithmetic::
  195. company = Company.objects.annotate(chairs_needed=F("num_employees") - F("num_chairs"))
  196. If the fields that you're combining are of different types you'll need to tell
  197. Django what kind of field will be returned. Most expressions support
  198. :ref:`output_field<output-field>` for this case, but since ``F()`` does not, you
  199. will need to wrap the expression with :class:`ExpressionWrapper`::
  200. from django.db.models import DateTimeField, ExpressionWrapper, F
  201. Ticket.objects.annotate(
  202. expires=ExpressionWrapper(
  203. F("active_at") + F("duration"), output_field=DateTimeField()
  204. )
  205. )
  206. When referencing relational fields such as ``ForeignKey``, ``F()`` returns the
  207. primary key value rather than a model instance:
  208. .. code-block:: pycon
  209. >>> car = Company.objects.annotate(built_by=F("manufacturer"))[0]
  210. >>> car.manufacturer
  211. <Manufacturer: Toyota>
  212. >>> car.built_by
  213. 3
  214. .. _using-f-to-sort-null-values:
  215. Using ``F()`` to sort null values
  216. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  217. Use ``F()`` and the ``nulls_first`` or ``nulls_last`` keyword argument to
  218. :meth:`.Expression.asc` or :meth:`~.Expression.desc` to control the ordering of
  219. a field's null values. By default, the ordering depends on your database.
  220. For example, to sort companies that haven't been contacted (``last_contacted``
  221. is null) after companies that have been contacted::
  222. from django.db.models import F
  223. Company.objects.order_by(F("last_contacted").desc(nulls_last=True))
  224. Using ``F()`` with logical operations
  225. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  226. ``F()`` expressions that output ``BooleanField`` can be logically negated with
  227. the inversion operator ``~F()``. For example, to swap the activation status of
  228. companies::
  229. from django.db.models import F
  230. Company.objects.update(is_active=~F("is_active"))
  231. .. _func-expressions:
  232. ``Func()`` expressions
  233. ----------------------
  234. ``Func()`` expressions are the base type of all expressions that involve
  235. database functions like ``COALESCE`` and ``LOWER``, or aggregates like ``SUM``.
  236. They can be used directly::
  237. from django.db.models import F, Func
  238. queryset.annotate(field_lower=Func(F("field"), function="LOWER"))
  239. or they can be used to build a library of database functions::
  240. class Lower(Func):
  241. function = "LOWER"
  242. queryset.annotate(field_lower=Lower("field"))
  243. But both cases will result in a queryset where each model is annotated with an
  244. extra attribute ``field_lower`` produced, roughly, from the following SQL:
  245. .. code-block:: sql
  246. SELECT
  247. ...
  248. LOWER("db_table"."field") as "field_lower"
  249. See :doc:`database-functions` for a list of built-in database functions.
  250. The ``Func`` API is as follows:
  251. .. class:: Func(*expressions, **extra)
  252. .. attribute:: function
  253. A class attribute describing the function that will be generated.
  254. Specifically, the ``function`` will be interpolated as the ``function``
  255. placeholder within :attr:`template`. Defaults to ``None``.
  256. .. attribute:: template
  257. A class attribute, as a format string, that describes the SQL that is
  258. generated for this function. Defaults to
  259. ``'%(function)s(%(expressions)s)'``.
  260. If you're constructing SQL like ``strftime('%W', 'date')`` and need a
  261. literal ``%`` character in the query, quadruple it (``%%%%``) in the
  262. ``template`` attribute because the string is interpolated twice: once
  263. during the template interpolation in ``as_sql()`` and once in the SQL
  264. interpolation with the query parameters in the database cursor.
  265. .. attribute:: arg_joiner
  266. A class attribute that denotes the character used to join the list of
  267. ``expressions`` together. Defaults to ``', '``.
  268. .. attribute:: arity
  269. A class attribute that denotes the number of arguments the function
  270. accepts. If this attribute is set and the function is called with a
  271. different number of expressions, ``TypeError`` will be raised. Defaults
  272. to ``None``.
  273. .. method:: as_sql(compiler, connection, function=None, template=None, arg_joiner=None, **extra_context)
  274. Generates the SQL fragment for the database function. Returns a tuple
  275. ``(sql, params)``, where ``sql`` is the SQL string, and ``params`` is
  276. the list or tuple of query parameters.
  277. The ``as_vendor()`` methods should use the ``function``, ``template``,
  278. ``arg_joiner``, and any other ``**extra_context`` parameters to
  279. customize the SQL as needed. For example:
  280. .. code-block:: python
  281. :caption: ``django/db/models/functions.py``
  282. class ConcatPair(Func):
  283. ...
  284. function = "CONCAT"
  285. ...
  286. def as_mysql(self, compiler, connection, **extra_context):
  287. return super().as_sql(
  288. compiler,
  289. connection,
  290. function="CONCAT_WS",
  291. template="%(function)s('', %(expressions)s)",
  292. **extra_context
  293. )
  294. To avoid an SQL injection vulnerability, ``extra_context`` :ref:`must
  295. not contain untrusted user input <avoiding-sql-injection-in-query-expressions>`
  296. as these values are interpolated into the SQL string rather than passed
  297. as query parameters, where the database driver would escape them.
  298. The ``*expressions`` argument is a list of positional expressions that the
  299. function will be applied to. The expressions will be converted to strings,
  300. joined together with ``arg_joiner``, and then interpolated into the ``template``
  301. as the ``expressions`` placeholder.
  302. Positional arguments can be expressions or Python values. Strings are
  303. assumed to be column references and will be wrapped in ``F()`` expressions
  304. while other values will be wrapped in ``Value()`` expressions.
  305. The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated
  306. into the ``template`` attribute. To avoid an SQL injection vulnerability,
  307. ``extra`` :ref:`must not contain untrusted user input
  308. <avoiding-sql-injection-in-query-expressions>` as these values are interpolated
  309. into the SQL string rather than passed as query parameters, where the database
  310. driver would escape them.
  311. The ``function``, ``template``, and ``arg_joiner`` keywords can be used to
  312. replace the attributes of the same name without having to define your own
  313. class. :ref:`output_field<output-field>` can be used to define the expected
  314. return type.
  315. ``Aggregate()`` expressions
  316. ---------------------------
  317. An aggregate expression is a special case of a :ref:`Func() expression
  318. <func-expressions>` that informs the query that a ``GROUP BY`` clause
  319. is required. All of the :ref:`aggregate functions <aggregation-functions>`,
  320. like ``Sum()`` and ``Count()``, inherit from ``Aggregate()``.
  321. Since ``Aggregate``\s are expressions and wrap expressions, you can represent
  322. some complex computations::
  323. from django.db.models import Count
  324. Company.objects.annotate(
  325. managers_required=(Count("num_employees") / 4) + Count("num_managers")
  326. )
  327. The ``Aggregate`` API is as follows:
  328. .. class:: Aggregate(*expressions, output_field=None, distinct=False, filter=None, default=None, **extra)
  329. .. attribute:: template
  330. A class attribute, as a format string, that describes the SQL that is
  331. generated for this aggregate. Defaults to
  332. ``'%(function)s(%(distinct)s%(expressions)s)'``.
  333. .. attribute:: function
  334. A class attribute describing the aggregate function that will be
  335. generated. Specifically, the ``function`` will be interpolated as the
  336. ``function`` placeholder within :attr:`template`. Defaults to ``None``.
  337. .. attribute:: window_compatible
  338. Defaults to ``True`` since most aggregate functions can be used as the
  339. source expression in :class:`~django.db.models.expressions.Window`.
  340. .. attribute:: allow_distinct
  341. A class attribute determining whether or not this aggregate function
  342. allows passing a ``distinct`` keyword argument. If set to ``False``
  343. (default), ``TypeError`` is raised if ``distinct=True`` is passed.
  344. .. attribute:: empty_result_set_value
  345. Defaults to ``None`` since most aggregate functions result in ``NULL``
  346. when applied to an empty result set.
  347. The ``expressions`` positional arguments can include expressions, transforms of
  348. the model field, or the names of model fields. They will be converted to a
  349. string and used as the ``expressions`` placeholder within the ``template``.
  350. The ``distinct`` argument determines whether or not the aggregate function
  351. should be invoked for each distinct value of ``expressions`` (or set of
  352. values, for multiple ``expressions``). The argument is only supported on
  353. aggregates that have :attr:`~Aggregate.allow_distinct` set to ``True``.
  354. The ``filter`` argument takes a :class:`Q object <django.db.models.Q>` that's
  355. used to filter the rows that are aggregated. See :ref:`conditional-aggregation`
  356. and :ref:`filtering-on-annotations` for example usage.
  357. The ``default`` argument takes a value that will be passed along with the
  358. aggregate to :class:`~django.db.models.functions.Coalesce`. This is useful for
  359. specifying a value to be returned other than ``None`` when the queryset (or
  360. grouping) contains no entries.
  361. The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated
  362. into the ``template`` attribute.
  363. Creating your own Aggregate Functions
  364. -------------------------------------
  365. You can create your own aggregate functions, too. At a minimum, you need to
  366. define ``function``, but you can also completely customize the SQL that is
  367. generated. Here's a brief example::
  368. from django.db.models import Aggregate
  369. class Sum(Aggregate):
  370. # Supports SUM(ALL field).
  371. function = "SUM"
  372. template = "%(function)s(%(all_values)s%(expressions)s)"
  373. allow_distinct = False
  374. def __init__(self, expression, all_values=False, **extra):
  375. super().__init__(expression, all_values="ALL " if all_values else "", **extra)
  376. ``Value()`` expressions
  377. -----------------------
  378. .. class:: Value(value, output_field=None)
  379. A ``Value()`` object represents the smallest possible component of an
  380. expression: a simple value. When you need to represent the value of an integer,
  381. boolean, or string within an expression, you can wrap that value within a
  382. ``Value()``.
  383. You will rarely need to use ``Value()`` directly. When you write the expression
  384. ``F('field') + 1``, Django implicitly wraps the ``1`` in a ``Value()``,
  385. allowing simple values to be used in more complex expressions. You will need to
  386. use ``Value()`` when you want to pass a string to an expression. Most
  387. expressions interpret a string argument as the name of a field, like
  388. ``Lower('name')``.
  389. The ``value`` argument describes the value to be included in the expression,
  390. such as ``1``, ``True``, or ``None``. Django knows how to convert these Python
  391. values into their corresponding database type.
  392. If no :ref:`output_field<output-field>` is specified, it will be inferred from
  393. the type of the provided ``value`` for many common types. For example, passing
  394. an instance of :py:class:`datetime.datetime` as ``value`` defaults
  395. ``output_field`` to :class:`~django.db.models.DateTimeField`.
  396. ``ExpressionWrapper()`` expressions
  397. -----------------------------------
  398. .. class:: ExpressionWrapper(expression, output_field)
  399. ``ExpressionWrapper`` surrounds another expression and provides access to
  400. properties, such as :ref:`output_field<output-field>`, that may not be
  401. available on other expressions. ``ExpressionWrapper`` is necessary when using
  402. arithmetic on ``F()`` expressions with different types as described in
  403. :ref:`using-f-with-annotations`.
  404. Conditional expressions
  405. -----------------------
  406. Conditional expressions allow you to use :keyword:`if` ... :keyword:`elif` ...
  407. :keyword:`else` logic in queries. Django natively supports SQL ``CASE``
  408. expressions. For more details see :doc:`conditional-expressions`.
  409. ``Subquery()`` expressions
  410. --------------------------
  411. .. class:: Subquery(queryset, output_field=None)
  412. You can add an explicit subquery to a ``QuerySet`` using the ``Subquery``
  413. expression.
  414. For example, to annotate each post with the email address of the author of the
  415. newest comment on that post:
  416. .. code-block:: pycon
  417. >>> from django.db.models import OuterRef, Subquery
  418. >>> newest = Comment.objects.filter(post=OuterRef("pk")).order_by("-created_at")
  419. >>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values("email")[:1]))
  420. On PostgreSQL, the SQL looks like:
  421. .. code-block:: sql
  422. SELECT "post"."id", (
  423. SELECT U0."email"
  424. FROM "comment" U0
  425. WHERE U0."post_id" = ("post"."id")
  426. ORDER BY U0."created_at" DESC LIMIT 1
  427. ) AS "newest_commenter_email" FROM "post"
  428. .. note::
  429. The examples in this section are designed to show how to force
  430. Django to execute a subquery. In some cases it may be possible to
  431. write an equivalent queryset that performs the same task more
  432. clearly or efficiently.
  433. Referencing columns from the outer queryset
  434. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  435. .. class:: OuterRef(field)
  436. Use ``OuterRef`` when a queryset in a ``Subquery`` needs to refer to a field
  437. from the outer query or its transform. It acts like an :class:`F` expression
  438. except that the check to see if it refers to a valid field isn't made until the
  439. outer queryset is resolved.
  440. Instances of ``OuterRef`` may be used in conjunction with nested instances
  441. of ``Subquery`` to refer to a containing queryset that isn't the immediate
  442. parent. For example, this queryset would need to be within a nested pair of
  443. ``Subquery`` instances to resolve correctly:
  444. .. code-block:: pycon
  445. >>> Book.objects.filter(author=OuterRef(OuterRef("pk")))
  446. Limiting a subquery to a single column
  447. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  448. There are times when a single column must be returned from a ``Subquery``, for
  449. instance, to use a ``Subquery`` as the target of an ``__in`` lookup. To return
  450. all comments for posts published within the last day:
  451. .. code-block:: pycon
  452. >>> from datetime import timedelta
  453. >>> from django.utils import timezone
  454. >>> one_day_ago = timezone.now() - timedelta(days=1)
  455. >>> posts = Post.objects.filter(published_at__gte=one_day_ago)
  456. >>> Comment.objects.filter(post__in=Subquery(posts.values("pk")))
  457. In this case, the subquery must use :meth:`~.QuerySet.values`
  458. to return only a single column: the primary key of the post.
  459. Limiting the subquery to a single row
  460. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  461. To prevent a subquery from returning multiple rows, a slice (``[:1]``) of the
  462. queryset is used:
  463. .. code-block:: pycon
  464. >>> subquery = Subquery(newest.values("email")[:1])
  465. >>> Post.objects.annotate(newest_commenter_email=subquery)
  466. In this case, the subquery must only return a single column *and* a single
  467. row: the email address of the most recently created comment.
  468. (Using :meth:`~.QuerySet.get` instead of a slice would fail because the
  469. ``OuterRef`` cannot be resolved until the queryset is used within a
  470. ``Subquery``.)
  471. ``Exists()`` subqueries
  472. ~~~~~~~~~~~~~~~~~~~~~~~
  473. .. class:: Exists(queryset)
  474. ``Exists`` is a ``Subquery`` subclass that uses an SQL ``EXISTS`` statement. In
  475. many cases it will perform better than a subquery since the database is able to
  476. stop evaluation of the subquery when a first matching row is found.
  477. For example, to annotate each post with whether or not it has a comment from
  478. within the last day:
  479. .. code-block:: pycon
  480. >>> from django.db.models import Exists, OuterRef
  481. >>> from datetime import timedelta
  482. >>> from django.utils import timezone
  483. >>> one_day_ago = timezone.now() - timedelta(days=1)
  484. >>> recent_comments = Comment.objects.filter(
  485. ... post=OuterRef("pk"),
  486. ... created_at__gte=one_day_ago,
  487. ... )
  488. >>> Post.objects.annotate(recent_comment=Exists(recent_comments))
  489. On PostgreSQL, the SQL looks like:
  490. .. code-block:: sql
  491. SELECT "post"."id", "post"."published_at", EXISTS(
  492. SELECT (1) as "a"
  493. FROM "comment" U0
  494. WHERE (
  495. U0."created_at" >= YYYY-MM-DD HH:MM:SS AND
  496. U0."post_id" = "post"."id"
  497. )
  498. LIMIT 1
  499. ) AS "recent_comment" FROM "post"
  500. It's unnecessary to force ``Exists`` to refer to a single column, since the
  501. columns are discarded and a boolean result is returned. Similarly, since
  502. ordering is unimportant within an SQL ``EXISTS`` subquery and would only
  503. degrade performance, it's automatically removed.
  504. You can query using ``NOT EXISTS`` with ``~Exists()``.
  505. Filtering on a ``Subquery()`` or ``Exists()`` expressions
  506. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  507. ``Subquery()`` that returns a boolean value and ``Exists()`` may be used as a
  508. ``condition`` in :class:`~django.db.models.expressions.When` expressions, or to
  509. directly filter a queryset:
  510. .. code-block:: pycon
  511. >>> recent_comments = Comment.objects.filter(...) # From above
  512. >>> Post.objects.filter(Exists(recent_comments))
  513. This will ensure that the subquery will not be added to the ``SELECT`` columns,
  514. which may result in a better performance.
  515. Using aggregates within a ``Subquery`` expression
  516. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  517. Aggregates may be used within a ``Subquery``, but they require a specific
  518. combination of :meth:`~.QuerySet.filter`, :meth:`~.QuerySet.values`, and
  519. :meth:`~.QuerySet.annotate` to get the subquery grouping correct.
  520. Assuming both models have a ``length`` field, to find posts where the post
  521. length is greater than the total length of all combined comments:
  522. .. code-block:: pycon
  523. >>> from django.db.models import OuterRef, Subquery, Sum
  524. >>> comments = Comment.objects.filter(post=OuterRef("pk")).order_by().values("post")
  525. >>> total_comments = comments.annotate(total=Sum("length")).values("total")
  526. >>> Post.objects.filter(length__gt=Subquery(total_comments))
  527. The initial ``filter(...)`` limits the subquery to the relevant parameters.
  528. ``order_by()`` removes the default :attr:`~django.db.models.Options.ordering`
  529. (if any) on the ``Comment`` model. ``values('post')`` aggregates comments by
  530. ``Post``. Finally, ``annotate(...)`` performs the aggregation. The order in
  531. which these queryset methods are applied is important. In this case, since the
  532. subquery must be limited to a single column, ``values('total')`` is required.
  533. This is the only way to perform an aggregation within a ``Subquery``, as
  534. using :meth:`~.QuerySet.aggregate` attempts to evaluate the queryset (and if
  535. there is an ``OuterRef``, this will not be possible to resolve).
  536. Raw SQL expressions
  537. -------------------
  538. .. currentmodule:: django.db.models.expressions
  539. .. class:: RawSQL(sql, params, output_field=None)
  540. Sometimes database expressions can't easily express a complex ``WHERE`` clause.
  541. In these edge cases, use the ``RawSQL`` expression. For example:
  542. .. code-block:: pycon
  543. >>> from django.db.models.expressions import RawSQL
  544. >>> queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (param,)))
  545. These extra lookups may not be portable to different database engines (because
  546. you're explicitly writing SQL code) and violate the DRY principle, so you
  547. should avoid them if possible.
  548. ``RawSQL`` expressions can also be used as the target of ``__in`` filters:
  549. .. code-block:: pycon
  550. >>> queryset.filter(id__in=RawSQL("select id from sometable where col = %s", (param,)))
  551. .. warning::
  552. To protect against `SQL injection attacks
  553. <https://en.wikipedia.org/wiki/SQL_injection>`_, you must escape any
  554. parameters that the user can control by using ``params``. ``params`` is a
  555. required argument to force you to acknowledge that you're not interpolating
  556. your SQL with user-provided data.
  557. You also must not quote placeholders in the SQL string. This example is
  558. vulnerable to SQL injection because of the quotes around ``%s``::
  559. RawSQL("select col from sometable where othercol = '%s'") # unsafe!
  560. You can read more about how Django's :ref:`SQL injection protection
  561. <sql-injection-protection>` works.
  562. Window functions
  563. ----------------
  564. Window functions provide a way to apply functions on partitions. Unlike a
  565. normal aggregation function which computes a final result for each set defined
  566. by the group by, window functions operate on :ref:`frames <window-frames>` and
  567. partitions, and compute the result for each row.
  568. You can specify multiple windows in the same query which in Django ORM would be
  569. equivalent to including multiple expressions in a :doc:`QuerySet.annotate()
  570. </topics/db/aggregation>` call. The ORM doesn't make use of named windows,
  571. instead they are part of the selected columns.
  572. .. class:: Window(expression, partition_by=None, order_by=None, frame=None, output_field=None)
  573. .. attribute:: template
  574. Defaults to ``%(expression)s OVER (%(window)s)``. If only the
  575. ``expression`` argument is provided, the window clause will be blank.
  576. The ``Window`` class is the main expression for an ``OVER`` clause.
  577. The ``expression`` argument is either a :ref:`window function
  578. <window-functions>`, an :ref:`aggregate function <aggregation-functions>`, or
  579. an expression that's compatible in a window clause.
  580. The ``partition_by`` argument accepts an expression or a sequence of
  581. expressions (column names should be wrapped in an ``F``-object) that control
  582. the partitioning of the rows. Partitioning narrows which rows are used to
  583. compute the result set.
  584. The :ref:`output_field<output-field>` is specified either as an argument or by
  585. the expression.
  586. The ``order_by`` argument accepts an expression on which you can call
  587. :meth:`~django.db.models.Expression.asc` and
  588. :meth:`~django.db.models.Expression.desc`, a string of a field name (with an
  589. optional ``"-"`` prefix which indicates descending order), or a tuple or list
  590. of strings and/or expressions. The ordering controls the order in which the
  591. expression is applied. For example, if you sum over the rows in a partition,
  592. the first result is the value of the first row, the second is the sum of first
  593. and second row.
  594. The ``frame`` parameter specifies which other rows that should be used in the
  595. computation. See :ref:`window-frames` for details.
  596. For example, to annotate each movie with the average rating for the movies by
  597. the same studio in the same genre and release year:
  598. .. code-block:: pycon
  599. >>> from django.db.models import Avg, F, Window
  600. >>> Movie.objects.annotate(
  601. ... avg_rating=Window(
  602. ... expression=Avg("rating"),
  603. ... partition_by=[F("studio"), F("genre")],
  604. ... order_by="released__year",
  605. ... ),
  606. ... )
  607. This allows you to check if a movie is rated better or worse than its peers.
  608. You may want to apply multiple expressions over the same window, i.e., the
  609. same partition and frame. For example, you could modify the previous example
  610. to also include the best and worst rating in each movie's group (same studio,
  611. genre, and release year) by using three window functions in the same query. The
  612. partition and ordering from the previous example is extracted into a dictionary
  613. to reduce repetition:
  614. .. code-block:: pycon
  615. >>> from django.db.models import Avg, F, Max, Min, Window
  616. >>> window = {
  617. ... "partition_by": [F("studio"), F("genre")],
  618. ... "order_by": "released__year",
  619. ... }
  620. >>> Movie.objects.annotate(
  621. ... avg_rating=Window(
  622. ... expression=Avg("rating"),
  623. ... **window,
  624. ... ),
  625. ... best=Window(
  626. ... expression=Max("rating"),
  627. ... **window,
  628. ... ),
  629. ... worst=Window(
  630. ... expression=Min("rating"),
  631. ... **window,
  632. ... ),
  633. ... )
  634. Filtering against window functions is supported as long as lookups are not
  635. disjunctive (not using ``OR`` or ``XOR`` as a connector) and against a queryset
  636. performing aggregation.
  637. For example, a query that relies on aggregation and has an ``OR``-ed filter
  638. against a window function and a field is not supported. Applying combined
  639. predicates post-aggregation could cause rows that would normally be excluded
  640. from groups to be included:
  641. .. code-block:: pycon
  642. >>> qs = Movie.objects.annotate(
  643. ... category_rank=Window(Rank(), partition_by="category", order_by="-rating"),
  644. ... scenes_count=Count("actors"),
  645. ... ).filter(Q(category_rank__lte=3) | Q(title__contains="Batman"))
  646. >>> list(qs)
  647. NotImplementedError: Heterogeneous disjunctive predicates against window functions
  648. are not implemented when performing conditional aggregation.
  649. Among Django's built-in database backends, MySQL, PostgreSQL, and Oracle
  650. support window expressions. Support for different window expression features
  651. varies among the different databases. For example, the options in
  652. :meth:`~django.db.models.Expression.asc` and
  653. :meth:`~django.db.models.Expression.desc` may not be supported. Consult the
  654. documentation for your database as needed.
  655. .. _window-frames:
  656. Frames
  657. ~~~~~~
  658. For a window frame, you can choose either a range-based sequence of rows or an
  659. ordinary sequence of rows.
  660. .. class:: ValueRange(start=None, end=None, exclusion=None)
  661. .. attribute:: frame_type
  662. This attribute is set to ``'RANGE'``.
  663. PostgreSQL has limited support for ``ValueRange`` and only supports use of
  664. the standard start and end points, such as ``CURRENT ROW`` and ``UNBOUNDED
  665. FOLLOWING``.
  666. .. versionchanged:: 5.1
  667. The ``exclusion`` argument was added.
  668. .. class:: RowRange(start=None, end=None, exclusion=None)
  669. .. attribute:: frame_type
  670. This attribute is set to ``'ROWS'``.
  671. .. versionchanged:: 5.1
  672. The ``exclusion`` argument was added.
  673. Both classes return SQL with the template:
  674. .. code-block:: sql
  675. %(frame_type)s BETWEEN %(start)s AND %(end)s
  676. .. class:: WindowFrameExclusion
  677. .. versionadded:: 5.1
  678. .. attribute:: CURRENT_ROW
  679. .. attribute:: GROUP
  680. .. attribute:: TIES
  681. .. attribute:: NO_OTHERS
  682. The ``exclusion`` argument allows excluding rows
  683. (:attr:`~WindowFrameExclusion.CURRENT_ROW`), groups
  684. (:attr:`~WindowFrameExclusion.GROUP`), and ties
  685. (:attr:`~WindowFrameExclusion.TIES`) from the window frames on supported
  686. databases:
  687. .. code-block:: sql
  688. %(frame_type)s BETWEEN %(start)s AND %(end)s EXCLUDE %(exclusion)s
  689. Frames narrow the rows that are used for computing the result. They shift from
  690. some start point to some specified end point. Frames can be used with and
  691. without partitions, but it's often a good idea to specify an ordering of the
  692. window to ensure a deterministic result. In a frame, a peer in a frame is a row
  693. with an equivalent value, or all rows if an ordering clause isn't present.
  694. The default starting point for a frame is ``UNBOUNDED PRECEDING`` which is the
  695. first row of the partition. The end point is always explicitly included in the
  696. SQL generated by the ORM and is by default ``UNBOUNDED FOLLOWING``. The default
  697. frame includes all rows from the partition to the last row in the set.
  698. The accepted values for the ``start`` and ``end`` arguments are ``None``, an
  699. integer, or zero. A negative integer for ``start`` results in ``N PRECEDING``,
  700. while ``None`` yields ``UNBOUNDED PRECEDING``. In ``ROWS`` mode, a positive
  701. integer can be used for ``start`` resulting in ``N FOLLOWING``. Positive
  702. integers are accepted for ``end`` and results in ``N FOLLOWING``. In ``ROWS``
  703. mode, a negative integer can be used for ``end`` resulting in ``N PRECEDING``.
  704. For both ``start`` and ``end``, zero will return ``CURRENT ROW``.
  705. There's a difference in what ``CURRENT ROW`` includes. When specified in
  706. ``ROWS`` mode, the frame starts or ends with the current row. When specified in
  707. ``RANGE`` mode, the frame starts or ends at the first or last peer according to
  708. the ordering clause. Thus, ``RANGE CURRENT ROW`` evaluates the expression for
  709. rows which have the same value specified by the ordering. Because the template
  710. includes both the ``start`` and ``end`` points, this may be expressed with::
  711. ValueRange(start=0, end=0)
  712. If a movie's "peers" are described as movies released by the same studio in the
  713. same genre in the same year, this ``RowRange`` example annotates each movie
  714. with the average rating of a movie's two prior and two following peers:
  715. .. code-block:: pycon
  716. >>> from django.db.models import Avg, F, RowRange, Window
  717. >>> Movie.objects.annotate(
  718. ... avg_rating=Window(
  719. ... expression=Avg("rating"),
  720. ... partition_by=[F("studio"), F("genre")],
  721. ... order_by="released__year",
  722. ... frame=RowRange(start=-2, end=2),
  723. ... ),
  724. ... )
  725. If the database supports it, you can specify the start and end points based on
  726. values of an expression in the partition. If the ``released`` field of the
  727. ``Movie`` model stores the release month of each movie, this ``ValueRange``
  728. example annotates each movie with the average rating of a movie's peers
  729. released between twelve months before and twelve months after each movie:
  730. .. code-block:: pycon
  731. >>> from django.db.models import Avg, F, ValueRange, Window
  732. >>> Movie.objects.annotate(
  733. ... avg_rating=Window(
  734. ... expression=Avg("rating"),
  735. ... partition_by=[F("studio"), F("genre")],
  736. ... order_by="released__year",
  737. ... frame=ValueRange(start=-12, end=12),
  738. ... ),
  739. ... )
  740. .. versionchanged:: 5.1
  741. Support for positive integer ``start`` and negative integer ``end`` was
  742. added for ``RowRange``.
  743. .. currentmodule:: django.db.models
  744. Technical Information
  745. =====================
  746. Below you'll find technical implementation details that may be useful to
  747. library authors. The technical API and examples below will help with
  748. creating generic query expressions that can extend the built-in functionality
  749. that Django provides.
  750. Expression API
  751. --------------
  752. Query expressions implement the :ref:`query expression API <query-expression>`,
  753. but also expose a number of extra methods and attributes listed below. All
  754. query expressions must inherit from ``Expression()`` or a relevant
  755. subclass.
  756. When a query expression wraps another expression, it is responsible for
  757. calling the appropriate methods on the wrapped expression.
  758. .. class:: Expression
  759. .. attribute:: allowed_default
  760. Tells Django that this expression can be used in
  761. :attr:`Field.db_default`. Defaults to ``False``.
  762. .. attribute:: constraint_validation_compatible
  763. .. versionadded:: 5.1
  764. Tells Django that this expression can be used during a constraint
  765. validation. Expressions with ``constraint_validation_compatible`` set
  766. to ``False`` must have only one source expression. Defaults to
  767. ``True``.
  768. .. attribute:: contains_aggregate
  769. Tells Django that this expression contains an aggregate and that a
  770. ``GROUP BY`` clause needs to be added to the query.
  771. .. attribute:: contains_over_clause
  772. Tells Django that this expression contains a
  773. :class:`~django.db.models.expressions.Window` expression. It's used,
  774. for example, to disallow window function expressions in queries that
  775. modify data.
  776. .. attribute:: filterable
  777. Tells Django that this expression can be referenced in
  778. :meth:`.QuerySet.filter`. Defaults to ``True``.
  779. .. attribute:: window_compatible
  780. Tells Django that this expression can be used as the source expression
  781. in :class:`~django.db.models.expressions.Window`. Defaults to
  782. ``False``.
  783. .. attribute:: empty_result_set_value
  784. Tells Django which value should be returned when the expression is used
  785. to apply a function over an empty result set. Defaults to
  786. :py:data:`NotImplemented` which forces the expression to be computed on
  787. the database.
  788. .. attribute:: set_returning
  789. .. versionadded:: 5.2
  790. Tells Django that this expression contains a set-returning function,
  791. enforcing subquery evaluation. It's used, for example, to allow some
  792. Postgres set-returning functions (e.g. ``JSONB_PATH_QUERY``,
  793. ``UNNEST``, etc.) to skip optimization and be properly evaluated when
  794. annotations spawn rows themselves. Defaults to ``False``.
  795. .. method:: resolve_expression(query=None, allow_joins=True, reuse=None, summarize=False, for_save=False)
  796. Provides the chance to do any preprocessing or validation of
  797. the expression before it's added to the query. ``resolve_expression()``
  798. must also be called on any nested expressions. A ``copy()`` of ``self``
  799. should be returned with any necessary transformations.
  800. ``query`` is the backend query implementation.
  801. ``allow_joins`` is a boolean that allows or denies the use of
  802. joins in the query.
  803. ``reuse`` is a set of reusable joins for multi-join scenarios.
  804. ``summarize`` is a boolean that, when ``True``, signals that the
  805. query being computed is a terminal aggregate query.
  806. ``for_save`` is a boolean that, when ``True``, signals that the query
  807. being executed is performing a create or update.
  808. .. method:: get_source_expressions()
  809. Returns an ordered list of inner expressions. For example:
  810. .. code-block:: pycon
  811. >>> Sum(F("foo")).get_source_expressions()
  812. [F('foo')]
  813. .. method:: set_source_expressions(expressions)
  814. Takes a list of expressions and stores them such that
  815. ``get_source_expressions()`` can return them.
  816. .. method:: relabeled_clone(change_map)
  817. Returns a clone (copy) of ``self``, with any column aliases relabeled.
  818. Column aliases are renamed when subqueries are created.
  819. ``relabeled_clone()`` should also be called on any nested expressions
  820. and assigned to the clone.
  821. ``change_map`` is a dictionary mapping old aliases to new aliases.
  822. Example::
  823. def relabeled_clone(self, change_map):
  824. clone = copy.copy(self)
  825. clone.expression = self.expression.relabeled_clone(change_map)
  826. return clone
  827. .. method:: convert_value(value, expression, connection)
  828. A hook allowing the expression to coerce ``value`` into a more
  829. appropriate type.
  830. ``expression`` is the same as ``self``.
  831. .. method:: get_group_by_cols()
  832. Responsible for returning the list of columns references by
  833. this expression. ``get_group_by_cols()`` should be called on any
  834. nested expressions. ``F()`` objects, in particular, hold a reference
  835. to a column.
  836. .. method:: asc(nulls_first=None, nulls_last=None)
  837. Returns the expression ready to be sorted in ascending order.
  838. ``nulls_first`` and ``nulls_last`` define how null values are sorted.
  839. See :ref:`using-f-to-sort-null-values` for example usage.
  840. .. method:: desc(nulls_first=None, nulls_last=None)
  841. Returns the expression ready to be sorted in descending order.
  842. ``nulls_first`` and ``nulls_last`` define how null values are sorted.
  843. See :ref:`using-f-to-sort-null-values` for example usage.
  844. .. method:: reverse_ordering()
  845. Returns ``self`` with any modifications required to reverse the sort
  846. order within an ``order_by`` call. As an example, an expression
  847. implementing ``NULLS LAST`` would change its value to be
  848. ``NULLS FIRST``. Modifications are only required for expressions that
  849. implement sort order like ``OrderBy``. This method is called when
  850. :meth:`~django.db.models.query.QuerySet.reverse()` is called on a
  851. queryset.
  852. Writing your own Query Expressions
  853. ----------------------------------
  854. You can write your own query expression classes that use, and can integrate
  855. with, other query expressions. Let's step through an example by writing an
  856. implementation of the ``COALESCE`` SQL function, without using the built-in
  857. :ref:`Func() expressions <func-expressions>`.
  858. The ``COALESCE`` SQL function is defined as taking a list of columns or
  859. values. It will return the first column or value that isn't ``NULL``.
  860. We'll start by defining the template to be used for SQL generation and
  861. an ``__init__()`` method to set some attributes::
  862. import copy
  863. from django.db.models import Expression
  864. class Coalesce(Expression):
  865. template = "COALESCE( %(expressions)s )"
  866. def __init__(self, expressions, output_field):
  867. super().__init__(output_field=output_field)
  868. if len(expressions) < 2:
  869. raise ValueError("expressions must have at least 2 elements")
  870. for expression in expressions:
  871. if not hasattr(expression, "resolve_expression"):
  872. raise TypeError("%r is not an Expression" % expression)
  873. self.expressions = expressions
  874. We do some basic validation on the parameters, including requiring at least 2
  875. columns or values, and ensuring they are expressions. We are requiring
  876. :ref:`output_field<output-field>` here so that Django knows what kind of model
  877. field to assign the eventual result to.
  878. Now we implement the preprocessing and validation. Since we do not have
  879. any of our own validation at this point, we delegate to the nested
  880. expressions::
  881. def resolve_expression(
  882. self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
  883. ):
  884. c = self.copy()
  885. c.is_summary = summarize
  886. for pos, expression in enumerate(self.expressions):
  887. c.expressions[pos] = expression.resolve_expression(
  888. query, allow_joins, reuse, summarize, for_save
  889. )
  890. return c
  891. Next, we write the method responsible for generating the SQL::
  892. def as_sql(self, compiler, connection, template=None):
  893. sql_expressions, sql_params = [], []
  894. for expression in self.expressions:
  895. sql, params = compiler.compile(expression)
  896. sql_expressions.append(sql)
  897. sql_params.extend(params)
  898. template = template or self.template
  899. data = {"expressions": ",".join(sql_expressions)}
  900. return template % data, sql_params
  901. def as_oracle(self, compiler, connection):
  902. """
  903. Example of vendor specific handling (Oracle in this case).
  904. Let's make the function name lowercase.
  905. """
  906. return self.as_sql(compiler, connection, template="coalesce( %(expressions)s )")
  907. ``as_sql()`` methods can support custom keyword arguments, allowing
  908. ``as_vendorname()`` methods to override data used to generate the SQL string.
  909. Using ``as_sql()`` keyword arguments for customization is preferable to
  910. mutating ``self`` within ``as_vendorname()`` methods as the latter can lead to
  911. errors when running on different database backends. If your class relies on
  912. class attributes to define data, consider allowing overrides in your
  913. ``as_sql()`` method.
  914. We generate the SQL for each of the ``expressions`` by using the
  915. ``compiler.compile()`` method, and join the result together with commas.
  916. Then the template is filled out with our data and the SQL and parameters
  917. are returned.
  918. We've also defined a custom implementation that is specific to the Oracle
  919. backend. The ``as_oracle()`` function will be called instead of ``as_sql()``
  920. if the Oracle backend is in use.
  921. Finally, we implement the rest of the methods that allow our query expression
  922. to play nice with other query expressions::
  923. def get_source_expressions(self):
  924. return self.expressions
  925. def set_source_expressions(self, expressions):
  926. self.expressions = expressions
  927. Let's see how it works:
  928. .. code-block:: pycon
  929. >>> from django.db.models import F, Value, CharField
  930. >>> qs = Company.objects.annotate(
  931. ... tagline=Coalesce(
  932. ... [F("motto"), F("ticker_name"), F("description"), Value("No Tagline")],
  933. ... output_field=CharField(),
  934. ... )
  935. ... )
  936. >>> for c in qs:
  937. ... print("%s: %s" % (c.name, c.tagline))
  938. ...
  939. Google: Do No Evil
  940. Apple: AAPL
  941. Yahoo: Internet Company
  942. Django Software Foundation: No Tagline
  943. .. _avoiding-sql-injection-in-query-expressions:
  944. Avoiding SQL injection
  945. ~~~~~~~~~~~~~~~~~~~~~~
  946. Since a ``Func``'s keyword arguments for ``__init__()`` (``**extra``) and
  947. ``as_sql()`` (``**extra_context``) are interpolated into the SQL string rather
  948. than passed as query parameters (where the database driver would escape them),
  949. they must not contain untrusted user input.
  950. For example, if ``substring`` is user-provided, this function is vulnerable to
  951. SQL injection::
  952. from django.db.models import Func
  953. class Position(Func):
  954. function = "POSITION"
  955. template = "%(function)s('%(substring)s' in %(expressions)s)"
  956. def __init__(self, expression, substring):
  957. # substring=substring is an SQL injection vulnerability!
  958. super().__init__(expression, substring=substring)
  959. This function generates an SQL string without any parameters. Since
  960. ``substring`` is passed to ``super().__init__()`` as a keyword argument, it's
  961. interpolated into the SQL string before the query is sent to the database.
  962. Here's a corrected rewrite::
  963. class Position(Func):
  964. function = "POSITION"
  965. arg_joiner = " IN "
  966. def __init__(self, expression, substring):
  967. super().__init__(substring, expression)
  968. With ``substring`` instead passed as a positional argument, it'll be passed as
  969. a parameter in the database query.
  970. Adding support in third-party database backends
  971. -----------------------------------------------
  972. If you're using a database backend that uses a different SQL syntax for a
  973. certain function, you can add support for it by monkey patching a new method
  974. onto the function's class.
  975. Let's say we're writing a backend for Microsoft's SQL Server which uses the SQL
  976. ``LEN`` instead of ``LENGTH`` for the :class:`~functions.Length` function.
  977. We'll monkey patch a new method called ``as_sqlserver()`` onto the ``Length``
  978. class::
  979. from django.db.models.functions import Length
  980. def sqlserver_length(self, compiler, connection):
  981. return self.as_sql(compiler, connection, function="LEN")
  982. Length.as_sqlserver = sqlserver_length
  983. You can also customize the SQL using the ``template`` parameter of ``as_sql()``.
  984. We use ``as_sqlserver()`` because ``django.db.connection.vendor`` returns
  985. ``sqlserver`` for the backend.
  986. Third-party backends can register their functions in the top level
  987. ``__init__.py`` file of the backend package or in a top level ``expressions.py``
  988. file (or package) that is imported from the top level ``__init__.py``.
  989. For user projects wishing to patch the backend that they're using, this code
  990. should live in an :meth:`AppConfig.ready()<django.apps.AppConfig.ready>` method.