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?

CodePudding user response：

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') . '%');
    })
})

CodePudding user response：

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.