Laravel - PHP/MySQL date range filter against date range in database

Question

I am working on an application using Laravel and I am trying to filter records from the database. Here is the criteria:

In the database I have 2 date columns [excluded_period_start] and [excluded_period_end]. Both columns have date datatype.

Now I have 2 fields in my form [start_date] and [end_date].

I want to get all the records excluding the period stored in the database. The code I am using is:

      $hotels = Hotel::whereHas('location' , function($query) use($searchOptions){


            if(trim($searchOptions['location']) != ''){
              $query->where('location_title', $searchOptions['location']);
            }

          })
          ->where('excluded_period_start', '<', $start)
          ->where('excluded_period_end', '>', $end)
          ->where('active', 1)
          ->take(10)
          ->paginate(10);

However, this only gives me results which comes between the range stored in my database but I want the results outside of that range.

I have tried many things like ->whereBetween() but none of them worked.

Any help would be appreciated.

javier_domenech · Accepted Answer · 2018-02-16 18:07:15Z

1

Your initial excluded_period can't be between your start and end:

          ->whereNotBetween('excluded_period_start', [$start, $end])
          ->whereNotBetween('excluded_period_end', [$start, $end])

edited Feb 16, 2018 at 18:07

answered Feb 16, 2018 at 15:39

javier_domenech

6,3036 gold badges41 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bilal Mustafa Over a year ago

let me give it a try.

Collectives™ on Stack Overflow

Laravel - PHP/MySQL date range filter against date range in database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related