withCount() doesn't apply additional constraints

I have a User model and a Position model. They have a belongsToMany() relationship with each other.

Now I'm trying to list the Positions, along with the list of attached Users.

That works fine, but not when I'm adding a local scope to the query (to apply several filters to the query). I also tried to replace the local scope with a simple where(), but that doesn't work either.

So the code below:

$positions = Position::withCount(['users' => function ($query) {
    $query->withFilters($this->filters);
}])->get();

Generates this query:

select `positions`.*, (select count(*) from `users` inner join `position_user` on `users`.`id` = `position_user`.`user_id` where `positions`.`id` = `position_user`.`position_id` and `users`.`deleted_at` is null and `active` = 1) as `users_count` from `positions`

Any idea why the additional constraints aren't applied?

bugsysha

5 years ago

Level 61

Try to explain what is the expected and what is actual result you get. Just saying that something doesn't work is not very helpful.

Veur

5 years ago

Level 2

Sorry, the expected result is a list of Positions along with the user_count, with the filters applied to the query which counts the users.

So, this is the actual query:

select `positions`.*, (select count(*) from `users` inner join `position_user` on `users`.`id` = `position_user`.`user_id` where `positions`.`id` = `position_user`.`position_id` and `users`.`deleted_at` is null and `active` = 1) as `users_count` from `positions`

While I'm expecting something like:

select `positions`.*, (select count(*) from `users` inner join `position_user` on `users`.`id` = `position_user`.`user_id` where `positions`.`id` = `position_user`.`position_id` and `users`.`deleted_at` is null and `active` = 1 and `first_name` like '%john%' and `city` like '%new%') as `users_count` from `positions`

Please or to participate in this conversation.