Laravel whereDate() is not working as expected

The following is part of my query for querying data between two dates:

->whereDate('fixture_date', '>=', Carbon::now()->subDays($pastDays))
->whereDate('fixture_date', '<=', Carbon::now()->addDays($futureDays))
->when(request('search'), function ($query) {
    $query->orWhere('fixture_hometeam_name', 'LIKE', '%' . request('search') . '%')
          ->orWhere('fixture_awayteam_name', 'LIKE', '%' . request('search') . '%');
        })

When request('search') is empty, I am getting the expected results but when not, the whereDate queries are not working.

How should this be modified to give the correct results?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Add another layer to add parenthesis around the orWhere of the search.

->whereDate('fixture_date', '>=', Carbon::now()->subDays($pastDays))
->whereDate('fixture_date', '<=', Carbon::now()->addDays($futureDays))
->when(request('search'), function ($query) {
    $query->where(function($subQuery) {
        $subQuery->orWhere('fixture_hometeam_name', 'LIKE', '%' . request('search') . '%')
          ->orWhere('fixture_awayteam_name', 'LIKE', '%' . request('search') . '%');
    })
})

Method 2

You can use whereBetween instead of two whereDate.

->whereBetween('fixture_date', [
    now()->subDays($pastDays)->startOfDay(), now()->addDays($futureDays)->endOfDay()
])->when($request->search, function ($query, $search) {
    return $query->where(function ($query) use ($search) {
        $query->where('fixture_hometeam_name', 'like', "%{$search}%")
              ->orWhere('fixture_awayteam_name', 'like', "%{$search}%");
    });

https://laravel.com/docs/8.x/queries#logical-grouping

You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x