How to rename fields returned from Laravel's Query Builder withCount (aka $asColumn)

Question

I have a single model and I am trying to chain on multiple withCounts with different relations. Here is an example:

    return MyModel::query()
        ->withCount(array('users'=>function($query){
            $query->where('enabled', '=', 1);
        }))
        ->withCount(array('users'=>function($query){
            $query->where('phone', '=', "123-4567");
        }))
        ->get();

This doesn't work because Laravel automatically returns a snake_case field name in my results called "users_count", and basically this means the enabled section of the query is overwritten with the phone section.

I've peeked into the Builder.php class a bit and noticed that $asColumn is the name being returned: https://github.com/laravel/framework/commit/67b821dde62e64f94aa7d99f1806ecf03ea323e5, which like mentioned is just a snake_case of the relation's $name + '_count'.

Is there any way to make a custom $asColumn without using a raw query so that I can get both results? I basically want my results to be enabled_users_count and phone_users_count, or something similar. I'd also prefer not to have to do 2 different queries.

Answer 1

This functionality is available as of Laravel 5.3.7, released on 2016-09-08.

Assuming you are on 5.3.7 or later, you just need to add as new_name to the relationship string in the withCount method, and it will name the count field as new_name_count. So, your code would look like:

return MyModel::query()
    ->withCount(array('users as enabled_users'=>function($query){
        $query->where('enabled', '=', 1);
    }))
    ->withCount(array('users as phone_users'=>function($query){
        $query->where('phone', '=', "123-4567");
    }))
    ->get();

This code will generate an enabled_users_count field and a phone_users_count field.

If you are not on this version, and cannot upgrade, you will need to go the route of defining a new relationship, as mentioned by @RossWilson.

---

Laravel >= 5.5

Starting in Laravel 5.5, the _count suffix will not be appended if you specify a column alias. So, starting in 5.5, the above example would generate enabled_users and phone_users fields, instead of enabled_users_count and phone_users_count fields.

The _count suffix is still automatically appended if there is no alias specified, though. So, withCount('users') would still generate a users_count field.