Q object for CheckConstraint

Question

I'm trying to write a Q object that says

Either one of these 4 fields is not null, or this field is not true

I have a normalized table with 4 category of prices, and an available flag

price_A
price_B
price_C
price_D
available_on_the_store

I want at least one of these prices to be populated before it is possible to mark it as available_on_the_store on a CheckConstraint with a Q object

I can easily do this with a huge Q chain or can easily write it in .clean() and force it in the python side, yet I want it to be in the database level; so please answer accordingly.

Answer 1

You can write this as:

  Q(available_on_the_store=False) |
  Q(price_A__isnull=False) |
  Q(price_B__isnull=False) |
  Q(price_C__isnull=False) |
  Q(price_D__isnull=False)

or we can write it shorter with:

Q(
    ('available_on_the_store', False),
    ('price_A__isnull', False),
    ('price_B__isnull', False),
    ('price_C__isnull', False),
    ('price_D__isnull', False),
    _connector=Q.OR
)

You thus can make a model that looks like:

class MyModel(models.Model):
    # …

    class Meta:
        constraints = [
            models.CheckConstraint(
                Q(
                    ('available_on_the_store', False),
                    ('price_A__isnull', False),
                    ('price_B__isnull', False),
                    ('price_C__isnull', False),
                    ('price_D__isnull', False),
                    _connector=Q.OR
                ),
                name='one_price_not_null_if_avaiable'
            )
        ]