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!
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
- How to choose the value and label from Django ModelChoiceField queryset
- How do I remove the square brackets at the end of a JS variable name during AJAX calls?
3👍
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