Django Postgres Exclusion Constraint with ManyToManyField

Question

I would like to use Django Exclusion Constraint with ManyToManyField. Unfortunatelly, so far my efforts were futile. This is my appointment model:

from django.contrib.postgres.constraints import ExclusionConstraint from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators

class Appointment:
    patients = models.ManyToManyField(Patient, related_name='appointments' , blank=True )     
    datetimerange = DateTimeRangeField(null=False, blank = False )
    doctor = models.ForeignKey(Doctor, related_name='doctor_appointments') 

    class Meta: 
        constraints = [ 
            ExclusionConstraint(
                name='unique_doctor',
                expressions=[
                    ('datetimerange', RangeOperators.OVERLAPS),
                    ('doctor ', RangeOperators.EQUAL),

                ], 
            ), 
            ExclusionConstraint(
                name='unique_patients',
                expressions=[
                    ('datetimerange', RangeOperators.OVERLAPS),
                    ('patients', RangeOperators.CONTAINED_BY)
                
                ],
                condition= Q(is_archived=False) & Q(is_cancelled=False)                
        ) 
        ]

Unfortunatelly this doesn't work. The first constraint that references the Doctor works perfectly, but the second one gives me this error during migration:

return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column "patient_id" named in key does not exist

    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "patient_id" named in key does not exist

This has been bogging me for quite some time. Any help appreciated.

Answer 1

The problem here is that the constraint must be made on the Appointment model's table, however because patients is a M2M field, there is no column as the error message says. The relationship is based on an intermediate table which holds the foreign key.

The upshot is that you can't do exactly what you want to do here.

However, you could build this validation into the model's clean() method.