I want to validate that there is no duplicate row for any overlapping dates in laravel. I have 2 columns in database with DATE datatype, valid_from_date
and valid_until_date
.
Suppose valid_from_date = 2019-01-01 and valid_until_date = 2019-01-31, I don't want to let user enter any dates between these days.
Invalid Case examples: Should not be accepted
valid_from_date = 2018-12-01 and valid_until_date = 2019-01-02
valid_from_date = 2019-01-04 and valid_until_date = 2019-01-29
valid_from_date = 2019-01-15 and valid_until_date = 2019-02-05
Valid Case Examples: Can be accepted
valid_from_date = 2018-12-02 and valid_until_date = 2018-12-31
valid_from_date = 2019-02-05 and valid_until_date = 2019-02-25
Precisely, any input date that falls between these 2 dates should not be accepted as valid_from_date
or valid_until_date
.
I tried doing this with Rule::unique in following manner, also tried many other query ways.
Rule::unique('users')->where(function ($query) use ($request) {
return $query->where('user_id', $request->user_id)
->where(function($q1) use ($request) {
$q1->where(function($q2) use ($request) {
$q2->where('valid_from_date', '<=', $request->valid_from_date)
->where('valid_until_date', '>=', $request->valid_from_date);
})
->orWhere(function($q2) use ($request) {
$q2->where('valid_from_date', '<=', $request->valid_until_date)
->where('valid_until_date', '>=', $request->valid_until_date);
});
});
})
Thanks in advance for your time and help.
I achieved this using Rule::notIn($array), this $array having all the dates between valid_from_date and valid_until_date.