1

I have two models: Account and Transfer. The model Account has the attribute currency (EUR, USD, JPY, etc.). The model Transfer has two attributes account_from and account_to. I want to add a constraint that checks that account_from uses the same currency as account_to.

I was thinking of adding such a constraint on Transfer model:

class Meta:
    constraints = [
            CheckConstraint(check=Q(account_from__currency=F('account_to__currency')), name='same_currency'),
        ]

But that doesn't work because of error

django.core.exceptions.FieldError: Joined field references are not permitted in this query

How do I do that ? Without relying on SQL. I know how to do that in SQL but I want to use the ORM. Or is it impossible to do that with Django ORM ?

Here are the two models (simplified to avoid noise):

class Tranfer(AuditedModel):
    """
    The action of moving money from one account to another
    """
    account_from = models.ForeignKey(Account, related_name="outgoing", on_delete=models.CASCADE)
    account_to = models.ForeignKey(Account, related_name="incoming", on_delete=models.CASCADE)

class Account(AuditedModel):
    """
    Recipient for money
    """
    currency = models.CharField('currency', max_length=3, choices=(('EUR', 'Euro'), ('USD', 'American Dollars'), ('JPY', 'Japan Yen')))
    
    class Meta:
        constraints = [
CheckConstraint(check=Q(account_from__currency=F('account_to__currency')), name='same_currency'),
        ]
Adrien H
  • 643
  • 6
  • 21
  • 1
    You should add that constrain to Currency Model, not in Account model. – ruddra Jun 24 '20 at 08:52
  • doc: support for `boolean Expression` is added in version 3.1 https://code.djangoproject.com/ticket/30484 – minglyu Jun 24 '20 at 09:24
  • @minglyu While this is interesting, it doesn't address the issue, or did I miss something ? After digging, it seems that it's not possible at all using CheckConstraint as it's not possible in SQL check either. The solution I found for the moment is using the pre_save where I can browse relations to my liking. – Adrien H Jun 24 '20 at 16:11
  • 1
    Related: https://stackoverflow.com/questions/60254433/error-using-checkconstraint-in-model-meta-along-with-django-genericforeignkey – SergiyKolesnikov Oct 19 '20 at 22:00

1 Answers1

0

Follow the following steps for the solution of your problem.

  1. The constrain section should be in the Transfer Model not in Account model.

  2. Check constrain for any two relational model must be avoided.

  3. The alternative for CheckConstraint() in the Constraint array of Meta section use the function clean for validation rather than constraint.

from django.db import models
from django.core.exceptions import ValidationError


class Account(models.AuditedModel):
    """
    Recipient for money
    """
    currency = models.CharField('currency', max_length=3, choices=(
        ('EUR', 'Euro'), 
        ('USD', 'American Dollars'), 
        ('JPY', 'Japan Yen')
    ))
        
            

class Transfer(models.AuditedModel):
    """
    The action of moving money from one account to another
    """
    account_from = models.ForeignKey(Account, related_name="outgoing", on_delete=models.CASCADE)
    account_to = models.ForeignKey(Account, related_name="incoming", on_delete=models.CASCADE)

    
    def clean(self):
        if self.account_from.currency != self.account_to.currency:
            raise ValidationError("Same currency required for transaction.")

        return super().clean()
Alex Roy
  • 1
  • 1