Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello,
Among the tables, three are interesting to understand my problem.
1rst table : states (id, name, color)
2nd table : sessions (id, date, state_id)
3rd table : students (id, name)
The number of states is dynamic (in the database), so I can add some new states if needed.
When I display the view of a student, I want to display some statistics with the number of sessions of each state.
$student = Student::
with('sessions')
->withCount([
'sessions as sessions_state_1_count' => function ($query) {
$query->where('state_id', ...);
}])
->find($id);
But the content of the withCount has to be dynamic according to the number of states in the database.
Further more the withCount has to be about the sessions and not the students. So my query is false, my withCount is not at the right place. I'm always clumsy with such queries with Eloquent.
Do you have any idea how to do that ?
Thanks a lot ;).
Vincent
I would do something like:
$sessions_by_state = Sessions::query()
->where('student_id', $student->id)
->get()
->groupBy('state_id');
$sessions_by_state will be a collection of state_ids that you can use ->count() on to get the real count.
I believe if you're using Laravel 9 you can also get away with:
$sessions_by_state = Sessions::query()
->whereBelongsTo($student)
->get()
->groupBy('state_id');
@undeportedmexican That's ok for me ... but is it possible to do the same while retrieving the student datas inside with('sessions') ?
How about like this ->
$sessions_by_state = Sessions::where('student_id', $student->id)->with('states')->get();
foreach($sessions_by_state as $session){
echo count($session->states);
}
@iftekhs Sorry but this cannot work. This will count the number of states for each session but I already know the answer, each session has only one unique state. I need the number of sessions associated with each state.
@vincent15000 So how many sessions are there for a single state, Do you want to count that?
@iftekhs Yes that's it ... but is it possible to do that inside the with('sessions') ? the aim is to retrieve the student's datas and in the same query retrieve the sessions statistics.
@vincent15000 Well I guess you can create a function on the Sessions model which finds its state's sessions count and returns it. that way you can get all students with the sessions and loop through the sessions and call the function to get the state's session count.
@iftekhs That's effectively what I have done because I didn't find the way to include this in my principal query.
Please or to participate in this conversation.