[Django] #30484: Feature request: Using RawSQL with CheckConstraint
Django
-------------------------------------+-------------------------------------
Reporter: David | Owner: nobody
Sanders |
Type: New | Status: new
feature |
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I'm wondering about the possibility of updating CheckConstraint to handle
RawSQL expressions, in a similar way to annotate().
To illustrate the limitations of using Q objects and usefulness of RawSQL
expressions consider the following example:
{{{
class Foo(models.Model):
…
total = models.IntegerField(blank=True, null=True) # a calculated
field
total_last_updated = models.DateTimeField(blank=True, null=True) #
set when total is calculated
}}}
A check constraint would be useful here to enforce that
`total_last_updated` is populated/cleared when total is populated/cleared
and would be something like an XOR check with the following SQL
expression: `(total IS NULL) = (total_last_updated IS NULL)`. This
doesn't appear to be possible with Q objects.
The workaround in this case is to create the check constraint manually
with a RunSQL migration.
--
Ticket URL: <https://code.djangoproject.com/ticket/30484>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
It looks like this could be expressed as
`Q(total__isnull=RawSQL('(total_last_updated IS NULL)')` but I haven't
tested it.
When #25367 lands it should even work without `RawSQL`:
`Q(total__isnull=Q(total_last_updated__isnull=True))`.
In the mean time if the above doesn't work I'd suggest you register
[https://docs.djangoproject.com/en/2.2/howto/custom-lookups/ a custom
Lookup] to express this filtering.
{{{#!python
from django.db.models.fields import Field
from django.db.models import Lookup
class IsNullEq(Lookup):
lookup_name = 'isnulleq'
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return '(%s IS NULL) = (%s IS NULL)' % (lhs, rhs), params
Field.register_lookup(IsNullEq)
}}}
And use it for your constraint's condition
`Q(total__isnulleq=F('total_last_updated'))`.
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Gavin Wahl):
The other ticket is fixed, but it is still not possible to use expressions
in check constraints. The proposed workaround with lookups doesn't work
because my check constraint uses a function that takes multiple arguments
(I need to be able to do `CHECK num_nonnulls(a, b, c) = 1`).
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
This came out [https://groups.google.com/forum/#!topic/django-
users/cC9QcS0kh1Q on the user mailing list today].
Wouldn't `CheckConstraint(Func('a,' 'b', 'c', function='num_nonnulls'))`
work once support for expression is added in #30916?
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: nobody
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* version: 2.2 => master
* has_patch: 0 => 1
* resolution: wontfix =>
* stage: Unreviewed => Accepted
Comment:
Re-opening since this isn't easily possible.
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:5>
Django
-------------------------------------+-------------------------------------
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Simon Charette
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: Simon
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: Simon
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: Simon
| Charette
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"e9a0e1d4f6ecfc4227acff74e1f56f288a0b30aa" e9a0e1d4]:
{{{
#!CommitTicketReference repository=""
revision="e9a0e1d4f6ecfc4227acff74e1f56f288a0b30aa"
Fixed #30484 -- Added conditional expressions support to CheckConstraint.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30484#comment:9>