search dates with laravel not between query

Question

I have to search for data with given dates and time (morning and evening). How can I find available dates from data with laravel query? If i search dates 2019-06-10 to 2019-06-18. It should only return id 3 and 4. In this query, it is returning id 1, 3 and 4. What's wrong with this query it is not checking from in between dates? I have date type column in the database for start and end date. I want to check two conditions

Given dates should be not equal to date_from and date_to
Given dates are not between in these dates

Both conditions in same query. Hope you understand.

id  event_id   date_from    date_to   

1     1        2019-06-08   2019-06-12  
2     1        2019-06-14   2019-06-16  
3     2        2019-06-20   2019-06-25  
4     3        2019-07-26   2019-07-27

$getEvents = Booking::where('category_id', '=', $categoryID)
                ->where('date_from', '!=', $startDate)
                ->where('date_to', '!=', $endDate)
                ->orWhere('date_from', '<', $startDate)
                ->orWhere('date_to', '>', $endDate)
                ->whereNotBetween('date_from', [$startDate,$endDate])
                ->whereNotBetween('date_to', [$startDate,$endDate])
                ->get();

dates 2019-06-10 to 2019-06-18, how id 4 its returned?, it should be only id 3 ? — Kelvin
– Kelvin, Commented Jul 5, 2019 at 4:08
Id 3 and 4 should return because these dates are no in that range... i have multiple halls i have to check in which hall these dates are available.. thats why it should return 3 and 4 — Daniyal Mughees
– Daniyal Mughees, Commented Jul 5, 2019 at 5:42
The two orWhere could cause you problem, I think you should enclose them — Med.ZAIRI
– Med.ZAIRI, Commented Jul 5, 2019 at 19:44

Suresh Ramani · Accepted Answer · 2019-07-05 08:53:13Z

1

Try this

DB::table('table_name')
    ->whereNotBetween('date', [star_date, end_date])    
    ->get();

answered Jul 5, 2019 at 8:53

Suresh Ramani

1211 silver badge8 bronze badges

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

Comments

porloscerros Ψ · Accepted Answer · 2019-07-05 19:04:39Z

1

Assuming that $endDate will always be greater (or equal) than $startDate

$startDate = "2019-06-10";
$endDate = "2019-06-18";

Given dates are not between in these dates

        ->whereDate('date_to', '<', $startDate)
        ->whereDate('date_from', '>', $endDate)

If given $startDate is greater than db 'date_to', $startDate is not between 'date_to' and 'date_from'. Will retrieve records with 'date_to' less than "2019-06-10".

And when the given 'date_from' is greater than $endDate, $endDate is not between 'date_to' and 'date_from'. Will retrieve records with 'date_from' greater than "2019-06-18".

Given dates should be not equal to date_from and date_to

This is not needed since in the query of point 2, we use > and <, which will not take into account equals.

    //->where('date_from', '<>', $startDate)->where('date_from', '<>', $endDate)
    //->where('date_to', '<>', $startDate)->where('date_to', '<>', $endDate)

Just:

$getEvents = Booking::where('category_id', '=', $categoryID)
            ->whereDate('date_to', '<', $startDate)
            ->whereDate('date_from', '>', $endDate)
            ->get();

edited Jul 5, 2019 at 19:04

answered Jul 4, 2019 at 16:44

porloscerros Ψ

5,1182 gold badges14 silver badges22 bronze badges

1 Comment

Daniyal Mughees Over a year ago

this is also not working ... I want to check two conditions 1) Given dates should be not equal to date_from and date_to 2) Given dates are not between in these dates both conditions in same query.

Rashedul Islam Sagor · Accepted Answer · 2019-07-05 04:03:18Z

0

Laravel 5

$getEvents = Booking::where('category_id', '=', $categoryID)
                ->where('date_from', '<', $startDate)
                ->where('date_to', '>', $endDate)
                ->get();

answered Jul 5, 2019 at 4:03

Rashedul Islam Sagor

2,07917 silver badges20 bronze badges

Collectives™ on Stack Overflow

search dates with laravel not between query

3 Answers 3

Comments

1 Comment

Laravel 5

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Laravel 5

Comments

Your Answer

Sign up or log in

Post as a guest

Related