Good afternoon. I have the following tables: one for students, one for the regions where students live, they are related through the IdRegioes field.
I would like to bring the data on student counts by region, according to some criteria to be displayed in my summary view, using SQL in the database I would have the following query:
SELECT i.regioes_idregioes, r.regiao, COUNT(i.regioes_idregioes) AS quantidade FROM inscricoes as i, regioes as r WHERE i.Regioes_IdRegioes = r.IdRegiao and i.Atendido ='N' and i.Evadido = 'N' and i.Desistente = 'N' and i.DataNascimento >= '2015-03-31' GROUP BY i.regioes_idregioes;
When transporting this query to an Eloquent template I did the following:
$TotInscRegioes = Inscricao::selectRaw('inscricoes.Regioes_IdRegioes',['count(*) as TotRegiao'],'Regioes.Regiao') ->join('Regioes','inscricoes.Regioes_IdRegioes','=','Regioes.IdRegiao') ->where('Atendido','N')->where('Desistente','N')->where('Evadido','N') ->where('DataNascimento','>=',$dataLimite) ->groupBy('inscricoes.Regioes_IdRegioes','Regioes.Regiao') ->get();
However, I always get the general total of students and not the counting by region. I already researched several and I can not find a way out, have you ever had a situation like this? What was the solution you found?
Note: I have already tried using select and putting -> count () at the end and even then the data did not come apart.
Thank you for help.