Query by date range in db date range fields

In my application I can add holidays for employers, setting date range (start_date, end_date).

In the frontend user can choose two dates- "date_from" and "date_to" and I should perform query which should return holidays for which at least one day of holiday is between that given dates (so return not only holidays that fits enitirely in that date- which I know how to do).

Unfortunately I dont have any idea how to construct query like that.

RamjithAp

8 years ago

Level 10

Follow this https://laravel.com/docs/5.5/queries#where-clauses

wizjo

8 years ago

Level 26

I know the documentation and all this methods, but I dont have idea how to construct query.

wizjo

8 years ago

Best Answer

Level 26

Ok, I figured it out by myself. Here is the solution:

Holiday::where([
                ['start_date', '>=', $date_from->format('Y-m-d')],
                ['end_date', '<=', $date_to->format('Y-m-d')],
            ])
            ->orWhere([
                ['start_date', '>=', $date_from->format('Y-m-d')],
                ['end_date', '<=', $date_to->format('Y-m-d')],
            ])
            ->orWhere([
                ['start_date', '<=', $date_from->format('Y-m-d')],
                ['end_date', '>=', $date_to->format('Y-m-d')],
            ])              
            ->get();

Please or to participate in this conversation.