search dates with laravel not between query

2
votes

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

  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. 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();
3
laravellaravel-5eloquent
dates 2019-06-10 to 2019-06-18, how id 4 its returned?, it should be only id 3 ? – Kelvin
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
The two orWhere could cause you problem, I think you should enclose them – Med.ZAIRI

3 Answers

1
votes

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

$startDate = "2019-06-10";
$endDate = "2019-06-18";
  1. 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".

  1. 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();
0
votes

Laravel 5

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

Try this

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