$roleIds * @return array */ public static function forRoleIds(iterable $roleIds): array { $ids = collect($roleIds) ->map(static fn ($id): int => (int) $id) ->unique() ->values(); if ($ids->isEmpty()) { return []; } return DB::table('admin_user_site_roles as usr') ->leftJoin('admin_user_agents as uag', 'uag.admin_user_id', '=', 'usr.admin_user_id') ->whereIn('usr.role_id', $ids->all()) ->groupBy('usr.role_id') ->selectRaw( 'usr.role_id, ' .'COUNT(DISTINCT CASE WHEN uag.admin_user_id IS NULL THEN usr.admin_user_id END) AS platform_user_count, ' .'COUNT(DISTINCT CASE WHEN uag.admin_user_id IS NOT NULL THEN usr.admin_user_id END) AS agent_user_count' ) ->get() ->mapWithKeys(static function (object $row): array { $platformCount = (int) $row->platform_user_count; $agentCount = (int) $row->agent_user_count; return [(int) $row->role_id => [ 'user_count' => $platformCount + $agentCount, 'platform_user_count' => $platformCount, 'agent_user_count' => $agentCount, ]]; }) ->all(); } }