How to alias multiple withCount on the same related object

Question

I have a Hotel model which 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 trying 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:

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

You can simply do a $hotel->occupied_rooms_count = $hotel->rooms_count — code_ablaze
– code_ablaze, Commented Sep 8, 2016 at 8:32
At the moment, the ORM doesn't produce the two fields since they have the same name. The ORM produces only one count. It's not a simple naming problem — error500
– error500, Commented Sep 8, 2016 at 8:41

Dharman · Accepted Answer · 2024-07-03 10:46:43Z

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:

$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.

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

jaysingkar · Accepted Answer · 2016-09-08 11:27:26Z

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();

xayer · Accepted Answer · 2019-06-20 13:36:18Z

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

Collectives™ on Stack Overflow

How to alias multiple withCount on the same related object

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related