How to express `foo = (bar < quox)` as a constraint

[Peter Law]

Hi,

Thanks for adding support for check constraints in Django 2.2, it's
great to be able to move constraints into the model definitions.

I've been trying to work out how to express a constraint which
validates that the value of one field expresses a relation between two
other fields, but can't find a nice way to do so.

I've read through the docs and also found
https://groups.google.com/d/topic/django-users/6Olh5V1b7Us/discussion,
but haven't found a concise spelling.

I've got a model like:

```
class Item(Model):
price = DecimalField()
full_price = DecimalField()
is_on_sale = BooleanField()
```

I'd like to be able to express neatly that the `is_on_sale` boolean be
true only when `price < full_price`.
In Postgres I can express this as:

```
ALTER TABLE item
ADD CONSTRAINT is_on_sale_check
CHECK (is_on_sale = (price < full_price))
```

However in Django I can't find a way to express this directly.

I did find a long spelling which essentially checks the True case and
the False case explicitly and then ORs them together, however it's
several lines of `models.Q` combinations and not at all clear about
what the code is trying to achieve.

Is there a concise way to do this sort of constraint? If not, would it
be possible for Django to add support for it?

Thanks,
Peter

[Stephen J. Butler]

Frankly, if is_on_sale has such a tight constraint I wouldn't have it as its own column. Why not just make it an annotated field with an F expression?

https://docs.djangoproject.com/en/3.0/ref/models/expressions/#using-f-with-annotations

Item.objects.annotate(is_on_sale=(F('price') < F('full_price')))

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAEMtty7rsf2qk_0qXnzqZdA89e6VRCqF%2B4nQSHgxVCE4XfKAnw%40mail.gmail.com.

[DANIEL URBANO DE LA RUA]

take a look at this https://docs.djangoproject.com/en/3.0/topics/signals/

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAD4ANxWtpqnDr_8WrQiskH7wXxBPyJ3sTWa%2BAsK1592SXWTfpw%40mail.gmail.com.

[DANIEL URBANO DE LA RUA]

it is goin to be better and less cost for the db 

[DANIEL URBANO DE LA RUA]

you can do few thing at the same time in a model before save or whenever you want

[DANIEL URBANO DE LA RUA]

sorry i diin't read to Stephen his trik is even better hahahaha but with sigñal yo ca do other thing

[Simon Charette]

Did you try

class Item(Model):
    price = DecimalField()
    full_price = DecimalField()
    is_on_sale = BooleanField()

    class Meta:

        constraints = [

            CheckConstraint(check=Q(is_on_sale=Q(price__lt=F('full_price'))))

        ]

I haven't tried it myself but I would expect it to work on Django 3.0.

Cheers,

Simon

[Peter Law]

Hi Stephen,

Thanks for your response.

I agree that using an annotation might be an alternative way to solve this, however for various reasons that's not suitable for my current use case. I was also hoping for a general solution which would work for cases where the expression is more complicated (and thus where an annotation might be less desirable).

I'm using Django 2.2 LTS (though I can't see anything to suggest this has changed in 3.0) but your suggested annotation unfortunately doesn't work -- I get `TypeError: unorderable types: F() < F()`.

Thanks,

Peter

To unsubscribe from this group and stop receiving emails from it, send an email to django...@googlegroups.com.

[Peter Law]

Hi Simon,

Thanks for your response.

I did try that, however unfortunately I get an error when running the migration:

django.core.exceptions.ValidationError: ["'(AND: <django.db.models.lookups.LessThan object at 0x7fcc49998a20>)' value must be either True or False."]

I'm using Django 2.2 LTS though testing this in 3.0 unfortunately errors in the same way.

I think the issue with this spelling is that a `models.Q` isn't expecting to be passed another `models.Q` as a value. I think ideally it would want an expression there, which a `models.F` is closest to, however using a `models.F` relation there also doesn't work (see my response to Stephen's suggestion).

Thanks,

Peter

[Simon Charette]

I see, I think you'll need to wrap the inner Q in an ExpressionWrapper[0]

That would be

CheckConstraint(check=Q(is_on_sale=ExpressionWrapper(Q(price__lt=F('full_price')), output_field=models.BooleanField())))

That involves a lot of boilerplate though and it ought to work without all of it so I'd submit an optimization ticket

to allow direct usage of Q objects for boolean field lookup right hand sides

Cheers,

Simon

https://docs.djangoproject.com/en/3.0/ref/models/expressions/#django.db.models.ExpressionWrapper

[Integr@te System]

Hi All,

Let try sth like sample:

a = True if b <= c else False

--

You received this message because you are subscribed to the Google Groups "Django users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5d9488a8-7e6d-4a96-b009-fd94feb9df86%40googlegroups.com.

[Peter Law]

Hi Simon,

Thanks! Using an ExpressionWrapper does work for this :)

I agree it might be nice to have a bit less boilerplate here. What I'd
first tried was actually:

```
CheckConstraint(check=Q(
is_on_sale=F('price') < F('full_price'),
))
```

It would be great if that could be made to work - I think that a
spelling like that would be even better than nested Q objects (even
without ExpressionWrapper).

Many thanks,

Peter

> --
> You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/mkTg344-dYk/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.