Versions:
django==5.1.4
python==3.12.8
postgres v15.4
How to ensure that there is no overlap in the class schedules?
I am modeling an educational system CRM. All the models are working fine, and I am aiming to add constraints for my GroupSchedule model
class GroupSchedule(models.Model):
DAYS = [
(1, _("Monday")),
(2, _("Tuesday")),
(3, _("Wednesday")),
(4, _("Thursday")),
(5, _("Friday")),
(6, _("Saturday")),
(7, _("Sunday")),
]
id = ShortUUIDField(length=16, max_length=40, primary_key=True, editable=False)
group = models.ForeignKey(Group, related_name="schedules", on_delete=models.CASCADE)
teacher = models.ForeignKey(
Employee,
related_name="schedules",
on_delete=models.PROTECT,
limit_choices_to=Q(role="TE") | Q(role="OW"),
)
room = models.ForeignKey(
Room, related_name="schedules", on_delete=models.SET_NULL, null=True
)
day = models.PositiveSmallIntegerField(choices=DAYS)
start_time = models.TimeField()
end_time = models.TimeField()
start_minutes = models.PositiveSmallIntegerField(editable=False)
end_minutes = models.PositiveSmallIntegerField(editable=False)
class Meta:
ordering = ["day", "start_time"]
constraints = [
CheckConstraint(
check=Q(start_time__lt=F("end_time")),
name="start_time_before_end_time",
violation_error_message="End time must be greater than start time",
),
]
def __str__(self):
return (
f"{self.group}, {self.get_day_display()} {self.start_time}:{self.end_time}"
)
def save(self, *args, **kwargs):
self.start_minutes = self.start_time.hour * 60 + self.start_time.minute
self.end_minutes = self.end_time.hour * 60 + self.end_time.minute
super().save(*args, **kwargs)
I want to create a database-level constraint that rejects overlapping schedules for teacher and/or group. A group or teacher cannot be present in two schedules at the same time. With the help of documentation and GPT, I installed the BtreeGistExtension()
in my migrations and added this constraint for group:
ExclusionConstraint(
name="exclude_group_overlap",
expressions=[
(("group_id", "gist_btree_ops"), "="),
(("day", "gist_int2_ops"), "="),
(
(
Range("start_minutes", "end_minutes", bounds="[]"),
"gist_int4range_ops",
),
"&&",
),
],
)
However, no matter what I do, it is not working and always returning the same message.
django.db.utils.ProgrammingError: data type unknown has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
It is the first time I am going this deep into ORM. I am now lost: is this the way to ensure no overlaps or am I fighting the Django? Am I better off (trying to) write raw SQL or just do a form-level validation (I am going with a monolithic approach with Django templates and HTMX)?
Thanks for your time.