7

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.

Improve this question
0

2 Answers 2

Reset to default
22

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I think this answer and @Ross Wilson are both correct, but this answer seems a bit more clean since it's built into the library. Unfortunately for me I'm on an older version of Laravel so I will have to use his.
I know this is from a long time ago but is this naming convention still followed in Laravel 6.x? I ask because it seems it changed to be exactly what you specified. So without the suffix '_count'.
@Daniel No. It looks like it was changed slightly in 5.5. Starting with 5.5.0, if you specify the field name alias, it won't append "_count" to the name you specify. If you don't specify an alias, it will append "_count" to the field name. So, starting in 5.5.0, if you say withCount('users as enabled_users'), the field name will be enabled_users, not enabled_users_count. However, withCount('users') will still return users_count.
8

The only way that I know of would to define different relationships for those conditions and then use the withCount with those e.g.

public function enableUsers() { return $this->hasMany(User::class) //Or whatever the relationship is ->where('enabled', '=', 1); }

then:

MyModel::withCount('enabledUsers', 'phoneUsers')->get();

Hope this helps!

Improve this answer

1 Comment

I never knew you could add clauses like ->where() directly to the relation. Good Answer!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.