4👍
EDITED:
How to make Django validate overlapping reservations?
It is possible to add special method for a model validate_unique
:
from django.db import models
from django.core.validators import ValidationError
from django.forms.forms import NON_FIELD_ERRORS
class Dish(models.Model):
name = models.CharField('Dish name', max_length=200)
class Menu(models.Model):
user_id = models.IntegerField()
dish = models.ForeignKey(Dish)
order_start_time = models.DateTimeField()
order_end_time = models.DateTimeField()
def validate_unique(self, *args, **kwargs):
# call inherited unique validators
super().validate_unique(*args, **kwargs) # or super(Menu, self) for Python2.7
# query if DB already has object with same dish
# and overlapping reservation
# [order_start_time, order_end_time]
qs = self.__class__._default_manager.filter(
order_start_time__lte=self.order_end_time,
order_end_time__gte=self.order_start_time,
dish=self.dish,
)
# and this object is not the same we are working with
if not self._state.adding and self.pk is not None:
qs = qs.exclude(pk=self.pk)
if qs.exists():
raise ValidationError({
NON_FIELD_ERRORS: ['Overlapping order dates for dish'],
})
Lets try it in console:
from core.models import *
m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 10:00', order_end_time='2016-03-22 15:00')
m.validate_unique()
# no output here - all is ok
m.save()
print(m.id)
8
# lets add duplicate
m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 12:00', order_end_time='2016-03-22 13:00')
m.validate_unique()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/el/tmp/hypothesis_test/menu/core/models.py", line 29, in validate_unique
NON_FIELD_ERRORS: ['Overlapping order dates for dish'],
django.core.exceptions.ValidationError: {'__all__': ['Overlapping order dates for dish']}
# excellent! dup is found!
# But! Django helps you find dups but allows you to add them to db if you want it!
# It's responsibility of your application not to add duplicates.
m.save()
print(m.id)
9
How to be sure noboby can add duplicate?
In this case you need to make a CONSTRAINT on database-level.
In PostgreSQL console:
CREATE EXTENSION btree_gist;
-- our table:
SELECT * FROM core_menu;
id | user_id | order_start_time | order_end_time | dish_id
----+---------+------------------------+------------------------+---------
8 | 1 | 2016-03-22 13:00:00+03 | 2016-03-22 18:00:00+03 | 1
9 | 1 | 2016-03-22 15:00:00+03 | 2016-03-22 16:00:00+03 | 1
DELETE FROM core_menu WHERE id=9; -- we should remove dups before adding unique constraint
ALTER TABLE core_menu
ADD CONSTRAINT core_menu_exclude_dish_same_tstzrange_constr
EXCLUDE USING gist (dish_id WITH =, tstzrange(order_start_time, order_end_time) WITH &&);
Now lets create the duplicate object and add it to db:
m=Menu(user_id=1, dish_id=1, order_start_time='2016-03-22 13:00', order_end_time='2016-03-22 14:00')
m.save()
Traceback (most recent call last):
File "/Users/el/tmp/hypothesis_test/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
psycopg2.IntegrityError: ОШИБКА: конфликтующее значение ключа нарушает ограничение-исключение "core_menu_exclude_dish_same_tstzrange_constr"
DETAIL: Key (dish_id, tstzrange(order_start_time, order_end_time))=(1, ["2016-03-22 13:00:00+00","2016-03-22 14:00:00+00")) conflicts with existing key (dish_id, tstzrange(order_start_time, order_end_time))=(1, ["2016-03-22 10:00:00+00","2016-03-22 15:00:00+00")).
Excellent!
Now data is validated at program and db levels.
Source:stackexchange.com