Eloquent Query

Hello. I've been struggling with some querys that works as expected unless has where clauses. For instance I'm trying to filter clients that belongs to the current user. When there's no filter works fine, but when filter is submited shows all records that matches the query even if the user is not the owner.

Is there something wrong?

public function index(Request $request) { $filtro = $request->filtro; $empresa = session('empresa')['id'] ?? 0;

    if($filtro) {
        $qry = Clientes::where('empresa_id', $empresa)
            ->where('nombreComercial', 'like', '%' . $filtro . '%')
            ->orWhere('razonSocial', 'like', '%' . $filtro . '%')
            ->orWhere('rfc', 'like', '%' . $filtro . '%')
            ->orWhere('IdTributario', 'like', '%' . $filtro . '%')
            ->orWhere('correoContacto', 'like', '%' . $filtro . '%')
            ->orWhereHas('direcciones', function($qDireccion) use ($filtro) {
                $qDireccion->where('calle', 'like', '%' . $filtro .'%')
                    ->orWhere('colonia', 'like', '%' . $filtro . '%')
                    ->orWhere('municipio', 'like', '%' . $filtro . '%')
                    ->orWhere('estado', 'like', '%' . $filtro . '%')
                    ->orWhere('codigoPostal', 'like', '%' . $filtro . '%');
            })
            ->orWhereHas('contactos', function($qContactos) use ($filtro) {
                $qContactos->where('nombre', 'like', '%' . $filtro .'%')
                    ->orWhere('telefono', 'like', '%' . $filtro . '%')
                    ->orWhere('movil', 'like', '%' . $filtro . '%')
                    ->orWhere('email', 'like', '%' . $filtro . '%');
            })
            ->when(auth()->user()->hasRole('Ventas'), function (Builder $b){
                $b->whereHas('vendedor', function($v) use($b) {
                    $b->where('vendedor_id', auth()->user()->empleado->vendedor->id);
                });
            })->sortable()->paginate(50);
    } else {
        $qry = Clientes::where('empresa_id', $empresa)
            ->when(auth()->user()->hasRole('Ventas'), function (Builder $b){
                $b->where('vendedor_id', auth()->user()->empleado->vendedor->id);
            })->sortable()->paginate(50);
    }

    return view('clientes.index', compact('qry', 'filtro'));
}

LaryAI

3 years ago

Best Answer

Level 58

The issue with the current query is that the orWhere clauses are not being grouped together properly. This means that the query is returning any records that match any of the orWhere conditions, regardless of whether they belong to the current user or not.

To fix this, we can group the orWhere clauses together using a closure. This will ensure that all of the orWhere conditions are grouped together and evaluated as a single unit.

Here's an updated version of the query with the orWhere clauses grouped together:

$qry = Clientes::where('empresa_id', $empresa)
    ->where(function ($query) use ($filtro) {
        $query->where('nombreComercial', 'like', '%' . $filtro . '%')
            ->orWhere('razonSocial', 'like', '%' . $filtro . '%')
            ->orWhere('rfc', 'like', '%' . $filtro . '%')
            ->orWhere('IdTributario', 'like', '%' . $filtro . '%')
            ->orWhere('correoContacto', 'like', '%' . $filtro . '%')
            ->orWhereHas('direcciones', function($qDireccion) use ($filtro) {
                $qDireccion->where('calle', 'like', '%' . $filtro .'%')
                    ->orWhere('colonia', 'like', '%' . $filtro . '%')
                    ->orWhere('municipio', 'like', '%' . $filtro . '%')
                    ->orWhere('estado', 'like', '%' . $filtro . '%')
                    ->orWhere('codigoPostal', 'like', '%' . $filtro . '%');
            })
            ->orWhereHas('contactos', function($qContactos) use ($filtro) {
                $qContactos->where('nombre', 'like', '%' . $filtro .'%')
                    ->orWhere('telefono', 'like', '%' . $filtro . '%')
                    ->orWhere('movil', 'like', '%' . $filtro . '%')
                    ->orWhere('email', 'like', '%' . $filtro . '%');
            });
    })
    ->when(auth()->user()->hasRole('Ventas'), function (Builder $b){
        $b->whereHas('vendedor', function($v) use($b) {
            $b->where('vendedor_id', auth()->user()->empleado->vendedor->id);
        });
    })
    ->sortable()
    ->paginate(50);

In this updated version, we've wrapped the orWhere clauses in a closure using the where method. This ensures that all of the orWhere conditions are grouped together and evaluated as a single unit.

Note that we've also removed the ->sortable() method call from the when closure, as this was causing an error. Instead, we've moved it to the end of the query chain.

With these changes, the query should now return only the records that belong to the current user and match the search criteria.

mfdzmirabal

3 years ago

Level 1

Wow... I didnt think on that. Thanks a lot. Works as it should be

Please or to participate in this conversation.