Eloquent Query Problem

Hello! Im having trouble with this query. It should bring all records that belongs to one user. The problem is that "facturas" can have one user and depends on "clientes" which also has the user.

The thing is than Facturas table can be fetched by its field vendedor_id but is nullable and through its relation with Clientes table which also has its field vendedor_id that also is nullable.

The output should be all the records in Facturas table that match the current user using the field vendedor_id and through the related table Clientes in vendedor_id field. Also, it must include the filter applied like in other fields

It currently fetchs only a portion of the desired data. Can someone give me some direction?

public function facturas(Request $request) {
        $title = 'Facturas (Mis Clientes)';
        $filtro = $request->filtro ?? '';
        $usr = auth()->user()->empleado->vendedor;

        $qry = Facturas::where('tipo', 'I')
            ->where('vendedor_id', $usr->id)
            ->where(function($q) use ($filtro, $usr) {
                $q->whereHas('cliente', function($cte) use ($filtro, $usr) {
                    $cte->whereHas('vendedor', function($v) use ($usr) {
                        $v->where('id', $usr->id);
                    })
                    ->where('nombreComercial', 'like', "%$filtro%")
                    ->orWhere('razonSocial', 'like', "%$filtro%");

                });
            })
            ->orderByDesc('fecha')
            ->paginate(150);

        return view('portalVentas.facturas.index', compact('title', 'filtro', 'qry'));
    }

LaryAI

3 years ago

Level 58

The current query is filtering Facturas by the vendedor_id field, but it's not taking into account the nullable relationship with Clientes. One solution could be to use a left join to include the nullable relationship and filter by both vendedor_id fields. Here's an example:

$qry = Facturas::leftJoin('clientes', 'facturas.cliente_id', '=', 'clientes.id')
    ->where('facturas.tipo', 'I')
    ->where(function($q) use ($filtro, $usr) {
        $q->where('facturas.vendedor_id', $usr->id)
            ->orWhere('clientes.vendedor_id', $usr->id);
    })
    ->where(function($q) use ($filtro) {
        $q->where('clientes.nombreComercial', 'like', "%$filtro%")
            ->orWhere('clientes.razonSocial', 'like', "%$filtro%");
    })
    ->orderByDesc('facturas.fecha')
    ->paginate(150);

This query uses a left join to include the nullable relationship with Clientes, and then filters by both vendedor_id fields using an OR condition. It also includes the filter applied to the nombreComercial and razonSocial fields of Clientes.

Please or to participate in this conversation.