Laravel5 ORM : how to alias multiple withCount on the same related object

Question

I have a Hotel model wich has Many Rooms that can be occupied. How should I query this :

Hotel list

• the count of Rooms
• the count of Occupied Rooms

The query :

$hotels = Hotel::where('foo',$bar)
->withCount('rooms')
->withCount(['rooms' => function ($query) {
    $query->where('status', 'Occupied');
    }])
->get();

The result :

$hotel->rooms_count gives the count of occupied rooms, which is the last withCount expression.

What I'm tring to get

• $hotel->rooms_count as the count of rooms in each hotel

• $hotel->occupied_rooms_count as the count of occupied rooms of each hotel

as an alias of the second withcount :

Question

Is there a way to alias the second withCount on Room ?

Answer 1

Although @jaysingkar's answer is a good way to go and answers the question pretty well, yes, it's possible to alias a withCount() call although this hasn't been documented yet:

$hotels = Hotel::where('foo', $bar)
    ->withCount([
        'rooms',
        'rooms AS occupied_rooms' => function ($query) {
            $query->where('status', 'Occupied');
        }
    ])
    ->get();

This will give you the $hotel->occupied_rooms_count with the count of occupied rooms of each hotel. :)

The code where this magic happens can be seen here. It was added in Laravel 5.3.7 through the PR #15279.

Updated: I've submitted a PR and it's now properly documented. :)

Answer 2

Instead of specifying where clause in your withCount define the relation for occupied rooms in Hotel Model.

public function occupied_rooms(){
    return $this->hasMany(Room::class)
                ->where('status', 'Occupied');
}

Now, in your controller use, withCount('occupied_rooms').

$hotels = Hotel::where('foo',$bar)
->withCount(['rooms','occupied_rooms'])
->get();