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

guijs's avatar
Level 1

Query builder "where" and "when" methods not being applied together

I'm running the following query builder in Laravel 9. The "where" statement before the "when" conditional clause is only executed if the $term variable is empty. If $term is not empty, only the statements inside "when" are executed. I have tried placing the "where" after "when" but it does not work as expected either.

The "id_make" column is INT datatype and can be null.

What am I missing?

public function myFunction(Request $request)
    {
        $input = $request->all();
        $per_page = ! empty($input['per_page']) ? $input['per_page'] : 100;
        $term = isset($input['term']) ? $input['term'] : '';

        $select = DB::table('mpy_reg as reg')
        ->select([
            'reg.id',
            'reg.NMFAB',
            'reg.DSMOD',
            'reg.REG',
            'reg.id_make',
            'm.name'
        ])
        ->leftJoin('mpy_make as m', 'm.id', '=', 'reg.id_make')
        ->where('reg.id_make', '=', null) // not executed if $term is not empty
        ->when($term, function ($query, $term) {
            $query->where('reg.NMFAB', 'like', '%'.$term.'%')
                  ->orWhere('reg.DSMOD', 'like', '%'.$term.'%')
                  ->orWhere('reg.REG', 'like', '%'.$term.'%');
        })
        //->whereNull('rab.id_make') // not executed here also
        ->groupBy('reg.REG')
        ->orderBy('reg.id', 'asc')
        ->paginate($per_page);

        return $select;
    }
0 likes
2 replies
JeromeFitzpatrick's avatar
Level 8

@guijs try wrapping your orWhere clauses. E.g.

$select = DB::table('mpy_reg as reg')
            ->select([
                'reg.id',
                'reg.NMFAB',
                'reg.DSMOD',
                'reg.REG',
                'reg.id_make',
                'm.name'
            ])
            ->leftJoin('mpy_make as m', 'm.id', '=', 'reg.id_make')
            ->where('reg.id_make', '=', null) // not executed if $term is not empty
            ->when($term, function ($query, $term) {
                $query->where(function ($q) use ($term) {
                    $q->where('reg.NMFAB', 'like', '%'.$term.'%')
                        ->orWhere('reg.DSMOD', 'like', '%'.$term.'%')
                        ->orWhere('reg.REG', 'like', '%'.$term.'%');
                });
            })
            //->whereNull('rab.id_make') // not executed here also
            ->groupBy('reg.REG')
            ->orderBy('reg.id', 'asc')
            ->paginate($per_page);
1 like

Please or to participate in this conversation.