Scope in Laravel with related table

Question

Scope in Laravel with related table

Navigation

#1 by (0 votes)

0

I'm stuck in a filter:

These are my tables:

My model for Movement

public function factura() {
    return $this->belongsTo('App\Factura'); 
}

public function cuenta() {
    return $this->belongsTo('App\Cuenta'); 
}

public function centroCosto() {
    return $this->belongsTo('App\CentroCosto'); 
}

public function scopeFfecha($query, $fdesde=NULL, $fhasta=NULL)
{
    if ($fdesde)
        $query=$query->where('fechaContable','>=',$fdesde);
    if ($fhasta)
        $query=$query->where('fechaContable','<=',$fhasta);
    return $query;
}

and for my controller:

public function show(Request $request, $id)
{
    $fdesde= $request->get('fdesde');
    $fhasta=$request->get('fhasta');
    $movimientos=Movimiento::wherecuenta_id($id)->with('factura')
                            ->ffecha($fdesde,$fhasta)
                            ->get();
    return view('resultado.show',compact('movimientos'));   
}

The problem is that since the field fechaContable is not in the table, the filter fails and returns the entire data collection.

I have already checked the date parameters returned by $ request and they are correct.

I need to show all the records of movimientos that meet the condition of cuenta_id and the rank of fechaContable , so if I reverse the relationship (filter the invoice date first) anyway I will have the same problem because I will have to filter by the field cuenta_id of the table movements.

php laravel eloquent filtro scope

asked by Diego Zepeda 29.12.2018 в 23:58

source

1 answer

Export selected data (multiple checkboxes) to a table Why can not I start?

score 0 · Answer 1

Thanks for the comments, I finally found the solution, I'll leave it in case it helps someone else.

The filter must be placed in the Factura model, in the following way (effectively as Alberto Ortega indicates it must be used whereDate or otherwise interfere with the time in the query and the results are erroneous):

public function scopeFfecha($query, $fdesde=NULL, $fhasta=NULL)
{
    if ($fdesde)
        $query=$query->whereDate('fechaContable','>=',$fdesde);
    if ($fhasta)
        $query=$query->whereDate('fechaContable','<=',$fhasta);
    return $query;
}

and the controller uses whereHas in the following way:

$movimientos=Movimiento::wherecuenta_id($id)
                    ->whereHas('factura',function($q) use ($fdesde,$fhasta){
                        $q->ffecha($fdesde,$fhasta);})
                     ->get();

I also took the opportunity to order it by the date of the invoice, so it finally stayed like this:

$movimientos=Movimiento::wherecuenta_id($id)
                    ->whereHas('factura',function($q) use ($fdesde,$fhasta){
                        $q->ffecha($fdesde,$fhasta);
                    })
                    ->join('facturas','movimientos.factura_id','=','facturas.id')
                    ->orderBy('facturas.fechaContable','DESC')
                    ->get();