I have 3 tables (establishment, staff, resident) and I want to get the name of the Establishment and the number of resident and staff for each Establishment in one query. But I don't know if it's possible.
I tried this :
DB::table('etablissements')
->select(array('etablissements.nom',
DB::raw('COUNT(residents.id) as nbResidents'),
DB::raw('COUNT(personnels.id) as nbPersonnels')))
->join('residents','etablissements.id','=','residents.etablissement_id')
->join('personnels','etablissements.id','=','personnels.etablissement_id')
->groupBy('etablissements.nom')->get();
But the results are not good. Can you help me?
I want an array with all the names of establishments and for each establishment the number of staff and residents who are attached to it.
Here you can see what I get but the numbers are wrong: https://puu.sh/tL6Jg/7d830067e1.png
There is no establishments with the same name, and the result are the same with the id. I get the same result with the query in SQL, I'm not sure i'm doing it the right way. https://puu.sh/tMMFI/49bd788501.png If I take the first establishment I shoul have 28 residents and 36 staff members.
Solution : I've forgot to put DISTINCT after each COUNT.