Count returning null value with Query Builder Laravel

Asked

Viewed 236 times

1

I have a problem, I have a query that searches the employee data plus the sum of vacation days and absences that an employee has. The query is working if the employee has some day registered in the tables, absence and vacation. The problem is that if these employees have no record of absences or vacation the query does not work.

I need you when you are null in these tables, set the value 0.

$servidores = DB::table('sch_sismapa.tb_servidor')
->join('sch_decom.tb_servidor', 'sch_sismapa.tb_servidor.nr_matricula', '=', 'sch_decom.tb_servidor.nr_matricula')
->join('sch_sismapa.tb_falta','sch_sismapa.tb_servidor.id', '=', 'sch_sismapa.tb_falta.id_servidor')
->join('sch_sismapa.tb_ferias','sch_sismapa.tb_servidor.id', '=', 'sch_sismapa.tb_ferias.id_servidor')
->select('sch_decom.tb_servidor.nm_servidor','sch_sismapa.tb_servidor.*',DB::raw('COUNT(sch_sismapa.tb_falta.id_servidor) as faltas'),DB::raw('COUNT(sch_sismapa.tb_ferias.id_servidor) as ferias'))
->where('sch_sismapa.tb_servidor.cd_secretaria',$request->cd_secretaria)
->where('sch_sismapa.tb_servidor.id_referencia',$request->id_referencia)
->groupBy('sch_decom.tb_servidor.nm_servidor','sch_sismapa.tb_servidor.id','sch_sismapa.tb_servidor.id_referencia','sch_sismapa.tb_servidor.nr_matricula',
    'sch_sismapa.tb_servidor.cd_secretaria','sch_sismapa.tb_servidor.cd_departamento','sch_sismapa.tb_servidor.nr_dias',
    'sch_sismapa.tb_servidor.st_lancado','sch_sismapa.tb_servidor.st_aprovado','sch_sismapa.tb_servidor.ds_observacao',
    'sch_sismapa.tb_servidor.st_grat_reuniao')
->orderby('sch_sismapa.tb_servidor.cd_departamento', 'asc')
->orderBy('sch_decom.tb_servidor.nm_servidor', 'asc')
->skip($partes)
->take(300)
->get();

I’ve tried using the IFNULL but I think I’m using it wrong.

NOTE: I am using POSTGRESQL and Laravel 5.5.

sql laravel postgresql

  • if you run the query directly in the database it works ?

1 answer

1


I believe your problem is in join where the right one would be to use leftJoin for holidays or absences, because case the innerjoin does not find anything it is a unique parameter, already the leftJoin would not be.

Test the following eloquent, and tell me if it worked:

$servidores = DB::table('sch_sismapa.tb_servidor')
->join('sch_decom.tb_servidor', 'sch_sismapa.tb_servidor.nr_matricula', '=', 'sch_decom.tb_servidor.nr_matricula')
->leftJoin('sch_sismapa.tb_falta','sch_sismapa.tb_servidor.id', '=', 'sch_sismapa.tb_falta.id_servidor')
->leftJoin('sch_sismapa.tb_ferias','sch_sismapa.tb_servidor.id', '=', 'sch_sismapa.tb_ferias.id_servidor')
->select('sch_decom.tb_servidor.nm_servidor','sch_sismapa.tb_servidor.*',DB::raw('COUNT(sch_sismapa.tb_falta.id_servidor) as faltas'),DB::raw('COUNT(sch_sismapa.tb_ferias.id_servidor) as ferias'))
->where('sch_sismapa.tb_servidor.cd_secretaria',$request->cd_secretaria)
->where('sch_sismapa.tb_servidor.id_referencia',$request->id_referencia)
->groupBy('sch_decom.tb_servidor.nm_servidor','sch_sismapa.tb_servidor.id','sch_sismapa.tb_servidor.id_referencia','sch_sismapa.tb_servidor.nr_matricula',
    'sch_sismapa.tb_servidor.cd_secretaria','sch_sismapa.tb_servidor.cd_departamento','sch_sismapa.tb_servidor.nr_dias',
    'sch_sismapa.tb_servidor.st_lancado','sch_sismapa.tb_servidor.st_aprovado','sch_sismapa.tb_servidor.ds_observacao',
    'sch_sismapa.tb_servidor.st_grat_reuniao')
->orderby('sch_sismapa.tb_servidor.cd_departamento', 'asc')
->orderBy('sch_decom.tb_servidor.nm_servidor', 'asc')
->skip($partes)
->take(300)
->get();

Link: https://laravel.com/docs/5.7/queries#joins

Browser other questions tagged sql laravel postgresql

You are not signed in. Login or sign up in order to post.