lundi 28 octobre 2019

Doesnthave inside wherehas with hasMany relation

Good day, so I have a model called "Recibo", on this model I have a relationship hasMany like this:

public function  recaudarRecibo(){
     return $this->hasMany('App\RecaudaRecibo','ID_RECIBO','ID_RECIBO');
 }

Because a "RECIBO" (receipt) could be in one or more "RECAUDACION" (takings), then, a "RECAUDACION" could pay one or more "CUOTA"(dues) so on the model "RecaudaRecibo" I have another relationship:

public function compromisoCuotaPago(){
    return $this->hasMany('App\CompromisoCuotaPago', 'ID_RECAUDA', 'ID_RECAUDA')->where('SEC_RECAUDA', $this->SEC_RECAUDA)->where('ID_RECIBO', $this->ID_RECIBO);
}

->where('SEC_RECAUDA', $this->SEC_RECAUDA)->where('ID_RECIBO', $this->ID_RECIBO);

Here is the fun part: Currently I'm developing a report, this is my eloquent query for now:

$recaudaciones = Recibo::whereHas('recaudarRecibo', function($q) use ($anio){
                $q->whereRaw("YEAR(FEC_PAGO) = $anio")
                ->whereRaw("IND_DIR_EXT = 'D'")
                ->doesntHave('compromisoCuotaPago')
                ->where("COD_ESTADO_PAGO", 1);
            })->where("COD_TIPO_APORTE", 1)
            ->where("ID_LINEA_INGRESO", $lineaIngreso->ID_LINEA_INGRESO)
            ->get();

The problem is that whereHas('recaudaRecibo') can't have a relationship 'compromisoCuotaPago' but when I print the query this happens:

select *
 from `M_RECIBO` where 
 exists (
 select * from `R_RECAUDA_RECIBO` where `M_RECIBO`.`ID_RECIBO` = `R_RECAUDA_RECIBO`.`ID_RECIBO` and YEAR(FEC_PAGO) = 2019 
 AND MONTH(FEC_PAGO) = 10 and IND_DIR_EXT = 'D' and not exists 
 (select * from `R_COMPROMISO_CUOTA_PAGO` where `R_RECAUDA_RECIBO`.`ID_RECAUDA` = `R_COMPROMISO_CUOTA_PAGO`.`ID_RECAUDA` 
 and `SEC_RECAUDA` is null and `ID_RECIBO` is null) and `COD_ESTADO_PAGO` = 1) and `COD_TIPO_APORTE` = 1 and YEAR(FEC_VCTO) = (2019 - 1) 
 and `ID_LINEA_INGRESO` = 4;

and SEC_RECAUDA is null and ID_RECIBO is null

So, the relationship is not right, It's good when I use it from an object but when I make a query like that it gives null. Any ideas?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire