[Django] #30484: Feature request: Using RawSQL with CheckConstraint

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #30484: Feature request: Using RawSQL with CheckConstraint

Django
#30484: Feature request: Using RawSQL with CheckConstraint
-------------------------------------+-------------------------------------
               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.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/051.894f84b4927e6b37c94e0e63271bbfe2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30484: Using RawSQL with CheckConstraint (was: Feature request: Using RawSQL with CheckConstraint)

Django
#30484: Using RawSQL with CheckConstraint
-------------------------------------+-------------------------------------
     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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.78e3464c3191f4da142d09bbd00615ac%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30484: Using RawSQL with CheckConstraint

Django
In reply to this post by Django
#30484: Using RawSQL with CheckConstraint
-------------------------------------+-------------------------------------
     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
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * 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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.7de97acf8ce492c910a05c9028ccc74e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.