Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Deekshith's avatar

get count on nested relationship

i have tables like below,

rooms

id, room_name, capacity

room_slots

id, room_id, from_time, to_time

booked_slots

id, room_slot_id, date

Flow will be like below,

  1. User selects the room
  2. user selects the date of booking
  3. On select of room slots will be displayed But now i want to add some conditions to filter slots
  4. filter out slots if that slot has more than or equal to room capacity on selected date. i have a code like below
$roomdata = Room::with(['roomslots' => function ($q) use($bookingdate,$currenttime){
            $q->when($bookingdate == todaydate(),function($qu) use ($currenttime,$bookingdate){
                    $qu->where('from_time','>',$currenttime);
                })
        },'roomslots.bookedslots' => function($q) use($bookingdate){
            $q->where('student_schedule_date',$bookingdate);
        }])->where('room_code',$request->room)->where('active_status',1)->first();

if($roomdata)
        {
            $roomslotdata = $roomdata->roomslots->map(function($slot) use($roomdata) {
                if($roomdata->seating_capacity > 0 && $roomdata->seating_capacity > count($slot->bookedslots))
                {
                    return [
                        'id' => $slot->id,
                        'roomdata' => $roomdata,
                        'from_time' => $slot->from_time,
                        'to_time' => $slot->to_time,
                        'title' => $slot->title,
                        'capacity' => $roomdata->seating_capacity,
                        'bookedcount' => count($slot->bookedslots),
                    ];

                } else {
                    return false;
                }
            })->reject(function ($value) {
                return $value === false;
            });

This query is working fine but Is there any way i can use count() instead of loading the relations in first query only? Thank you

0 likes
9 replies
Sinnbeck's avatar

What is offlineconvertedtestdetail?

And you can load counts from the database using ->withCount(). It works exactly like with() so you can pass it an array if you need to filter it. The count is named roomslots_count for the roomslots relationship

Deekshith's avatar

@Sinnbeck Sorry i have updated the question it should be $slot->bookedslots I tried withCount but it is not working for nested one. i ahve a relationship like below,

Room.php

public function roomslots()
{
return $this->hasMany('App\RoomSlot','room_id');
}

RoomSlot.php

public function bookedslots()
{
		return $this->hasMany('App\BookedSlot','room_slot_id');
}

so withCount() is not working for nested relationship 'roomslots.bookedslots' here i want to add count for bookedslots

Deekshith's avatar

@Sinnbeck i tried hasManyThrough like below,

return $roomdata = Room::with(['roomslots' => function ($q) use ($bookingdate, $currenttime) {
                $q->when($bookingdate == todaydate(),function($qu) use ($currenttime) {
                    $qu->where('from_time','>',$currenttime);
                });
               }])->withCount(['bookedslots' => function ($q) use($bookingdate) {
                    $q->where('student_schedule_date',$bookingdate)
                      ->whereIn('attended_status',['None','Present']);
                }])
            ->where('room_code', $request->room)
            ->where('active_status', 1)
            ->first();

Room.php

public function bookedslots()
    {
      return $this->hasManyThrough(
          BookedSlot::class,
          RoomSlot::class,
          'room_id',
          'room_slot_id',
         );
    }

But above query return count including all slots but i need it in slot level right now i am getting response like below,

bookedslots_count: 1,
seating_capacity: 2,
subcenterroomslots: [ {
0: {id: 7,  sub_center_room_id: 5, from_time: "09:00:00",…}
1: {id: 8, sub_center_room_id: 5, from_time: "13:00:00",…}
2: {id: 10,  sub_center_room_id: 5, from_time: "18:00:00",…}
}]

i want to alter like below,


seating_capacity: 2,
subcenterroomslots: [ {
0: {id: 7,  sub_center_room_id: 5, from_time: "09:00:00",bookedslots_count: 1,}
1: {id: 8, sub_center_room_id: 5, from_time: "13:00:00",bookedslots_count: 0}
2: {id: 10,  sub_center_room_id: 5, from_time: "18:00:00",bookedslots_count: 0}
}]
Deekshith's avatar

@Sinnbeck Sorry again i have different function name it should be like below,

seating_capacity: 2,
roomslots: [ {
0: {id: 7,  sub_center_room_id: 5, from_time: "09:00:00",bookedslots_count: 1,}
1: {id: 8, sub_center_room_id: 5, from_time: "13:00:00",bookedslots_count: 0}
2: {id: 10,  sub_center_room_id: 5, from_time: "18:00:00",bookedslots_count: 0}
}]

And now i tried like below and it is working fine now,

return $roomdata = Room::with(['roomslots' => function ($q) use ($bookingdate, $currenttime) {
                $q->when($bookingdate == todaydate(),function($qu) use ($currenttime) {
                    $qu->where('from_time','>',$currenttime);
                })->withCount(['bookedslots' => function($qu) use ($bookingdate) {
                    $qu->where('student_schedule_date', $bookingdate)
                        ->whereIn('attended_status',['None','Present']);
                }]);
               }])
            ->where('room_code', $request->room)
            ->where('active_status', 1)
            ->first();
Sinnbeck's avatar
Sinnbeck
4 years ago
Best Answer
Level 102

@Deekshith Ah then you just nest it under that. Not needs for the hasManyThrough

with(['roomslots' => function ($q) use ($bookingdate, $currenttime) {
                $q->withCount('bookedslots) //add it here
                      ->when($bookingdate == todaydate(),function($qu) use ($currenttime) {
                    $qu->where('from_time','>',$currenttime);
                })
1 like

Please or to participate in this conversation.