Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mpk123's avatar

Adding brackets to scope query

I have the query below and need to wrap the first query ( $search) in parenthesis and can't figure out how.

public function scopeFilter($query, array $filters)
    {
        $mealType = $filters['mealType'] ?? null;
        $serviceDate = $filters['serviceDate'] ?? null;
        $search = $filters['search'] ?? null;

        $query
            ->when($search, function ($query) use ($search) { 
                $query->where('first_name', 'like', '%'.$search.'%')
                    ->orWhere('last_name', 'like', '%'.$search.'%')
                    ->orWhere('sisid', 'like', '%'.$search.'%')
                    ->orWhere('grade', 'like', '%'.$search.'%')
                    ->orWhere('hr', 'like', '%'.$search.'%', 'and');
        
        })
            ->when($filters['serviceDate'] ?? null, function ($query, $serviceDate) {
                $query->where('enter_date', '<=', $serviceDate)
                    ->where('exit_date', '>=', $serviceDate);
        })
            ->when($filters['grade'] ?? null, function ($query, $grade) {
                $query->where('grade', '=', $grade);
        })
            ->when($filters['hr'] ?? null, function ($query, $hr) {
                    $query->where('hr', '=', $hr);
        })
            ->when( $mealType, function ($query) use ($mealType, $serviceDate) {
                $query->whereDoesntHave('student_meals', fn ($query) => 
                    $query->where('meal_type_id', $mealType )
                        ->where('void', false)
                        ->where('date_served', $serviceDate));
        });        
            
    }

The SQL created is

SELECT * FROM `students` 
WHERE `students`.`site_id` = 1 and `students`.`site_id` IS not NULL 
and (`first_name` like '%maxi%' or `last_name` like '%maxi%' or `sisid` like '%maxi%' or `grade` like '%maxi%' or `hr` like '%maxi%' 
and `enter_date` <= '2022-05-11' 
and `exit_date` >= '2022-05-11' 
and not exists (SELECT * FROM `student_meals` WHERE `students`.`id` = `student_meals`.`student_id` and `meal_type_id` = '1' and `void` = 0 and `date_served` = '2022-05-11')) and `students`.`deleted_at` IS NULL ORDER BY `first_name` ASC, `last_name` ASC

I need it to look like this (parenthesis around the second where clause).

SELECT * FROM `students` 
WHERE `students`.`site_id` = 1 and `students`.`site_id` IS not NULL 
and (`first_name` like '%Maxi%' or `last_name` like '%Maxi%' or `sisid` like '%Maxi%' or `grade` like '%Maxi%' or `hr` like '%Maxi%')  // <--- Need to close here 
and `enter_date` <= '2022-05-11' 
and `exit_date` >= '2022-05-11' 
and not exists (SELECT * FROM `student_meals` WHERE `students`.`id` = `student_meals`.`student_id` and `meal_type_id` = '1' and `void` = 0 and `date_served` = '2022-05-11') and `students`.`deleted_at` IS NULL ORDER BY `first_name` ASC, `last_name` ASC

I've looked at multiple posts and the docs and I can't see what I'm missing. Can anyone help?

0 likes
1 reply
mpk123's avatar

Took a walk, came back and the answer became obvious...

`` Took a walk. Came back and saw the solution:

public function scopeFilter($query, array $filters)
    {
        $mealType = $filters['mealType'] ?? null;
        $serviceDate = $filters['serviceDate'] ?? null;
        $search = $filters['search'] ?? null;

        $query
            ->when($search, function ($query) use ($search) { 
                $query->where( fn ($query) =>
                    $query->where('first_name', 'like', '%'.$search.'%')
                    ->orWhere('last_name', 'like', '%'.$search.'%')
                    ->orWhere('sisid', 'like', '%'.$search.'%')
                    ->orWhere('grade', 'like', '%'.$search.'%')
                    ->orWhere('hr', 'like', '%'.$search.'%', 'and'));
        
        })
            ->when($filters['serviceDate'] ?? null, function ($query, $serviceDate) {
                $query->where('enter_date', '<=', $serviceDate)
                    ->where('exit_date', '>=', $serviceDate);
        })
            ->when($filters['grade'] ?? null, function ($query, $grade) {
                $query->where('grade', '=', $grade);
        })
            ->when($filters['hr'] ?? null, function ($query, $hr) {
                    $query->where('hr', '=', $hr);
        })
            ->when( $mealType, function ($query) use ($mealType, $serviceDate) {
                $query->whereDoesntHave('student_meals', fn ($query) => 
                    $query->where('meal_type_id', $mealType )
                        ->where('void', false)
                        ->where('date_served', $serviceDate));
        });        
            
    }

Please or to participate in this conversation.