Validate overlapping dates in laravel while creating

wherebetween laravel
overlapping date ranges meaning
sql overlapping date ranges
linq overlapping date ranges
datetime overlap python
oracle sql overlapping date ranges
date range overlap c#
date overlap logic in java

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.

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);
                                                            })

                                                               })
                                                               });

Laravel Eloquent Query to Check Overlapping Start and End , Don't fully understand your question but will attempt to answer it anyway. For date overlaps I see four scenarios where an overlap would occur. Consider A as  For example, you may want to use the Laravel encrypter to encrypt a value while it is stored in the database, and then automatically decrypt the attribute when you access it on an Eloquent model. In addition to custom accessors and mutators, Eloquent can also automatically cast date fields to Carbon instances or even cast text fields to JSON .

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

Eloquent, Hi all, Edited: made my question a bit clearer I have events with start and end date fields. These will be displayed in a calendar month view, a marke Instead of manually specifying the value of each column when you create this test data, Laravel allows you to define a default set of attributes for each of your Eloquent models using model factories.

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

Laravel avoid overlapping dates, I have validation that checks that the end date can not be before the start: In laravel what I do is to create a scope in the model. Laravel's scheduled tasks will not run when Laravel is in maintenance mode, since we don't want your tasks to  [ Natty] php How to validate time in laravel By: Chathuranga Tennakoon 1.5; [ Natty ] reactjs in react can't get new value after immediately setState By: Alan 1.5 ;

MySQL: Validate for date range within rate range (laravel optional), I am using Laravel PHP but raw SQL will be perfect as well. Basically I want a solid validation to avoid overlapping date ranges. Working with Dates and Times in PHP and MySQL, When working in any Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. The Laravel Schema facade provides database agnostic support for creating and manipulating tables across all of Laravel's supported database systems. Generating Migrations. To create a migration, use the make:migration Artisan command: php artisan make:migration create_users_table. The new migration will be placed in your database/migrations

Determining if Two Date Ranges Overlap, Jeremy Brown explores how to tell if two date ranges overlap. We had some complicated ExecuteSQL-created, Virtual List reports that gathered Side note: This function works very well when I can compare dates set in the  Best How To : Try wrapping your code in a DO statement:. DO LANGUAGE plpgsql $$ DECLARE BEGIN INSERT INTO articulo VALUES (33, 'Mesa 33', 1000.45463, 50.2345, 200.23459, 20, 'Conjunto'); INSERT INTO articulo VALUES (34, 'Mesa 34', 300.4500, 15.2379 , 1.2379 , 5, 'kid 4'); SELECT * FROM articulo; INSERT INTO cliente (id_clie, nom_clie, rfc_clie, tel_clie, dir_clie, suspendido) VALUES (44,'Rosa

Test 2 time ranges to see if they overlap, As s3rius pointed out, you don't need the $timeCheck variable. You can just use return true; or return false; . To parse a time use the DateTime class. You can  javascript java c# python android php jquery c++ html ios css sql mysql.net c r asp.net ruby-on-rails objective-c arrays node.js sql-server iphone regex ruby angularjs json swift django linux asp.net-mvc xml wpf angular spring string ajax python-3.x git excel windows xcode multithreading pandas database reactjs bash scala algorithm eclipse

Comments
  • Rule::unique is for a database. So, you're storing date range in a database?
  • yes, I am adding the Rule::unique to creationRules in laravel nova.
  • Rule::unique is used in laravel-nova same as it is used in laravel.
  • Rule::unique('TABLE_NAME') this is correct approach. I don't understand from which table you need to validate a date.
  • users is the table name.
  • Thanks for the answer, but this doesn't work, it takes the dates from between range as well.
  • Hope i have given you an idea it will require some tweaks tho!