Adding secondary query to query based on parameter in laravel-CodePudding

I am trying to do a complex query based on the input fields of the form.

public function search(Request $request){
    $borrado =
    $activo = 0;
    $models = array();
    $escandallos = Scandal::all();
    foreach ($escandallos as $escandallo){
        if (in_array($escandallo->modelo, $models)) {
        }else{
            array_push($models, $escandallo->modelo);
        }
    }
            
    $query = Model::where('modelo', 'like', '%' . $request['modelo'] . '%')
                        ->where('prototipo', 'like', '%' . $request['prototipo'] . '%')
                        ->where('oficial', 'like', '%' . $request['modofi'] . '%')
                        ->where('usuario_realiza', 'like', '%' . $request['usuario'] . '%')
                        ->where('tipo', 'like', '%' . $request['tipo'] . '%')
                        ->where('activa', 'like', '%' . $activo . '%');
                       
   
    if($request['referencia'] || $request['proveedor']){
        /** Here i want to do this DB:raw where in clause if reference or provider is in request and need to search in other table*/
        $query->DB::raw("where (id_escandallo,id_version) in (select id_escandallo,id_version from escandallo_p where referencia ='".$request['referencia']. "' and proveedor ='".$request['proveedor']."'");
    }

    $query->orderBy('modelo','desc');
    $query->orderBy('id_version','asc');
    
    $scandals = $query->paginate(100);
    dd($scandals);
    return view('designdoc.escandallos',['scandals'=> $scandals],['models'=>$models]);
}

This return this error:

Property [DB] does not exist on the Eloquent builder instance.

Thank you in advance, any help would be appreciated.

Best regards.

CodePudding user response：

Use ->whereRaw()

$query->whereRaw(...);

also, you can simplify this part

if($request['referencia'] || $request['proveedor']){
    $query->whereRaw(...); // but dont use 'where' in the string, its already prepended
}

to this

$query->when($request['referencia'] || $request['proveedor'], function ($q) use ($request) {
    $q->whereRaw(...)
});

it is good so that you can chain it with previous command