Difficulties with Count in query builder Laravel

Question

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

Tigane · Accepted Answer · 2022-01-22 18:38:53Z

1

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!

answered Jan 22, 2022 at 18:38

Tigane

9912 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Difficulties with Count in query builder Laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related