| 1 |
<?php |
| 2 |
/** |
| 3 |
* Complete role totals and bounded member samples in one SQL statement. |
| 4 |
* |
| 5 |
* @package OpenStation |
| 6 |
*/ |
| 7 |
defined( 'ABSPATH' ) || exit; |
| 8 |
|
| 9 |
/** |
| 10 |
* Read the current site's role groups independently of directory pagination. |
| 11 |
* |
| 12 |
* One aggregate computes all counts; UNION branches bound each sample on older |
| 13 |
* MySQL versions too: no window functions or truncatable GROUP_CONCAT IDs. |
| 14 |
* Each sample rides in a derived table rather than a parenthesised UNION |
| 15 |
* member: SQLite (Playground, Studio, the SQLite integration plugin) rejects |
| 16 |
* `(SELECT … LIMIT 8) UNION ALL (…)` outright, and MySQL keeps the derived |
| 17 |
* table's ORDER BY + LIMIT. Only server-owned table identifiers are |
| 18 |
* interpolated. Every value is prepared. |
| 19 |
* |
| 20 |
* @return array|WP_Error Summary, or a permission/database error. |
| 21 |
*/ |
| 22 |
function openstation_users_window_roles_summary() { |
| 23 |
global $wpdb; |
| 24 |
if ( ! current_user_can( 'list_users' ) ) { |
| 25 |
return new WP_Error( 'openstation_users_forbidden', __( 'You are not allowed to list users.', 'desktop-mode' ), array( 'status' => 403 ) ); |
| 26 |
} |
| 27 |
$roles = openstation_users_window_all_roles_map(); |
| 28 |
$cap_key = $wpdb->get_blog_prefix( get_current_blog_id() ) . 'capabilities'; |
| 29 |
$member = $wpdb->prepare( "EXISTS (SELECT 1 FROM {$wpdb->usermeta} membership WHERE membership.user_id = u.ID AND membership.meta_key = %s)", $cap_key ); |
| 30 |
$scope = is_multisite() ? $member : '1=1'; |
| 31 |
$matches = array(); |
| 32 |
$count_matches = array(); |
| 33 |
foreach ( array_keys( $roles ) as $role ) { |
| 34 |
// Match a serialized KEY, including its length, not a substring of a role. |
| 35 |
$pattern = '%' . $wpdb->esc_like( 's:' . strlen( $role ) . ':"' . $role . '";' ) . '%'; |
| 36 |
$count_matches[ $role ] = $wpdb->prepare( 'caps.meta_value LIKE %s', $pattern ); |
| 37 |
$matches[ $role ] = $wpdb->prepare( "EXISTS (SELECT 1 FROM {$wpdb->usermeta} caps WHERE caps.user_id = u.ID AND caps.meta_key = %s AND caps.meta_value LIKE %s)", $cap_key, $pattern ); |
| 38 |
} |
| 39 |
$matches[''] = $matches ? 'NOT (' . implode( ' OR ', $matches ) . ')' : '1=1'; |
| 40 |
$roles[''] = __( 'No role', 'desktop-mode' ); |
| 41 |
$any_role = $count_matches ? implode( ' OR ', $count_matches ) : '0=1'; |
| 42 |
$count_matches[''] = ''; |
| 43 |
$columns = array(); |
| 44 |
$empty_columns = array(); |
| 45 |
$index = 0; |
| 46 |
foreach ( $count_matches as $role => $predicate ) { |
| 47 |
if ( '' === $role ) { |
| 48 |
$columns[] = "(COUNT(DISTINCT u.ID) - COUNT(DISTINCT CASE WHEN {$any_role} THEN u.ID END)) AS r{$index}"; |
| 49 |
} else { |
| 50 |
$columns[] = "COUNT(DISTINCT CASE WHEN {$predicate} THEN u.ID END) AS r{$index}"; |
| 51 |
} |
| 52 |
$empty_columns[] = "NULL AS r{$index}"; |
| 53 |
++$index; |
| 54 |
} |
| 55 |
$join_key = $wpdb->prepare( '%s', $cap_key ); |
| 56 |
// All role counts share one membership join and one aggregate scan. |
| 57 |
$branches = array( 'SELECT NULL AS role, COUNT(DISTINCT u.ID) AS total, NULL AS id, NULL AS name, NULL AS slug, NULL AS email, ' . implode( ', ', $columns ) . " FROM {$wpdb->users} u LEFT JOIN {$wpdb->usermeta} caps ON caps.user_id = u.ID AND caps.meta_key = {$join_key} WHERE {$scope}" ); |
| 58 |
$sample = 0; |
| 59 |
foreach ( $matches as $role => $predicate ) { |
| 60 |
$role_sql = $wpdb->prepare( '%s', $role ); |
| 61 |
// Derived tables preserve SQLite portability and stop each sample at eight. |
| 62 |
$branches[] = "SELECT * FROM (SELECT {$role_sql} AS role, NULL AS total, u.ID AS id, u.display_name AS name, u.user_nicename AS slug, u.user_email AS email, " . implode( ', ', $empty_columns ) . " FROM {$wpdb->users} u WHERE {$scope} AND {$predicate} ORDER BY u.display_name, u.ID LIMIT 8) AS sample_{$sample}"; |
| 63 |
++$sample; |
| 64 |
} |
| 65 |
$sql = implode( ' UNION ALL ', $branches ); |
| 66 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared above; identifiers are trusted wpdb table names. |
| 67 |
$rows = $wpdb->get_results( $sql, ARRAY_A ); |
| 68 |
if ( null === $rows || $wpdb->last_error ) { |
| 69 |
return new WP_Error( 'openstation_users_roles_failed', __( 'Role groups could not be loaded. Please try again.', 'desktop-mode' ), array( 'status' => 500 ) ); |
| 70 |
} |
| 71 |
$groups = array(); |
| 72 |
foreach ( $roles as $role => $label ) { |
| 73 |
$groups[ $role ] = array( |
| 74 |
'role' => $role, |
| 75 |
'label' => $label, |
| 76 |
'total' => 0, |
| 77 |
'members' => array(), |
| 78 |
); |
| 79 |
} |
| 80 |
$total = 0; |
| 81 |
foreach ( $rows as $row ) { |
| 82 |
if ( null === $row['role'] ) { |
| 83 |
$total = (int) $row['total']; |
| 84 |
foreach ( array_keys( $roles ) as $index => $role ) { |
| 85 |
$groups[ $role ]['total'] = (int) $row[ 'r' . $index ]; |
| 86 |
} |
| 87 |
} elseif ( null !== $row['total'] ) { |
| 88 |
$groups[ $row['role'] ]['total'] = (int) $row['total']; |
| 89 |
} else { |
| 90 |
$groups[ $row['role'] ]['members'][] = array( |
| 91 |
'id' => (int) $row['id'], |
| 92 |
'name' => $row['name'], |
| 93 |
'slug' => $row['slug'], |
| 94 |
'roles' => $row['role'] ? array( $row['role'] ) : array(), |
| 95 |
'avatar_urls' => array( '48' => get_avatar_url( $row['email'], array( 'size' => 48 ) ) ), |
| 96 |
); |
| 97 |
} |
| 98 |
} |
| 99 |
// Empty registered roles are useful; the synthetic No role group only appears when needed. |
| 100 |
if ( 0 === $groups['']['total'] ) { |
| 101 |
unset( $groups[''] ); |
| 102 |
} |
| 103 |
$groups = array_values( $groups ); |
| 104 |
usort( |
| 105 |
$groups, |
| 106 |
static function ( $a, $b ) { |
| 107 |
$by_count = $b['total'] <=> $a['total']; |
| 108 |
return 0 !== $by_count ? $by_count : strcmp( $a['role'], $b['role'] ); |
| 109 |
} |
| 110 |
); |
| 111 |
/** |
| 112 |
* Filter the complete current-site role summary shown by the Users app. |
| 113 |
* |
| 114 |
* @param array $summary Total unique users and role groups with up to eight members each. |
| 115 |
*/ |
| 116 |
return apply_filters( |
| 117 |
'openstation_users_window_roles_summary', |
| 118 |
array( |
| 119 |
'total' => $total, |
| 120 |
'groups' => $groups, |
| 121 |
) |
| 122 |
); |
| 123 |
} |
| 124 |
|
| 125 |
/** Register the authenticated, read-only role summary. */ |
| 126 |
function openstation_users_window_register_roles_summary_route() { |
| 127 |
register_rest_route( |
| 128 |
'desktop-mode/v1', |
| 129 |
'/users/roles-summary', |
| 130 |
array( |
| 131 |
'methods' => WP_REST_Server::READABLE, |
| 132 |
'permission_callback' => static function () { |
| 133 |
return current_user_can( 'list_users' ); }, |
| 134 |
'callback' => 'openstation_users_window_roles_summary', |
| 135 |
) |
| 136 |
); |
| 137 |
} |
| 138 |
add_action( 'rest_api_init', 'openstation_users_window_register_roles_summary_route' ); |
| 139 |
|
