Count returning null value with Query Builder Laravel

Question

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.

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