orWhere clause allows duplicities-CodePudding

My code is not working and I know it´s because of the orwhere clauses.

This is for an item list, and the objective is to avoid duplicates. So if 'nombre', 'codigo_interno' or 'codigo_comercial' already exists in the list, they should not appear again. If you type the attribute 'nombre' and it has been added to the list, it won´t appear in the drop-down list to add items, but codigo_interno and codigo_comerial do, meaning that the clause ->where('nombre' , 'like' , '%'.$request->busqueda.'%') is working, but the orWhere are not.

Could you tell me whats wrong with it?

public function findArticulo(Request $request)
{

    $id = $request->id;

    $articulos = Articulo::query()->select([
        'id',
        'codigo_interno',
        'codigo_comercial',
        'impuestos_venta',
        'impuestos_compra',
        'nombre',
        'descripcion',
        'status'])
        ->where('status','activo')
        ->whereNotIn('id', function($query)use($id){
            $query->select('articulo_id')
                ->from('listas_precios_articulos')
                ->where('lista_precio_id', $id)
                ->whereNotIn('estatus', ['eliminado']);
        })
        ->where('nombre' , 'like' , '%'.$request->busqueda.'%')
        ->orWhere('codigo_interno' , 'like' , '%'.$request->busqueda.'%')
        ->orWhere('codigo_comercial' , 'like' , '%'.$request->busqueda.'%')
        ->get()
        ->toArray();

    return response()->json($articulos,200);
}

CodePudding user response：

So you should make a block with three conditions

$id = $request->id;
$busqueda = $request->busqueda;
$articulos = Articulo::query()->select([
        'id',
        'codigo_interno',
        'codigo_comercial',
        'impuestos_venta',
        'impuestos_compra',
        'nombre',
        'descripcion',
        'status'])
        ->where('status','activo')
        ->whereNotIn('id', function($query)use($id){
            $query->select('articulo_id')
                ->from('listas_precios_articulos')
                ->where('lista_precio_id', $id)
                ->whereNotIn('estatus', ['eliminado']);
        })
        ->where(function($query) use (busqueda){
          $query->where('nombre' , 'like' , '%'.busqueda .'%')
                ->orWhere('codigo_interno' , 'like' , '%'.busqueda .'%')
                ->orWhere('codigo_comercial' , 'like' , '%'.busqueda.'%');
         })
        ->get()
        ->toArray();

It should generated somthing like ... AND (? OR ? OR ?) But you must check that you can pass $request->busqueda in use or you can do normal variable to pass in it.