Unique together involving multiple foreign keys & a many to many field

Question

Our business' pricing is dependent on multiple parameters, and now we want to introduce another possible M2M parameter to the existing setup in Django.

For this, we have an existing table for pricing, which has a unique_together constraint on all fields except the price_field. Apologies for the generic / letter-based naming in the example.

class PricingTable(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    price = MoneyField()
    b = ArrayField(models.CharField(choices=CHOICES))
    c = models.ForeignKey(C, on_delete=models.CASCADE)

    class Meta:
        ordering = ("a",)
        unique_together = ("a", "b", "c")

    def validate_b(self):
        # b can't be empty
        if not len(self.b) >= 1:
            raise ValueError
        # each element in b needs to be unique
        if not len(self.b) == len(set(self.b)):
            raise ValueError
        # each element in b needs to be unique together with a & c
        for i in self.b:
            query = PricingTable.objects.filter(
                a=self.a, c=self.c, b__contains=[i]
            ).exclude(pk=self.pk)
            if query.count() > 0:
                raise ValueError

    def save(self, *args, **kwargs):
        self.validate_b()
        return super().save(*args, **kwargs)

I want to introduce another parameter to this table which has to be unique_together with the non-price params (a, b & c).

d = models.ManyToManyField("x.D", related_name="+")

Each element in list b needs to be unique together with a & c.

The issue with the above is that the validate_b function has to be upgraded into a possibly complex function with heavy DB queries. Along with that Django does not provide a straight forward way to ensure many to many field's unique togetherness.

So, is there a different approach I should probably try? A through table perhaps? But then, what all fields should I include in the through table? All the non-price fields? Or should I stop dreaming of having a many to many field for d and go ahead with a simple foreignkey approach and have a unique_together on all those which would be straight forward?

Versions:

• Django 2.2
• postgres 11

If needed, I can convert the existing ArrayField into a simple CharField which would mean more DB rows which is somewhat okay as long as I get to put all the unique constraits into the Database rather than validating on save each time.

Answer 1

Since only few d entities had to have a corresponding price (rest will continue to have a generic common price), I ended up with the following structure.

class PricingTable(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    price = MoneyField()
    b = ArrayField(models.CharField(choices=CHOICES))
    c = models.ForeignKey(C, on_delete=models.CASCADE)
    d = models.ForeignKey("x.D", on_delete=models.CASCADE, blank=True, null=True)

    class Meta:
        ordering = ("a",)
        unique_together = ("a", "b", "c", "d")

    def validate_b(self):
        # b can't be empty
        if not len(self.b) >= 1:
            raise ValueError
        # each element in b needs to be unique
        if not len(self.b) == len(set(self.b)):
            raise ValueError
        # each element in b needs to be unique together with a, c & d
        query = PricingTable.objects.filter(
            a=self.a, c=self.c, d=self.d, b__overlap=self.b
        ).exclude(pk=self.pk)
        if query.count() > 0:
            raise ValueError

    def save(self, *args, **kwargs):
        self.validate_b()
        return super().save(*args, **kwargs)


class DBasedPricing(models.Model):
    """
    Lookup table that tells (row exists) if we have D based pricing coupled with param A
    If we do, query PricingTable.d=d, else PricingTable.d=None for correct pricing
    """
    d = models.ForeignKey("x.D", on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE)

    class Meta:
        unique_together = ("d", "a")

This forces me to first do a lookup based on d parameter, to check if pricing would be D based or not

d_id = None
if DBasedPricing.objects.filter(d_id=input_param.d, a_id=a.id).exists():
    d_id = input_param.d

Which then adds another parameter to my usual queries

price_obj = PricingTable.objects.filter(...usual query..., d_id=d_id)

Overall, at the cost of a single simple indexed lookup, I save on rows & of course complex DB structuring. Also, I ended up not having to re-enter all the existing pricing!

Answer 2

You should try overlap to replace

# each element in b needs to be unique together with a & c
for i in self.b:
    query = PricingTable.objects.filter(
        a=self.a, c=self.c, b__contains=[i]
    ).exclude(pk=self.pk)
    if query.count() > 0:
        raise ValueError

by

query = PricingTable.objects.filter(
    a=self.a, c=self.c, b__overlap=self.b
).exclude(pk=self.pk)
if query.count() > 0:
            raise ValueError

Note: I did not verify the query generated and the performances

Answer 3

Premises

In Sql and so in Django ORM you can't set a unique constraints on a many to many fields because it involves two different tables.

SQL Solution:

You can try to reproduce this solution on django.

But in order to do this you have to manually create the tab_constr and insert the trigger logic inside the save method or with the signals

Django solution

I do not recommend you to follow that solution because it is difficult to reproduce in django, in fact you have to manually reproduce the m2m reference with two external key and one extra table.

Simply put your check on on_save method there is no other way.

P.S.

Don't use the override of save method to add check on your object because this method is not called if you change a QuerySet of objects. Instead use the signal like this:

@receiver(post_save, sender=Program)
def on_save_pricing_table(sender, instance, created, **kwargs):
    if not instance.value = 10:
        raise ValueError