Debian Codegraph @

django/db/models/expressions.py

raw
1 import copy
2 import datetime
3 import functools
4 from collections import defaultdict
5 from decimal import Decimal
6 from enum import Enum
7 from itertools import chain
8 from types import NoneType
9 from uuid import UUID
10
11 from django.core.exceptions import EmptyResultSet, FieldError, FullResultSet
12 from django.db import DatabaseError, NotSupportedError, connection
13 from django.db.models import fields
14 from django.db.models.constants import LOOKUP_SEP
15 from django.db.models.query_utils import Q
16 from django.utils.deconstruct import deconstructible
17 from django.utils.functional import cached_property, classproperty
18 from django.utils.hashable import make_hashable
19 from django.utils.inspect import signature
20
21
22 class SQLiteNumericMixin:
23 """
24 Some expressions with output_field=DecimalField() must be cast to
25 numeric to be properly filtered.
26 """
27
28 def as_sqlite(self, compiler, connection, **extra_context):
29 sql, params = self.as_sql(compiler, connection, **extra_context)
30 try:
31 if self.output_field.get_internal_type() == "DecimalField":
32 sql = "(CAST(%s AS NUMERIC))" % sql
33 except FieldError:
34 pass
35 return sql, params
36
37
38 class Combinable:
39 """
40 Provide the ability to combine one or two objects with
41 some connector. For example F('foo') + F('bar').
42 """
43
44 # Arithmetic connectors
45 ADD = "+"
46 SUB = "-"
47 MUL = "*"
48 DIV = "/"
49 POW = "^"
50 # The following is a quoted % operator - it is quoted because it can be
51 # used in strings that also have parameter substitution.
52 MOD = "%%"
53
54 # Bitwise operators - note that these are generated by .bitand()
55 # and .bitor(), the '&' and '|' are reserved for boolean operator
56 # usage.
57 BITAND = "&"
58 BITOR = "|"
59 BITLEFTSHIFT = "<<"
60 BITRIGHTSHIFT = ">>"
61 BITXOR = "#"
62
63 def _combine(self, other, connector, reversed):
64 if not hasattr(other, "resolve_expression"):
65 # everything must be resolvable to an expression
66 other = Value(other)
67
68 if reversed:
69 return CombinedExpression(other, connector, self)
70 return CombinedExpression(self, connector, other)
71
72 #############
73 # OPERATORS #
74 #############
75
76 def __neg__(self):
77 return self._combine(-1, self.MUL, False)
78
79 def __add__(self, other):
80 return self._combine(other, self.ADD, False)
81
82 def __sub__(self, other):
83 return self._combine(other, self.SUB, False)
84
85 def __mul__(self, other):
86 return self._combine(other, self.MUL, False)
87
88 def __truediv__(self, other):
89 return self._combine(other, self.DIV, False)
90
91 def __mod__(self, other):
92 return self._combine(other, self.MOD, False)
93
94 def __pow__(self, other):
95 return self._combine(other, self.POW, False)
96
97 def __and__(self, other):
98 if getattr(self, "conditional", False) and getattr(other, "conditional", False):
99 return Q(self) & Q(other)
100 raise NotImplementedError(
101 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
102 )
103
104 def bitand(self, other):
105 return self._combine(other, self.BITAND, False)
106
107 def bitleftshift(self, other):
108 return self._combine(other, self.BITLEFTSHIFT, False)
109
110 def bitrightshift(self, other):
111 return self._combine(other, self.BITRIGHTSHIFT, False)
112
113 def __xor__(self, other):
114 if getattr(self, "conditional", False) and getattr(other, "conditional", False):
115 return Q(self) ^ Q(other)
116 raise NotImplementedError(
117 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
118 )
119
120 def bitxor(self, other):
121 return self._combine(other, self.BITXOR, False)
122
123 def __or__(self, other):
124 if getattr(self, "conditional", False) and getattr(other, "conditional", False):
125 return Q(self) | Q(other)
126 raise NotImplementedError(
127 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
128 )
129
130 def bitor(self, other):
131 return self._combine(other, self.BITOR, False)
132
133 def __radd__(self, other):
134 return self._combine(other, self.ADD, True)
135
136 def __rsub__(self, other):
137 return self._combine(other, self.SUB, True)
138
139 def __rmul__(self, other):
140 return self._combine(other, self.MUL, True)
141
142 def __rtruediv__(self, other):
143 return self._combine(other, self.DIV, True)
144
145 def __rmod__(self, other):
146 return self._combine(other, self.MOD, True)
147
148 def __rpow__(self, other):
149 return self._combine(other, self.POW, True)
150
151 def __rand__(self, other):
152 raise NotImplementedError(
153 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
154 )
155
156 def __ror__(self, other):
157 raise NotImplementedError(
158 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
159 )
160
161 def __rxor__(self, other):
162 raise NotImplementedError(
163 "Use .bitand(), .bitor(), and .bitxor() for bitwise logical operations."
164 )
165
166 def __invert__(self):
167 return NegatedExpression(self)
168
169
170 class OutputFieldIsNoneError(FieldError):
171 pass
172
173
174 class BaseExpression:
175 """Base class for all query expressions."""
176
177 empty_result_set_value = NotImplemented
178 # aggregate specific fields
179 is_summary = False
180 # Can the expression be used in a WHERE clause?
181 filterable = True
182 # Can the expression be used as a source expression in Window?
183 window_compatible = False
184 # Can the expression be used as a database default value?
185 allowed_default = False
186 # Can the expression be used during a constraint validation?
187 constraint_validation_compatible = True
188 # Does the expression possibly return more than one row?
189 set_returning = False
190 # Does the expression allow composite expressions?
191 allows_composite_expressions = False
192
193 def __init__(self, output_field=None):
194 if output_field is not None:
195 self.output_field = output_field
196
197 def __getstate__(self):
198 state = self.__dict__.copy()
199 state.pop("convert_value", None)
200 return state
201
202 def get_db_converters(self, connection):
203 return (
204 []
205 if self.convert_value is self._convert_value_noop
206 else [self.convert_value]
207 ) + self.output_field.get_db_converters(connection)
208
209 def get_source_expressions(self):
210 return []
211
212 def set_source_expressions(self, exprs):
213 assert not exprs
214
215 def _parse_expressions(self, *expressions):
216 return [
217 (
218 arg
219 if hasattr(arg, "resolve_expression")
220 else (F(arg) if isinstance(arg, str) else Value(arg))
221 )
222 for arg in expressions
223 ]
224
225 def as_sql(self, compiler, connection):
226 """
227 Responsible for returning a (sql, [params]) tuple to be included
228 in the current query.
229
230 Different backends can provide their own implementation, by
231 providing an `as_{vendor}` method and patching the Expression:
232
233 ```
234 def override_as_sql(self, compiler, connection):
235 # custom logic
236 return super().as_sql(compiler, connection)
237 setattr(Expression, 'as_' + connection.vendor, override_as_sql)
238 ```
239
240 Arguments:
241 * compiler: the query compiler responsible for generating the query.
242 Must have a compile method, returning a (sql, [params]) tuple.
243 Calling compiler(value) will return a quoted `value`.
244
245 * connection: the database connection used for the current query.
246
247 Return: (sql, params)
248 Where `sql` is a string containing ordered sql parameters to be
249 replaced with the elements of the list `params`.
250 """
251 raise NotImplementedError("Subclasses must implement as_sql()")
252
253 @cached_property
254 def contains_aggregate(self):
255 return any(
256 expr and expr.contains_aggregate for expr in self.get_source_expressions()
257 )
258
259 @cached_property
260 def contains_over_clause(self):
261 return any(
262 expr and expr.contains_over_clause for expr in self.get_source_expressions()
263 )
264
265 @cached_property
266 def contains_column_references(self):
267 return any(
268 expr and expr.contains_column_references
269 for expr in self.get_source_expressions()
270 )
271
272 @cached_property
273 def contains_subquery(self):
274 return any(
275 expr and (getattr(expr, "subquery", False) or expr.contains_subquery)
276 for expr in self.get_source_expressions()
277 )
278
279 def resolve_expression(
280 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
281 ):
282 """
283 Provide the chance to do any preprocessing or validation before being
284 added to the query.
285
286 Arguments:
287 * query: the backend query implementation
288 * allow_joins: boolean allowing or denying use of joins
289 in this query
290 * reuse: a set of reusable joins for multijoins
291 * summarize: a terminal aggregate clause
292 * for_save: whether this expression about to be used in a save or update
293
294 Return: an Expression to be added to the query.
295 """
296 c = self.copy()
297 c.is_summary = summarize
298 source_expressions = [
299 (
300 expr.resolve_expression(query, allow_joins, reuse, summarize, for_save)
301 if expr is not None
302 else None
303 )
304 for expr in c.get_source_expressions()
305 ]
306 if not self.allows_composite_expressions and any(
307 isinstance(expr, ColPairs) for expr in source_expressions
308 ):
309 raise ValueError(
310 f"{self.__class__.__name__} expression does not support "
311 "composite primary keys."
312 )
313 c.set_source_expressions(source_expressions)
314 return c
315
316 @property
317 def conditional(self):
318 return isinstance(self.output_field, fields.BooleanField)
319
320 @property
321 def field(self):
322 return self.output_field
323
324 @cached_property
325 def output_field(self):
326 """Return the output type of this expressions."""
327 output_field = self._resolve_output_field()
328 if output_field is None:
329 raise OutputFieldIsNoneError(
330 "Cannot resolve expression type, unknown output_field"
331 )
332 return output_field
333
334 @property
335 def _output_field_or_none(self):
336 """
337 Return the output field of this expression, or None if
338 _resolve_output_field() didn't return an output type.
339 """
340 try:
341 return self.output_field
342 except OutputFieldIsNoneError:
343 return
344
345 def _resolve_output_field(self):
346 """
347 Attempt to infer the output type of the expression.
348
349 As a guess, if the output fields of all source fields match then simply
350 infer the same type here.
351
352 If a source's output field resolves to None, exclude it from this check.
353 If all sources are None, then an error is raised higher up the stack in
354 the output_field property.
355 """
356 # This guess is mostly a bad idea, but there is quite a lot of code
357 # (especially 3rd party Func subclasses) that depend on it, we'd need a
358 # deprecation path to fix it.
359 sources_iter = (
360 source for source in self.get_source_fields() if source is not None
361 )
362 for output_field in sources_iter:
363 for source in sources_iter:
364 if not isinstance(output_field, source.__class__):
365 raise FieldError(
366 "Expression contains mixed types: %s, %s. You must "
367 "set output_field."
368 % (
369 output_field.__class__.__name__,
370 source.__class__.__name__,
371 )
372 )
373 return output_field
374
375 @staticmethod
376 def _convert_value_noop(value, expression, connection):
377 return value
378
379 @cached_property
380 def convert_value(self):
381 """
382 Expressions provide their own converters because users have the option
383 of manually specifying the output_field which may be a different type
384 from the one the database returns.
385 """
386 field = self.output_field
387 internal_type = field.get_internal_type()
388 if internal_type == "FloatField":
389 return lambda value, expression, connection: (
390 None if value is None else float(value)
391 )
392 elif internal_type.endswith("IntegerField"):
393 return lambda value, expression, connection: (
394 None if value is None else int(value)
395 )
396 elif internal_type == "DecimalField":
397 return lambda value, expression, connection: (
398 None if value is None else Decimal(value)
399 )
400 return self._convert_value_noop
401
402 def get_lookup(self, lookup):
403 return self.output_field.get_lookup(lookup)
404
405 def get_transform(self, name):
406 return self.output_field.get_transform(name)
407
408 def relabeled_clone(self, change_map):
409 clone = self.copy()
410 clone.set_source_expressions(
411 [
412 e.relabeled_clone(change_map) if e is not None else None
413 for e in self.get_source_expressions()
414 ]
415 )
416 return clone
417
418 def replace_expressions(self, replacements):
419 if not replacements:
420 return self
421 if replacement := replacements.get(self):
422 return replacement
423 if not (source_expressions := self.get_source_expressions()):
424 return self
425 clone = self.copy()
426 clone.set_source_expressions(
427 [
428 None if expr is None else expr.replace_expressions(replacements)
429 for expr in source_expressions
430 ]
431 )
432 return clone
433
434 def get_refs(self):
435 refs = set()
436 for expr in self.get_source_expressions():
437 if expr is None:
438 continue
439 refs |= expr.get_refs()
440 return refs
441
442 def copy(self):
443 return copy.copy(self)
444
445 def prefix_references(self, prefix):
446 clone = self.copy()
447 clone.set_source_expressions(
448 [
449 (
450 F(f"{prefix}{expr.name}")
451 if isinstance(expr, F)
452 else expr.prefix_references(prefix)
453 )
454 for expr in self.get_source_expressions()
455 ]
456 )
457 return clone
458
459 def get_group_by_cols(self):
460 if not self.contains_aggregate:
461 return [self]
462 cols = []
463 for source in self.get_source_expressions():
464 cols.extend(source.get_group_by_cols())
465 return cols
466
467 def get_source_fields(self):
468 """Return the underlying field types used by this aggregate."""
469 return [e._output_field_or_none for e in self.get_source_expressions()]
470
471 def asc(self, **kwargs):
472 return OrderBy(self, **kwargs)
473
474 def desc(self, **kwargs):
475 return OrderBy(self, descending=True, **kwargs)
476
477 def reverse_ordering(self):
478 return self
479
480 def flatten(self):
481 """
482 Recursively yield this expression and all subexpressions, in
483 depth-first order.
484 """
485 yield self
486 for expr in self.get_source_expressions():
487 if expr:
488 if hasattr(expr, "flatten"):
489 yield from expr.flatten()
490 else:
491 yield expr
492
493 def select_format(self, compiler, sql, params):
494 """
495 Custom format for select clauses. For example, EXISTS expressions need
496 to be wrapped in CASE WHEN on Oracle.
497 """
498 if hasattr(self.output_field, "select_format"):
499 return self.output_field.select_format(compiler, sql, params)
500 return sql, params
501
502 def get_expression_for_validation(self):
503 # Ignore expressions that cannot be used during a constraint validation.
504 if not getattr(self, "constraint_validation_compatible", True):
505 try:
506 (expression,) = self.get_source_expressions()
507 except ValueError as e:
508 raise ValueError(
509 "Expressions with constraint_validation_compatible set to False "
510 "must have only one source expression."
511 ) from e
512 else:
513 return expression
514 return self
515
516
517 @deconstructible
518 class Expression(BaseExpression, Combinable):
519 """An expression that can be combined with other expressions."""
520
521 @classproperty
522 @functools.lru_cache(maxsize=128)
523 def _constructor_signature(cls):
524 return signature(cls.__init__)
525
526 @classmethod
527 def _identity(cls, value):
528 if isinstance(value, tuple):
529 return tuple(map(cls._identity, value))
530 if isinstance(value, dict):
531 return tuple((key, cls._identity(val)) for key, val in value.items())
532 if isinstance(value, fields.Field):
533 if value.name and value.model:
534 return value.model._meta.label, value.name
535 return type(value)
536 return make_hashable(value)
537
538 @cached_property
539 def identity(self):
540 args, kwargs = self._constructor_args
541 signature = self._constructor_signature.bind_partial(self, *args, **kwargs)
542 signature.apply_defaults()
543 arguments = iter(signature.arguments.items())
544 next(arguments)
545 identity = [self.__class__]
546 for arg, value in arguments:
547 # If __init__() makes use of *args or **kwargs captures `value`
548 # will respectively be a tuple or a dict that must have its
549 # constituents unpacked (mainly if contain Field instances).
550 value = self._identity(value)
551 identity.append((arg, value))
552 return tuple(identity)
553
554 def __eq__(self, other):
555 if not isinstance(other, Expression):
556 return NotImplemented
557 return other.identity == self.identity
558
559 def __hash__(self):
560 return hash(self.identity)
561
562
563 # Type inference for CombinedExpression.output_field.
564 # Missing items will result in FieldError, by design.
565 #
566 # The current approach for NULL is based on lowest common denominator behavior
567 # i.e. if one of the supported databases is raising an error (rather than
568 # return NULL) for `val <op> NULL`, then Django raises FieldError.
569
570 _connector_combinations = [
571 # Numeric operations - operands of same type.
572 # PositiveIntegerField should take precedence over IntegerField (except
573 # subtraction).
574 {
575 connector: [
576 (
577 fields.PositiveIntegerField,
578 fields.PositiveIntegerField,
579 fields.PositiveIntegerField,
580 ),
581 ]
582 for connector in (
583 Combinable.ADD,
584 Combinable.MUL,
585 Combinable.DIV,
586 Combinable.MOD,
587 Combinable.POW,
588 )
589 },
590 # Other numeric operands.
591 {
592 connector: [
593 (fields.IntegerField, fields.IntegerField, fields.IntegerField),
594 (fields.FloatField, fields.FloatField, fields.FloatField),
595 (fields.DecimalField, fields.DecimalField, fields.DecimalField),
596 ]
597 for connector in (
598 Combinable.ADD,
599 Combinable.SUB,
600 Combinable.MUL,
601 # Behavior for DIV with integer arguments follows Postgres/SQLite,
602 # not MySQL/Oracle.
603 Combinable.DIV,
604 Combinable.MOD,
605 Combinable.POW,
606 )
607 },
608 # Numeric operations - operands of different type.
609 {
610 connector: [
611 (fields.IntegerField, fields.DecimalField, fields.DecimalField),
612 (fields.DecimalField, fields.IntegerField, fields.DecimalField),
613 (fields.IntegerField, fields.FloatField, fields.FloatField),
614 (fields.FloatField, fields.IntegerField, fields.FloatField),
615 ]
616 for connector in (
617 Combinable.ADD,
618 Combinable.SUB,
619 Combinable.MUL,
620 Combinable.DIV,
621 Combinable.MOD,
622 )
623 },
624 # Bitwise operators.
625 {
626 connector: [
627 (fields.IntegerField, fields.IntegerField, fields.IntegerField),
628 ]
629 for connector in (
630 Combinable.BITAND,
631 Combinable.BITOR,
632 Combinable.BITLEFTSHIFT,
633 Combinable.BITRIGHTSHIFT,
634 Combinable.BITXOR,
635 )
636 },
637 # Numeric with NULL.
638 {
639 connector: list(
640 chain.from_iterable(
641 [(field_type, NoneType, field_type), (NoneType, field_type, field_type)]
642 for field_type in (
643 fields.IntegerField,
644 fields.DecimalField,
645 fields.FloatField,
646 )
647 )
648 )
649 for connector in (
650 Combinable.ADD,
651 Combinable.SUB,
652 Combinable.MUL,
653 Combinable.DIV,
654 Combinable.MOD,
655 Combinable.POW,
656 )
657 },
658 # Date/DateTimeField/DurationField/TimeField.
659 {
660 Combinable.ADD: [
661 # Date/DateTimeField.
662 (fields.DateField, fields.DurationField, fields.DateTimeField),
663 (fields.DateTimeField, fields.DurationField, fields.DateTimeField),
664 (fields.DurationField, fields.DateField, fields.DateTimeField),
665 (fields.DurationField, fields.DateTimeField, fields.DateTimeField),
666 # DurationField.
667 (fields.DurationField, fields.DurationField, fields.DurationField),
668 # TimeField.
669 (fields.TimeField, fields.DurationField, fields.TimeField),
670 (fields.DurationField, fields.TimeField, fields.TimeField),
671 ],
672 },
673 {
674 Combinable.SUB: [
675 # Date/DateTimeField.
676 (fields.DateField, fields.DurationField, fields.DateTimeField),
677 (fields.DateTimeField, fields.DurationField, fields.DateTimeField),
678 (fields.DateField, fields.DateField, fields.DurationField),
679 (fields.DateField, fields.DateTimeField, fields.DurationField),
680 (fields.DateTimeField, fields.DateField, fields.DurationField),
681 (fields.DateTimeField, fields.DateTimeField, fields.DurationField),
682 # DurationField.
683 (fields.DurationField, fields.DurationField, fields.DurationField),
684 # TimeField.
685 (fields.TimeField, fields.DurationField, fields.TimeField),
686 (fields.TimeField, fields.TimeField, fields.DurationField),
687 ],
688 },
689 ]
690
691 _connector_combinators = defaultdict(list)
692
693
694 def register_combinable_fields(lhs, connector, rhs, result):
695 """
696 Register combinable types:
697 lhs <connector> rhs -> result
698 e.g.
699 register_combinable_fields(
700 IntegerField, Combinable.ADD, FloatField, FloatField
701 )
702 """
703 _connector_combinators[connector].append((lhs, rhs, result))
704
705
706 for d in _connector_combinations:
707 for connector, field_types in d.items():
708 for lhs, rhs, result in field_types:
709 register_combinable_fields(lhs, connector, rhs, result)
710
711
712 @functools.lru_cache(maxsize=128)
713 def _resolve_combined_type(connector, lhs_type, rhs_type):
714 combinators = _connector_combinators.get(connector, ())
715 for combinator_lhs_type, combinator_rhs_type, combined_type in combinators:
716 if issubclass(lhs_type, combinator_lhs_type) and issubclass(
717 rhs_type, combinator_rhs_type
718 ):
719 return combined_type
720
721
722 class CombinedExpression(SQLiteNumericMixin, Expression):
723 def __init__(self, lhs, connector, rhs, output_field=None):
724 super().__init__(output_field=output_field)
725 self.connector = connector
726 self.lhs = lhs
727 self.rhs = rhs
728
729 def __repr__(self):
730 return "<{}: {}>".format(self.__class__.__name__, self)
731
732 def __str__(self):
733 return "{} {} {}".format(self.lhs, self.connector, self.rhs)
734
735 def get_source_expressions(self):
736 return [self.lhs, self.rhs]
737
738 def set_source_expressions(self, exprs):
739 self.lhs, self.rhs = exprs
740
741 def _resolve_output_field(self):
742 # We avoid using super() here for reasons given in
743 # Expression._resolve_output_field()
744 combined_type = _resolve_combined_type(
745 self.connector,
746 type(self.lhs._output_field_or_none),
747 type(self.rhs._output_field_or_none),
748 )
749 if combined_type is None:
750 raise FieldError(
751 f"Cannot infer type of {self.connector!r} expression involving these "
752 f"types: {self.lhs.output_field.__class__.__name__}, "
753 f"{self.rhs.output_field.__class__.__name__}. You must set "
754 f"output_field."
755 )
756 return combined_type()
757
758 def as_sql(self, compiler, connection):
759 expressions = []
760 expression_params = []
761 sql, params = compiler.compile(self.lhs)
762 expressions.append(sql)
763 expression_params.extend(params)
764 sql, params = compiler.compile(self.rhs)
765 expressions.append(sql)
766 expression_params.extend(params)
767 # order of precedence
768 expression_wrapper = "(%s)"
769 sql = connection.ops.combine_expression(self.connector, expressions)
770 return expression_wrapper % sql, expression_params
771
772 def resolve_expression(
773 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
774 ):
775 resolved = super().resolve_expression(
776 query,
777 allow_joins,
778 reuse,
779 summarize,
780 for_save,
781 )
782 if not isinstance(self, (DurationExpression, TemporalSubtraction)):
783 try:
784 lhs_type = resolved.lhs.output_field.get_internal_type()
785 except (AttributeError, FieldError):
786 lhs_type = None
787 try:
788 rhs_type = resolved.rhs.output_field.get_internal_type()
789 except (AttributeError, FieldError):
790 rhs_type = None
791 if "DurationField" in {lhs_type, rhs_type} and lhs_type != rhs_type:
792 return DurationExpression(
793 resolved.lhs, resolved.connector, resolved.rhs
794 )
795 datetime_fields = {"DateField", "DateTimeField", "TimeField"}
796 if (
797 self.connector == self.SUB
798 and lhs_type in datetime_fields
799 and lhs_type == rhs_type
800 ):
801 return TemporalSubtraction(resolved.lhs, resolved.rhs)
802 return resolved
803
804 @cached_property
805 def allowed_default(self):
806 return self.lhs.allowed_default and self.rhs.allowed_default
807
808
809 class DurationExpression(CombinedExpression):
810 def compile(self, side, compiler, connection):
811 try:
812 output = side.output_field
813 except FieldError:
814 pass
815 else:
816 if output.get_internal_type() == "DurationField":
817 sql, params = compiler.compile(side)
818 return connection.ops.format_for_duration_arithmetic(sql), params
819 return compiler.compile(side)
820
821 def as_sql(self, compiler, connection):
822 if connection.features.has_native_duration_field:
823 return super().as_sql(compiler, connection)
824 connection.ops.check_expression_support(self)
825 expressions = []
826 expression_params = []
827 sql, params = self.compile(self.lhs, compiler, connection)
828 expressions.append(sql)
829 expression_params.extend(params)
830 sql, params = self.compile(self.rhs, compiler, connection)
831 expressions.append(sql)
832 expression_params.extend(params)
833 # order of precedence
834 expression_wrapper = "(%s)"
835 sql = connection.ops.combine_duration_expression(self.connector, expressions)
836 return expression_wrapper % sql, expression_params
837
838 def as_sqlite(self, compiler, connection, **extra_context):
839 sql, params = self.as_sql(compiler, connection, **extra_context)
840 if self.connector in {Combinable.MUL, Combinable.DIV}:
841 try:
842 lhs_type = self.lhs.output_field.get_internal_type()
843 rhs_type = self.rhs.output_field.get_internal_type()
844 except (AttributeError, FieldError):
845 pass
846 else:
847 allowed_fields = {
848 "DecimalField",
849 "DurationField",
850 "FloatField",
851 "IntegerField",
852 }
853 if lhs_type not in allowed_fields or rhs_type not in allowed_fields:
854 raise DatabaseError(
855 f"Invalid arguments for operator {self.connector}."
856 )
857 return sql, params
858
859
860 class TemporalSubtraction(CombinedExpression):
861 output_field = fields.DurationField()
862
863 def __init__(self, lhs, rhs):
864 super().__init__(lhs, self.SUB, rhs)
865
866 def as_sql(self, compiler, connection):
867 connection.ops.check_expression_support(self)
868 lhs = compiler.compile(self.lhs)
869 rhs = compiler.compile(self.rhs)
870 return connection.ops.subtract_temporals(
871 self.lhs.output_field.get_internal_type(), lhs, rhs
872 )
873
874
875 @deconstructible(path="django.db.models.F")
876 class F(Combinable):
877 """An object capable of resolving references to existing query objects."""
878
879 allowed_default = False
880
881 def __init__(self, name):
882 """
883 Arguments:
884 * name: the name of the field this expression references
885 """
886 self.name = name
887
888 def __repr__(self):
889 return "{}({})".format(self.__class__.__name__, self.name)
890
891 def __getitem__(self, subscript):
892 return Sliced(self, subscript)
893
894 def __contains__(self, other):
895 # Disable old-style iteration protocol inherited from implementing
896 # __getitem__() to prevent this method from hanging.
897 raise TypeError(f"argument of type '{self.__class__.__name__}' is not iterable")
898
899 def resolve_expression(
900 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
901 ):
902 return query.resolve_ref(self.name, allow_joins, reuse, summarize)
903
904 def replace_expressions(self, replacements):
905 if (replacement := replacements.get(self)) is not None:
906 return replacement
907 field_name, *transforms = self.name.split(LOOKUP_SEP)
908 # Avoid unnecessarily looking up replacements with field_name again as
909 # in the vast majority of cases F instances won't be composed of any
910 # lookups.
911 if not transforms:
912 return self
913 if (
914 replacement := replacements.get(F(field_name))
915 ) is None or replacement._output_field_or_none is None:
916 return self
917 for transform in transforms:
918 transform_class = replacement.get_transform(transform)
919 if transform_class is None:
920 return self
921 replacement = transform_class(replacement)
922 return replacement
923
924 def asc(self, **kwargs):
925 return OrderBy(self, **kwargs)
926
927 def desc(self, **kwargs):
928 return OrderBy(self, descending=True, **kwargs)
929
930 def __eq__(self, other):
931 return self.__class__ == other.__class__ and self.name == other.name
932
933 def __hash__(self):
934 return hash(self.name)
935
936 def copy(self):
937 return copy.copy(self)
938
939
940 class ResolvedOuterRef(F):
941 """
942 An object that contains a reference to an outer query.
943
944 In this case, the reference to the outer query has been resolved because
945 the inner query has been used as a subquery.
946 """
947
948 contains_aggregate = False
949 contains_over_clause = False
950
951 def as_sql(self, *args, **kwargs):
952 raise ValueError(
953 "This queryset contains a reference to an outer query and may "
954 "only be used in a subquery."
955 )
956
957 def resolve_expression(self, *args, **kwargs):
958 col = super().resolve_expression(*args, **kwargs)
959 if col.contains_over_clause:
960 raise NotSupportedError(
961 f"Referencing outer query window expression is not supported: "
962 f"{self.name}."
963 )
964 # FIXME: Rename possibly_multivalued to multivalued and fix detection
965 # for non-multivalued JOINs (e.g. foreign key fields). This should take
966 # into account only many-to-many and one-to-many relationships.
967 col.possibly_multivalued = LOOKUP_SEP in self.name
968 return col
969
970 def relabeled_clone(self, relabels):
971 return self
972
973 def get_group_by_cols(self):
974 return []
975
976
977 class OuterRef(F):
978 contains_aggregate = False
979 contains_over_clause = False
980
981 def resolve_expression(self, *args, **kwargs):
982 if isinstance(self.name, self.__class__):
983 return self.name
984 return ResolvedOuterRef(self.name)
985
986 def relabeled_clone(self, relabels):
987 return self
988
989
990 class Sliced(F):
991 """
992 An object that contains a slice of an F expression.
993
994 Object resolves the column on which the slicing is applied, and then
995 applies the slicing if possible.
996 """
997
998 def __init__(self, obj, subscript):
999 super().__init__(obj.name)
1000 self.obj = obj
1001 if isinstance(subscript, int):
1002 if subscript < 0:
1003 raise ValueError("Negative indexing is not supported.")
1004 self.start = subscript + 1
1005 self.length = 1
1006 elif isinstance(subscript, slice):
1007 if (subscript.start is not None and subscript.start < 0) or (
1008 subscript.stop is not None and subscript.stop < 0
1009 ):
1010 raise ValueError("Negative indexing is not supported.")
1011 if subscript.step is not None:
1012 raise ValueError("Step argument is not supported.")
1013 if subscript.stop and subscript.start and subscript.stop < subscript.start:
1014 raise ValueError("Slice stop must be greater than slice start.")
1015 self.start = 1 if subscript.start is None else subscript.start + 1
1016 if subscript.stop is None:
1017 self.length = None
1018 else:
1019 self.length = subscript.stop - (subscript.start or 0)
1020 else:
1021 raise TypeError("Argument to slice must be either int or slice instance.")
1022
1023 def __repr__(self):
1024 start = self.start - 1
1025 stop = None if self.length is None else start + self.length
1026 subscript = slice(start, stop)
1027 return f"{self.__class__.__qualname__}({self.obj!r}, {subscript!r})"
1028
1029 def resolve_expression(
1030 self,
1031 query=None,
1032 allow_joins=True,
1033 reuse=None,
1034 summarize=False,
1035 for_save=False,
1036 ):
1037 resolved = query.resolve_ref(self.name, allow_joins, reuse, summarize)
1038 if isinstance(self.obj, (OuterRef, self.__class__)):
1039 expr = self.obj.resolve_expression(
1040 query, allow_joins, reuse, summarize, for_save
1041 )
1042 else:
1043 expr = resolved
1044 return resolved.output_field.slice_expression(expr, self.start, self.length)
1045
1046
1047 @deconstructible(path="django.db.models.Func")
1048 class Func(SQLiteNumericMixin, Expression):
1049 """An SQL function call."""
1050
1051 function = None
1052 template = "%(function)s(%(expressions)s)"
1053 arg_joiner = ", "
1054 arity = None # The number of arguments the function accepts.
1055
1056 def __init__(self, *expressions, output_field=None, **extra):
1057 if self.arity is not None and len(expressions) != self.arity:
1058 raise TypeError(
1059 "'%s' takes exactly %s %s (%s given)"
1060 % (
1061 self.__class__.__name__,
1062 self.arity,
1063 "argument" if self.arity == 1 else "arguments",
1064 len(expressions),
1065 )
1066 )
1067 super().__init__(output_field=output_field)
1068 self.source_expressions = self._parse_expressions(*expressions)
1069 self.extra = extra
1070
1071 def __repr__(self):
1072 args = self.arg_joiner.join(str(arg) for arg in self.source_expressions)
1073 extra = {**self.extra, **self._get_repr_options()}
1074 if extra:
1075 extra = ", ".join(
1076 str(key) + "=" + str(val) for key, val in sorted(extra.items())
1077 )
1078 return "{}({}, {})".format(self.__class__.__name__, args, extra)
1079 return "{}({})".format(self.__class__.__name__, args)
1080
1081 def _get_repr_options(self):
1082 """Return a dict of extra __init__() options to include in the repr."""
1083 return {}
1084
1085 def get_source_expressions(self):
1086 return self.source_expressions
1087
1088 def set_source_expressions(self, exprs):
1089 self.source_expressions = exprs
1090
1091 def as_sql(
1092 self,
1093 compiler,
1094 connection,
1095 function=None,
1096 template=None,
1097 arg_joiner=None,
1098 **extra_context,
1099 ):
1100 connection.ops.check_expression_support(self)
1101 sql_parts = []
1102 params = []
1103 for arg in self.source_expressions:
1104 try:
1105 arg_sql, arg_params = compiler.compile(arg)
1106 except EmptyResultSet:
1107 empty_result_set_value = getattr(
1108 arg, "empty_result_set_value", NotImplemented
1109 )
1110 if empty_result_set_value is NotImplemented:
1111 raise
1112 arg_sql, arg_params = compiler.compile(Value(empty_result_set_value))
1113 except FullResultSet:
1114 arg_sql, arg_params = compiler.compile(Value(True))
1115 sql_parts.append(arg_sql)
1116 params.extend(arg_params)
1117 data = {**self.extra, **extra_context}
1118 # Use the first supplied value in this order: the parameter to this
1119 # method, a value supplied in __init__()'s **extra (the value in
1120 # `data`), or the value defined on the class.
1121 if function is not None:
1122 data["function"] = function
1123 else:
1124 data.setdefault("function", self.function)
1125 template = template or data.get("template", self.template)
1126 arg_joiner = arg_joiner or data.get("arg_joiner", self.arg_joiner)
1127 data["expressions"] = data["field"] = arg_joiner.join(sql_parts)
1128 return template % data, params
1129
1130 def copy(self):
1131 copy = super().copy()
1132 copy.source_expressions = self.source_expressions[:]
1133 copy.extra = self.extra.copy()
1134 return copy
1135
1136 @cached_property
1137 def allowed_default(self):
1138 return all(expression.allowed_default for expression in self.source_expressions)
1139
1140
1141 @deconstructible(path="django.db.models.Value")
1142 class Value(SQLiteNumericMixin, Expression):
1143 """Represent a wrapped value as a node within an expression."""
1144
1145 # Provide a default value for `for_save` in order to allow unresolved
1146 # instances to be compiled until a decision is taken in #25425.
1147 for_save = False
1148 allowed_default = True
1149
1150 def __init__(self, value, output_field=None):
1151 """
1152 Arguments:
1153 * value: the value this expression represents. The value will be
1154 added into the sql parameter list and properly quoted.
1155
1156 * output_field: an instance of the model field type that this
1157 expression will return, such as IntegerField() or CharField().
1158 """
1159 super().__init__(output_field=output_field)
1160 self.value = value
1161
1162 def __repr__(self):
1163 return f"{self.__class__.__name__}({self.value!r})"
1164
1165 def as_sql(self, compiler, connection):
1166 connection.ops.check_expression_support(self)
1167 val = self.value
1168 output_field = self._output_field_or_none
1169 if output_field is not None:
1170 if self.for_save:
1171 val = output_field.get_db_prep_save(val, connection=connection)
1172 else:
1173 val = output_field.get_db_prep_value(val, connection=connection)
1174 if hasattr(output_field, "get_placeholder"):
1175 return output_field.get_placeholder(val, compiler, connection), [val]
1176 if val is None:
1177 # oracledb does not always convert None to the appropriate
1178 # NULL type (like in case expressions using numbers), so we
1179 # use a literal SQL NULL
1180 return "NULL", []
1181 return "%s", [val]
1182
1183 def resolve_expression(
1184 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1185 ):
1186 c = super().resolve_expression(query, allow_joins, reuse, summarize, for_save)
1187 c.for_save = for_save
1188 return c
1189
1190 def get_group_by_cols(self):
1191 return []
1192
1193 def _resolve_output_field(self):
1194 if isinstance(self.value, str):
1195 return fields.CharField()
1196 if isinstance(self.value, bool):
1197 return fields.BooleanField()
1198 if isinstance(self.value, int):
1199 return fields.IntegerField()
1200 if isinstance(self.value, float):
1201 return fields.FloatField()
1202 if isinstance(self.value, datetime.datetime):
1203 return fields.DateTimeField()
1204 if isinstance(self.value, datetime.date):
1205 return fields.DateField()
1206 if isinstance(self.value, datetime.time):
1207 return fields.TimeField()
1208 if isinstance(self.value, datetime.timedelta):
1209 return fields.DurationField()
1210 if isinstance(self.value, Decimal):
1211 return fields.DecimalField()
1212 if isinstance(self.value, bytes):
1213 return fields.BinaryField()
1214 if isinstance(self.value, UUID):
1215 return fields.UUIDField()
1216
1217 @property
1218 def empty_result_set_value(self):
1219 return self.value
1220
1221
1222 class RawSQL(Expression):
1223 allowed_default = True
1224
1225 def __init__(self, sql, params, output_field=None):
1226 if output_field is None:
1227 output_field = fields.Field()
1228 self.sql, self.params = sql, params
1229 super().__init__(output_field=output_field)
1230
1231 def __repr__(self):
1232 return "{}({}, {})".format(self.__class__.__name__, self.sql, self.params)
1233
1234 def as_sql(self, compiler, connection):
1235 return "(%s)" % self.sql, self.params
1236
1237 def get_group_by_cols(self):
1238 return [self]
1239
1240 def resolve_expression(
1241 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1242 ):
1243 # Resolve parents fields used in raw SQL.
1244 if query.model:
1245 for parent in query.model._meta.all_parents:
1246 for parent_field in parent._meta.local_fields:
1247 if parent_field.column.lower() in self.sql.lower():
1248 query.resolve_ref(
1249 parent_field.name, allow_joins, reuse, summarize
1250 )
1251 break
1252 return super().resolve_expression(
1253 query, allow_joins, reuse, summarize, for_save
1254 )
1255
1256
1257 class Star(Expression):
1258 def __repr__(self):
1259 return "'*'"
1260
1261 def as_sql(self, compiler, connection):
1262 return "*", []
1263
1264
1265 class DatabaseDefault(Expression):
1266 """
1267 Expression to use DEFAULT keyword during insert otherwise the underlying expression.
1268 """
1269
1270 def __init__(self, expression, output_field=None):
1271 super().__init__(output_field)
1272 self.expression = expression
1273
1274 def get_source_expressions(self):
1275 return [self.expression]
1276
1277 def set_source_expressions(self, exprs):
1278 (self.expression,) = exprs
1279
1280 def resolve_expression(
1281 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1282 ):
1283 resolved_expression = self.expression.resolve_expression(
1284 query=query,
1285 allow_joins=allow_joins,
1286 reuse=reuse,
1287 summarize=summarize,
1288 for_save=for_save,
1289 )
1290 # Defaults used outside an INSERT context should resolve to their
1291 # underlying expression.
1292 if not for_save:
1293 return resolved_expression
1294 return DatabaseDefault(
1295 resolved_expression, output_field=self._output_field_or_none
1296 )
1297
1298 def as_sql(self, compiler, connection):
1299 if not connection.features.supports_default_keyword_in_insert:
1300 return compiler.compile(self.expression)
1301 return "DEFAULT", []
1302
1303
1304 class Col(Expression):
1305 contains_column_references = True
1306 possibly_multivalued = False
1307
1308 def __init__(self, alias, target, output_field=None):
1309 if output_field is None:
1310 output_field = target
1311 super().__init__(output_field=output_field)
1312 self.alias, self.target = alias, target
1313
1314 def __repr__(self):
1315 alias, target = self.alias, self.target
1316 identifiers = (alias, str(target)) if alias else (str(target),)
1317 return "{}({})".format(self.__class__.__name__, ", ".join(identifiers))
1318
1319 def as_sql(self, compiler, connection):
1320 alias, column = self.alias, self.target.column
1321 identifiers = (alias, column) if alias else (column,)
1322 sql = ".".join(map(compiler.quote_name_unless_alias, identifiers))
1323 return sql, []
1324
1325 def relabeled_clone(self, relabels):
1326 if self.alias is None:
1327 return self
1328 return self.__class__(
1329 relabels.get(self.alias, self.alias), self.target, self.output_field
1330 )
1331
1332 def get_group_by_cols(self):
1333 return [self]
1334
1335 def get_db_converters(self, connection):
1336 if self.target == self.output_field:
1337 return self.output_field.get_db_converters(connection)
1338 return self.output_field.get_db_converters(
1339 connection
1340 ) + self.target.get_db_converters(connection)
1341
1342
1343 class ColPairs(Expression):
1344 def __init__(self, alias, targets, sources, output_field):
1345 super().__init__(output_field=output_field)
1346 self.alias = alias
1347 self.targets = targets
1348 self.sources = sources
1349
1350 def __len__(self):
1351 return len(self.targets)
1352
1353 def __iter__(self):
1354 return iter(self.get_cols())
1355
1356 def __repr__(self):
1357 return (
1358 f"{self.__class__.__name__}({self.alias!r}, {self.targets!r}, "
1359 f"{self.sources!r}, {self.output_field!r})"
1360 )
1361
1362 def get_cols(self):
1363 return [
1364 Col(self.alias, target, source)
1365 for target, source in zip(self.targets, self.sources)
1366 ]
1367
1368 def get_source_expressions(self):
1369 return self.get_cols()
1370
1371 def set_source_expressions(self, exprs):
1372 assert all(isinstance(expr, Col) and expr.alias == self.alias for expr in exprs)
1373 self.targets = [col.target for col in exprs]
1374 self.sources = [col.field for col in exprs]
1375
1376 def as_sql(self, compiler, connection):
1377 cols_sql = []
1378 cols_params = []
1379 cols = self.get_cols()
1380
1381 for col in cols:
1382 sql, params = col.as_sql(compiler, connection)
1383 cols_sql.append(sql)
1384 cols_params.extend(params)
1385
1386 return ", ".join(cols_sql), cols_params
1387
1388 def relabeled_clone(self, relabels):
1389 return self.__class__(
1390 relabels.get(self.alias, self.alias), self.targets, self.sources, self.field
1391 )
1392
1393 def resolve_expression(self, *args, **kwargs):
1394 return self
1395
1396 def select_format(self, compiler, sql, params):
1397 return sql, params
1398
1399
1400 class Ref(Expression):
1401 """
1402 Reference to column alias of the query. For example, Ref('sum_cost') in
1403 qs.annotate(sum_cost=Sum('cost')) query.
1404 """
1405
1406 def __init__(self, refs, source):
1407 super().__init__()
1408 self.refs, self.source = refs, source
1409
1410 def __repr__(self):
1411 return "{}({}, {})".format(self.__class__.__name__, self.refs, self.source)
1412
1413 def get_source_expressions(self):
1414 return [self.source]
1415
1416 def set_source_expressions(self, exprs):
1417 (self.source,) = exprs
1418
1419 def resolve_expression(
1420 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1421 ):
1422 # The sub-expression `source` has already been resolved, as this is
1423 # just a reference to the name of `source`.
1424 return self
1425
1426 def get_refs(self):
1427 return {self.refs}
1428
1429 def relabeled_clone(self, relabels):
1430 clone = self.copy()
1431 clone.source = self.source.relabeled_clone(relabels)
1432 return clone
1433
1434 def as_sql(self, compiler, connection):
1435 return connection.ops.quote_name(self.refs), []
1436
1437 def get_group_by_cols(self):
1438 return [self]
1439
1440
1441 class ExpressionList(Func):
1442 """
1443 An expression containing multiple expressions. Can be used to provide a
1444 list of expressions as an argument to another expression, like a partition
1445 clause.
1446 """
1447
1448 template = "%(expressions)s"
1449
1450 def __str__(self):
1451 return self.arg_joiner.join(str(arg) for arg in self.source_expressions)
1452
1453 def as_sql(self, *args, **kwargs):
1454 if not self.source_expressions:
1455 return "", ()
1456 return super().as_sql(*args, **kwargs)
1457
1458 def as_sqlite(self, compiler, connection, **extra_context):
1459 # Casting to numeric is unnecessary.
1460 return self.as_sql(compiler, connection, **extra_context)
1461
1462 def get_group_by_cols(self):
1463 group_by_cols = []
1464 for expr in self.get_source_expressions():
1465 group_by_cols.extend(expr.get_group_by_cols())
1466 return group_by_cols
1467
1468
1469 class OrderByList(ExpressionList):
1470 allowed_default = False
1471 template = "ORDER BY %(expressions)s"
1472
1473 def __init__(self, *expressions, **extra):
1474 expressions = (
1475 (
1476 OrderBy(F(expr[1:]), descending=True)
1477 if isinstance(expr, str) and expr[0] == "-"
1478 else expr
1479 )
1480 for expr in expressions
1481 )
1482 super().__init__(*expressions, **extra)
1483
1484
1485 @deconstructible(path="django.db.models.ExpressionWrapper")
1486 class ExpressionWrapper(SQLiteNumericMixin, Expression):
1487 """
1488 An expression that can wrap another expression so that it can provide
1489 extra context to the inner expression, such as the output_field.
1490 """
1491
1492 def __init__(self, expression, output_field):
1493 super().__init__(output_field=output_field)
1494 self.expression = expression
1495
1496 def set_source_expressions(self, exprs):
1497 self.expression = exprs[0]
1498
1499 def get_source_expressions(self):
1500 return [self.expression]
1501
1502 def get_group_by_cols(self):
1503 if isinstance(self.expression, Expression):
1504 expression = self.expression.copy()
1505 expression.output_field = self.output_field
1506 return expression.get_group_by_cols()
1507 # For non-expressions e.g. an SQL WHERE clause, the entire
1508 # `expression` must be included in the GROUP BY clause.
1509 return super().get_group_by_cols()
1510
1511 def as_sql(self, compiler, connection):
1512 return compiler.compile(self.expression)
1513
1514 def __repr__(self):
1515 return "{}({})".format(self.__class__.__name__, self.expression)
1516
1517 @property
1518 def allowed_default(self):
1519 return self.expression.allowed_default
1520
1521
1522 class NegatedExpression(ExpressionWrapper):
1523 """The logical negation of a conditional expression."""
1524
1525 def __init__(self, expression):
1526 super().__init__(expression, output_field=fields.BooleanField())
1527
1528 def __invert__(self):
1529 return self.expression.copy()
1530
1531 def as_sql(self, compiler, connection):
1532 try:
1533 sql, params = super().as_sql(compiler, connection)
1534 except EmptyResultSet:
1535 features = compiler.connection.features
1536 if not features.supports_boolean_expr_in_select_clause:
1537 return "1=1", ()
1538 return compiler.compile(Value(True))
1539 ops = compiler.connection.ops
1540 # Some database backends (e.g. Oracle) don't allow EXISTS() and filters
1541 # to be compared to another expression unless they're wrapped in a CASE
1542 # WHEN.
1543 if not ops.conditional_expression_supported_in_where_clause(self.expression):
1544 return f"CASE WHEN {sql} = 0 THEN 1 ELSE 0 END", params
1545 return f"NOT {sql}", params
1546
1547 def resolve_expression(
1548 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1549 ):
1550 resolved = super().resolve_expression(
1551 query, allow_joins, reuse, summarize, for_save
1552 )
1553 if not getattr(resolved.expression, "conditional", False):
1554 raise TypeError("Cannot negate non-conditional expressions.")
1555 return resolved
1556
1557 def select_format(self, compiler, sql, params):
1558 # Wrap boolean expressions with a CASE WHEN expression if a database
1559 # backend (e.g. Oracle) doesn't support boolean expression in SELECT or
1560 # GROUP BY list.
1561 expression_supported_in_where_clause = (
1562 compiler.connection.ops.conditional_expression_supported_in_where_clause
1563 )
1564 if (
1565 not compiler.connection.features.supports_boolean_expr_in_select_clause
1566 # Avoid double wrapping.
1567 and expression_supported_in_where_clause(self.expression)
1568 ):
1569 sql = "CASE WHEN {} THEN 1 ELSE 0 END".format(sql)
1570 return sql, params
1571
1572
1573 @deconstructible(path="django.db.models.When")
1574 class When(Expression):
1575 template = "WHEN %(condition)s THEN %(result)s"
1576 # This isn't a complete conditional expression, must be used in Case().
1577 conditional = False
1578
1579 def __init__(self, condition=None, then=None, **lookups):
1580 if lookups:
1581 if condition is None:
1582 condition, lookups = Q(**lookups), None
1583 elif getattr(condition, "conditional", False):
1584 condition, lookups = Q(condition, **lookups), None
1585 if condition is None or not getattr(condition, "conditional", False) or lookups:
1586 raise TypeError(
1587 "When() supports a Q object, a boolean expression, or lookups "
1588 "as a condition."
1589 )
1590 if isinstance(condition, Q) and not condition:
1591 raise ValueError("An empty Q() can't be used as a When() condition.")
1592 super().__init__(output_field=None)
1593 self.condition = condition
1594 self.result = self._parse_expressions(then)[0]
1595
1596 def __str__(self):
1597 return "WHEN %r THEN %r" % (self.condition, self.result)
1598
1599 def __repr__(self):
1600 return "<%s: %s>" % (self.__class__.__name__, self)
1601
1602 def get_source_expressions(self):
1603 return [self.condition, self.result]
1604
1605 def set_source_expressions(self, exprs):
1606 self.condition, self.result = exprs
1607
1608 def resolve_expression(
1609 self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False
1610 ):
1611 c = super().resolve_expression(query, allow_joins, reuse, summarize, for_save)
1612 if for_save and c.condition is not None:
1613 # Resolve condition with for_save=False, since it's used as a filter.
1614 c.condition = self.condition.resolve_expression(
1615 query, allow_joins, reuse, summarize, for_save=False
1616 )
1617 return c
1618
1619 def get_source_fields(self):
1620 # We're only interested in the fields of the result expressions.
1621 return [self.result._output_field_or_none]
1622
1623 def as_sql(self, compiler, connection, template=None, **extra_context):
1624 connection.ops.check_expression_support(self)
1625 template_params = extra_context
1626 sql_params = []
1627 condition_sql, condition_params = compiler.compile(self.condition)
1628 template_params["condition"] = condition_sql
1629 result_sql, result_params = compiler.compile(self.result)
1630 template_params["result"] = result_sql
1631 template = template or self.template
1632 return template % template_params, (
1633 *sql_params,
1634 *condition_params,
1635 *result_params,
1636 )
1637
1638 def get_group_by_cols(self):
1639 # This is not a complete expression and cannot be used in GROUP BY.
1640 cols = []
1641 for source in self.get_source_expressions():
1642 cols.extend(source.get_group_by_cols())
1643 return cols
1644
1645 @cached_property
1646 def allowed_default(self):
1647 return self.condition.allowed_default and self.result.allowed_default
1648
1649
1650 @deconstructible(path="django.db.models.Case")
1651 class Case(SQLiteNumericMixin, Expression):
1652 """
1653 An SQL searched CASE expression:
1654
1655 CASE
1656 WHEN n > 0
1657 THEN 'positive'
1658 WHEN n < 0
1659 THEN 'negative'
1660 ELSE 'zero'
1661 END
1662 """
1663
1664 template = "CASE %(cases)s ELSE %(default)s END"
1665 case_joiner = " "
1666
1667 def __init__(self, *cases, default=None, output_field=None, **extra):
1668 if not all(isinstance(case, When) for case in cases):
1669 raise TypeError("Positional arguments must all be When objects.")
1670 super().__init__(output_field)
1671 self.cases = list(cases)
1672 self.default = self._parse_expressions(default)[0]
1673 self.extra = extra
1674
1675 def __str__(self):
1676 return "CASE %s, ELSE %r" % (
1677 ", ".join(str(c) for c in self.cases),
1678 self.default,
1679 )
1680
1681 def __repr__(self):
1682 return "<%s: %s>" % (self.__class__.__name__, self)
1683
1684 def get_source_expressions(self):
1685 return self.cases + [self.default]
1686
1687 def set_source_expressions(self, exprs):
1688 *self.cases, self.default = exprs
1689
1690 def copy(self):
1691 c = super().copy()
1692 c.cases = c.cases[:]
1693 return c
1694
1695 def as_sql(
1696 self, compiler, connection, template=None, case_joiner=None, **extra_context
1697 ):
1698 connection.ops.check_expression_support(self)
1699 if not self.cases:
1700 return compiler.compile(self.default)
1701 template_params = {**self.extra, **extra_context}
1702 case_parts = []
1703 sql_params = []
1704 for case in self.cases:
1705 try:
1706 case_sql, case_params = compiler.compile(case)
1707 except EmptyResultSet:
1708 continue
1709 except FullResultSet:
1710 default_sql, default_params = compiler.compile(case.result)
1711 break
1712 case_parts.append(case_sql)
1713 sql_params.extend(case_params)
1714 else:
1715 default_sql, default_params = compiler.compile(self.default)
1716 if not case_parts:
1717 return default_sql, default_params
1718 case_joiner = case_joiner or self.case_joiner
1719 template_params["cases"] = case_joiner.join(case_parts)
1720 template_params["default"] = default_sql
1721 sql_params.extend(default_params)
1722 template = template or template_params.get("template", self.template)
1723 sql = template % template_params
1724 if self._output_field_or_none is not None:
1725 sql = connection.ops.unification_cast_sql(self.output_field) % sql
1726 return sql, sql_params
1727
1728 def get_group_by_cols(self):
1729 if not self.cases:
1730 return self.default.get_group_by_cols()
1731 return super().get_group_by_cols()
1732
1733 @cached_property
1734 def allowed_default(self):
1735 return self.default.allowed_default and all(
1736 case_.allowed_default for case_ in self.cases
1737 )
1738
1739
1740 class Subquery(BaseExpression, Combinable):
1741 """
1742 An explicit subquery. It may contain OuterRef() references to the outer
1743 query which will be resolved when it is applied to that query.
1744 """
1745
1746 template = "(%(subquery)s)"
1747 contains_aggregate = False
1748 empty_result_set_value = None
1749 subquery = True
1750
1751 def __init__(self, queryset, output_field=None, **extra):
1752 # Allow the usage of both QuerySet and sql.Query objects.
1753 self.query = getattr(queryset, "query", queryset).clone()
1754 self.query.subquery = True
1755 self.extra = extra
1756 super().__init__(output_field)
1757
1758 def get_source_expressions(self):
1759 return [self.query]
1760
1761 def set_source_expressions(self, exprs):
1762 self.query = exprs[0]
1763
1764 def _resolve_output_field(self):
1765 return self.query.output_field
1766
1767 def copy(self):
1768 clone = super().copy()
1769 clone.query = clone.query.clone()
1770 return clone
1771
1772 @property
1773 def external_aliases(self):
1774 return self.query.external_aliases
1775
1776 def get_external_cols(self):
1777 return self.query.get_external_cols()
1778
1779 def as_sql(self, compiler, connection, template=None, **extra_context):
1780 connection.ops.check_expression_support(self)
1781 template_params = {**self.extra, **extra_context}
1782 subquery_sql, sql_params = self.query.as_sql(compiler, connection)
1783 template_params["subquery"] = subquery_sql[1:-1]
1784
1785 template = template or template_params.get("template", self.template)
1786 sql = template % template_params
1787 return sql, sql_params
1788
1789 def get_group_by_cols(self):
1790 return self.query.get_group_by_cols(wrapper=self)
1791
1792
1793 class Exists(Subquery):
1794 template = "EXISTS(%(subquery)s)"
1795 output_field = fields.BooleanField()
1796 empty_result_set_value = False
1797
1798 def __init__(self, queryset, **kwargs):
1799 super().__init__(queryset, **kwargs)
1800 self.query = self.query.exists()
1801
1802 def select_format(self, compiler, sql, params):
1803 # Wrap EXISTS() with a CASE WHEN expression if a database backend
1804 # (e.g. Oracle) doesn't support boolean expression in SELECT or GROUP
1805 # BY list.
1806 if not compiler.connection.features.supports_boolean_expr_in_select_clause:
1807 sql = "CASE WHEN {} THEN 1 ELSE 0 END".format(sql)
1808 return sql, params
1809
1810 def as_sql(self, compiler, *args, **kwargs):
1811 try:
1812 return super().as_sql(compiler, *args, **kwargs)
1813 except EmptyResultSet:
1814 features = compiler.connection.features
1815 if not features.supports_boolean_expr_in_select_clause:
1816 return "1=0", ()
1817 return compiler.compile(Value(False))
1818
1819
1820 @deconstructible(path="django.db.models.OrderBy")
1821 class OrderBy(Expression):
1822 template = "%(expression)s %(ordering)s"
1823 conditional = False
1824 constraint_validation_compatible = False
1825 allows_composite_expressions = True
1826
1827 def __init__(self, expression, descending=False, nulls_first=None, nulls_last=None):
1828 if nulls_first and nulls_last:
1829 raise ValueError("nulls_first and nulls_last are mutually exclusive")
1830 if nulls_first is False or nulls_last is False:
1831 raise ValueError("nulls_first and nulls_last values must be True or None.")
1832 self.nulls_first = nulls_first
1833 self.nulls_last = nulls_last
1834 self.descending = descending
1835 if not hasattr(expression, "resolve_expression"):
1836 raise ValueError("expression must be an expression type")
1837 self.expression = expression
1838
1839 def __repr__(self):
1840 return "{}({}, descending={})".format(
1841 self.__class__.__name__, self.expression, self.descending
1842 )
1843
1844 def set_source_expressions(self, exprs):
1845 self.expression = exprs[0]
1846
1847 def get_source_expressions(self):
1848 return [self.expression]
1849
1850 def as_sql(self, compiler, connection, template=None, **extra_context):
1851 if isinstance(self.expression, ColPairs):
1852 sql_parts = []
1853 params = []
1854 for col in self.expression.get_cols():
1855 copy = self.copy()
1856 copy.set_source_expressions([col])
1857 sql, col_params = compiler.compile(copy)
1858 sql_parts.append(sql)
1859 params.extend(col_params)
1860 return ", ".join(sql_parts), params
1861 template = template or self.template
1862 if connection.features.supports_order_by_nulls_modifier:
1863 if self.nulls_last:
1864 template = "%s NULLS LAST" % template
1865 elif self.nulls_first:
1866 template = "%s NULLS FIRST" % template
1867 else:
1868 if self.nulls_last and not (
1869 self.descending and connection.features.order_by_nulls_first
1870 ):
1871 template = "%%(expression)s IS NULL, %s" % template
1872 elif self.nulls_first and not (
1873 not self.descending and connection.features.order_by_nulls_first
1874 ):
1875 template = "%%(expression)s IS NOT NULL, %s" % template
1876 connection.ops.check_expression_support(self)
1877 expression_sql, params = compiler.compile(self.expression)
1878 placeholders = {
1879 "expression": expression_sql,
1880 "ordering": "DESC" if self.descending else "ASC",
1881 **extra_context,
1882 }
1883 params *= template.count("%(expression)s")
1884 return (template % placeholders).rstrip(), params
1885
1886 def as_oracle(self, compiler, connection):
1887 # Oracle < 23c doesn't allow ORDER BY EXISTS() or filters unless it's
1888 # wrapped in a CASE WHEN.
1889 if (
1890 not connection.features.supports_boolean_expr_in_select_clause
1891 and connection.ops.conditional_expression_supported_in_where_clause(
1892 self.expression
1893 )
1894 ):
1895 copy = self.copy()
1896 copy.expression = Case(
1897 When(self.expression, then=True),
1898 default=False,
1899 )
1900 return copy.as_sql(compiler, connection)
1901 return self.as_sql(compiler, connection)
1902
1903 def get_group_by_cols(self):
1904 cols = []
1905 for source in self.get_source_expressions():
1906 cols.extend(source.get_group_by_cols())
1907 return cols
1908
1909 def reverse_ordering(self):
1910 self.descending = not self.descending
1911 if self.nulls_first:
1912 self.nulls_last = True
1913 self.nulls_first = None
1914 elif self.nulls_last:
1915 self.nulls_first = True
1916 self.nulls_last = None
1917 return self
1918
1919 def asc(self):
1920 self.descending = False
1921
1922 def desc(self):
1923 self.descending = True
1924
1925
1926 class Window(SQLiteNumericMixin, Expression):
1927 template = "%(expression)s OVER (%(window)s)"
1928 # Although the main expression may either be an aggregate or an
1929 # expression with an aggregate function, the GROUP BY that will
1930 # be introduced in the query as a result is not desired.
1931 contains_aggregate = False
1932 contains_over_clause = True
1933
1934 def __init__(
1935 self,
1936 expression,
1937 partition_by=None,
1938 order_by=None,
1939 frame=None,
1940 output_field=None,
1941 ):
1942 self.partition_by = partition_by
1943 self.order_by = order_by
1944 self.frame = frame
1945
1946 if not getattr(expression, "window_compatible", False):
1947 raise ValueError(
1948 "Expression '%s' isn't compatible with OVER clauses."
1949 % expression.__class__.__name__
1950 )
1951
1952 if self.partition_by is not None:
1953 if not isinstance(self.partition_by, (tuple, list)):
1954 self.partition_by = (self.partition_by,)
1955 self.partition_by = ExpressionList(*self.partition_by)
1956
1957 if self.order_by is not None:
1958 if isinstance(self.order_by, (list, tuple)):
1959 self.order_by = OrderByList(*self.order_by)
1960 elif isinstance(self.order_by, (BaseExpression, str)):
1961 self.order_by = OrderByList(self.order_by)
1962 else:
1963 raise ValueError(
1964 "Window.order_by must be either a string reference to a "
1965 "field, an expression, or a list or tuple of them."
1966 )
1967 super().__init__(output_field=output_field)
1968 self.source_expression = self._parse_expressions(expression)[0]
1969
1970 def _resolve_output_field(self):
1971 return self.source_expression.output_field
1972
1973 def get_source_expressions(self):
1974 return [self.source_expression, self.partition_by, self.order_by, self.frame]
1975
1976 def set_source_expressions(self, exprs):
1977 self.source_expression, self.partition_by, self.order_by, self.frame = exprs
1978
1979 def as_sql(self, compiler, connection, template=None):
1980 connection.ops.check_expression_support(self)
1981 if not connection.features.supports_over_clause:
1982 raise NotSupportedError("This backend does not support window expressions.")
1983 expr_sql, params = compiler.compile(self.source_expression)
1984 window_sql, window_params = [], ()
1985
1986 if self.partition_by is not None:
1987 sql_expr, sql_params = self.partition_by.as_sql(
1988 compiler=compiler,
1989 connection=connection,
1990 template="PARTITION BY %(expressions)s",
1991 )
1992 window_sql.append(sql_expr)
1993 window_params += tuple(sql_params)
1994
1995 if self.order_by is not None:
1996 order_sql, order_params = compiler.compile(self.order_by)
1997 window_sql.append(order_sql)
1998 window_params += tuple(order_params)
1999
2000 if self.frame:
2001 frame_sql, frame_params = compiler.compile(self.frame)
2002 window_sql.append(frame_sql)
2003 window_params += tuple(frame_params)
2004
2005 template = template or self.template
2006
2007 return (
2008 template % {"expression": expr_sql, "window": " ".join(window_sql).strip()},
2009 (*params, *window_params),
2010 )
2011
2012 def as_sqlite(self, compiler, connection):
2013 if isinstance(self.output_field, fields.DecimalField):
2014 # Casting to numeric must be outside of the window expression.
2015 copy = self.copy()
2016 source_expressions = copy.get_source_expressions()
2017 source_expressions[0].output_field = fields.FloatField()
2018 copy.set_source_expressions(source_expressions)
2019 return super(Window, copy).as_sqlite(compiler, connection)
2020 return self.as_sql(compiler, connection)
2021
2022 def __str__(self):
2023 return "{} OVER ({}{}{})".format(
2024 str(self.source_expression),
2025 "PARTITION BY " + str(self.partition_by) if self.partition_by else "",
2026 str(self.order_by or ""),
2027 str(self.frame or ""),
2028 )
2029
2030 def __repr__(self):
2031 return "<%s: %s>" % (self.__class__.__name__, self)
2032
2033 def get_group_by_cols(self):
2034 group_by_cols = []
2035 if self.partition_by:
2036 group_by_cols.extend(self.partition_by.get_group_by_cols())
2037 if self.order_by is not None:
2038 group_by_cols.extend(self.order_by.get_group_by_cols())
2039 return group_by_cols
2040
2041
2042 class WindowFrameExclusion(Enum):
2043 CURRENT_ROW = "CURRENT ROW"
2044 GROUP = "GROUP"
2045 TIES = "TIES"
2046 NO_OTHERS = "NO OTHERS"
2047
2048 def __repr__(self):
2049 return f"{self.__class__.__qualname__}.{self._name_}"
2050
2051
2052 class WindowFrame(Expression):
2053 """
2054 Model the frame clause in window expressions. There are two types of frame
2055 clauses which are subclasses, however, all processing and validation (by no
2056 means intended to be complete) is done here. Thus, providing an end for a
2057 frame is optional (the default is UNBOUNDED FOLLOWING, which is the last
2058 row in the frame).
2059 """
2060
2061 template = "%(frame_type)s BETWEEN %(start)s AND %(end)s%(exclude)s"
2062
2063 def __init__(self, start=None, end=None, exclusion=None):
2064 self.start = Value(start)
2065 self.end = Value(end)
2066 if not isinstance(exclusion, (NoneType, WindowFrameExclusion)):
2067 raise TypeError(
2068 f"{self.__class__.__qualname__}.exclusion must be a "
2069 "WindowFrameExclusion instance."
2070 )
2071 self.exclusion = exclusion
2072
2073 def set_source_expressions(self, exprs):
2074 self.start, self.end = exprs
2075
2076 def get_source_expressions(self):
2077 return [self.start, self.end]
2078
2079 def get_exclusion(self):
2080 if self.exclusion is None:
2081 return ""
2082 return f" EXCLUDE {self.exclusion.value}"
2083
2084 def as_sql(self, compiler, connection):
2085 connection.ops.check_expression_support(self)
2086 start, end = self.window_frame_start_end(
2087 connection, self.start.value, self.end.value
2088 )
2089 if self.exclusion and not connection.features.supports_frame_exclusion:
2090 raise NotSupportedError(
2091 "This backend does not support window frame exclusions."
2092 )
2093 return (
2094 self.template
2095 % {
2096 "frame_type": self.frame_type,
2097 "start": start,
2098 "end": end,
2099 "exclude": self.get_exclusion(),
2100 },
2101 [],
2102 )
2103
2104 def __repr__(self):
2105 return "<%s: %s>" % (self.__class__.__name__, self)
2106
2107 def get_group_by_cols(self):
2108 return []
2109
2110 def __str__(self):
2111 if self.start.value is not None and self.start.value < 0:
2112 start = "%d %s" % (abs(self.start.value), connection.ops.PRECEDING)
2113 elif self.start.value is not None and self.start.value == 0:
2114 start = connection.ops.CURRENT_ROW
2115 elif self.start.value is not None and self.start.value > 0:
2116 start = "%d %s" % (self.start.value, connection.ops.FOLLOWING)
2117 else:
2118 start = connection.ops.UNBOUNDED_PRECEDING
2119
2120 if self.end.value is not None and self.end.value > 0:
2121 end = "%d %s" % (self.end.value, connection.ops.FOLLOWING)
2122 elif self.end.value is not None and self.end.value == 0:
2123 end = connection.ops.CURRENT_ROW
2124 elif self.end.value is not None and self.end.value < 0:
2125 end = "%d %s" % (abs(self.end.value), connection.ops.PRECEDING)
2126 else:
2127 end = connection.ops.UNBOUNDED_FOLLOWING
2128 return self.template % {
2129 "frame_type": self.frame_type,
2130 "start": start,
2131 "end": end,
2132 "exclude": self.get_exclusion(),
2133 }
2134
2135 def window_frame_start_end(self, connection, start, end):
2136 raise NotImplementedError("Subclasses must implement window_frame_start_end().")
2137
2138
2139 class RowRange(WindowFrame):
2140 frame_type = "ROWS"
2141
2142 def window_frame_start_end(self, connection, start, end):
2143 return connection.ops.window_frame_rows_start_end(start, end)
2144
2145
2146 class ValueRange(WindowFrame):
2147 frame_type = "RANGE"
2148
2149 def window_frame_start_end(self, connection, start, end):
2150 return connection.ops.window_frame_range_start_end(start, end)