Difficulties with Count in query builder Laravel
Hello i am trying to connect 4 of my tables to get all the necessary info in a view The 4 tables are (i will only put the fields it matter for this case):
Users: ID, name
Mentoria (ID_mentor is the foreign key of users.id): ID, ID_mentor
Sessao_Mentoria: ID, ID_Mentoria
User_Sessao (this is a pivot table that connects users and Sessao_mentoria tables, so ID_mentorando here is a foreign key to user.id and id_sessao is a foreign key to sessao_mentoria.id): ID_Mentorando, ID_Sessao
Using this query i get the almost all the result i desire
$sessoes= DB::table('sessao_mentoria')
->join('user_sessao', 'user_sessao.id_sessao', '=',
'sessao_mentoria.id')
->join('mentoria','mentoria.id', '=', 'sessao_mentoria.id_mentoria')
->join('users','users.id', '=', 'mentoria.id_mentor')
->select('sessao_mentoria.*','users.name')
->where('user_sessao.id_mentorando',$user->id)
->orderBy('sessao_mentoria.data')
->get();
I can get all data that is in «Sessao_mentoria table», and the identity of the «Mentor» that is connected to each «Sessao_mentoria), and put it in the order by «data»
However i also want to get how many «mentorandos are connected to each «Sessao_mentoria», so my plan was adding a count in the «user_sessao» table, that would get me the number of «mentorandos» in each Sessao. I tried doing like this
$sessoes= DB::table('sessao_mentoria')
->join('user_sessao', 'user_sessao.id_sessao', '=',
'sessao_mentoria.id')
->join('mentoria','mentoria.id', '=', 'sessao_mentoria.id_mentoria')
->join('users','users.id', '=', 'mentoria.id_mentor')
->select('sessao_mentoria.*','users.name', DB::raw('count(*) as sessao_count,
user_sessao.id_mentorando'))
->where('user_sessao.id_mentorando',$user->id)
->groupBy('user_sessao.id_mentorando')
->orderBy('sessao_mentoria.data')
->get();
and it is only giving me the data of the first row of the Sessao_Mentoria table, and it gives-me as the sessao_count result, the total number of «mentorandos» connected to any Sessao_mentoria, instead of the total number connected to each Sessao. I also tried using Id_sessao, instead of Id_mentorando in the count, and it gave me the same result, so how can i solve this problem? Thank you in advance
Someone made a comment but erased it , however i was able to check it in time, and i did what was suggested and i changed my group by, to «Sessao_mentoria.id» and it worked! So thank you!