Validate overlapping dates in laravel while creating

Question

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.

Rule::unique is for a database. So, you're storing date range in a database? — Rutvij Kothari, Jan 15 '19 at 19:46
yes, I am adding the Rule::unique to creationRules in laravel nova. — Shraddha Banerjee, Jan 15 '19 at 19:48
Rule::unique is used in laravel-nova same as it is used in laravel. — Shraddha Banerjee, Jan 15 '19 at 19:55
Rule::unique('TABLE_NAME') this is correct approach. I don't understand from which table you need to validate a date. — Rutvij Kothari, Jan 15 '19 at 20:02

phpdroid · Answer 1 · 2019-01-16T08:22:39.663

Logic used is
case 1:
input(valid_until_date) lies between database(valid_from_date,valid_until_date) or input(valid_from_date) lies between database(valid_from_date,valid_until_date)
case 2:
reversed or condition in second case
input(valid_from_date) lies between database(valid_from_date,valid_until_date) or input(valid_until_date) lies between database(valid_from_date,valid_until_date)

Rule::unique('users')->where(function ($query) use ($request) {
                                     return $query->where('user_id', $request->user_id)
                                     ->where(function ($query1) use ($request) {
                                                 $query1->where('valid_from_date', '>=', $request->valid_from_date)
                                                        ->where('valid_until_date', '<=', $request->valid_from_date)
                                                        ->orWhere(function ($query2) use ($request) {
                                                 $query2->where('valid_from_date', '>=', $request->valid_until_date)
                                                        ->where('valid_until_date', '<=', $request->valid_until_date);
                                                            })

                                                               })
                                    ->where(function ($query3) {
                                                 $query3->orWhere('valid_from_date', '>=', $request->valid_from_date)
                                                        ->where('valid_until_date', '<=', $request->valid_from_date)
                                                        ->where(function ($query4) use ($request) {
                                                 $query4->where('valid_from_date', '>=', $request->valid_until_date)
                                                        ->where('valid_until_date', '<=', $request->valid_until_date);
                                                            })

                                                               })
                                                               });

Thanks for the answer, but this doesn't work, it takes the dates from between range as well. — Shraddha Banerjee, Jan 16 '19 at 09:42
Hope i have given you an idea it will require some tweaks tho! — phpdroid, Jan 16 '19 at 09:45

score 0 · Accepted Answer · answered Jan 28 '19 at 09:14

0

I achieved this using Rule::notIn($array), this $array having all the dates between valid_from_date and valid_until_date.

answered Jan 28 '19 at 09:14

Shraddha Banerjee

197
2
14

score 0 · Answer 3 · edited Sep 08 '19 at 11:05

0

This works for me:

Reservation::whereBetween('date_start', [Carbon::parse($this->date_start), Carbon::parse($this->date_end)])->count()

Note: DB field must be date or timestamp

edited Sep 08 '19 at 11:05

noam621

2,766
1
16
26

answered Sep 08 '19 at 08:46

James Jomuad

50
3

Validate overlapping dates in laravel while creating

3 Answers3