WordPress and WooCommerce at Scale with 500,000 Users Examples

disable_customer_reports.php

/**
 * Remove WooCommerce customer reports.
 *
 * @param  array $reports WooCommerce default reports.
 *
 * @return array          WooCommerce reports.
 */
function remove_customer_reports( $reports ) {
  unset( $reports['customers'] );

	return $reports;
}
add_filter( 'woocommerce_admin_reports', 'remove_customer_reports' );

search_customers.php

<?php

	/**
	 * Search customers and return customer IDs.
	 *
	 * @param  string     $term Search term.
	 * @param  int|string $limit Limit search results.
	 * @since 3.0.7
	 *
	 * @return array
	 */
	public function search_customers( $term, $limit = '' ) {
		$results = apply_filters( 'woocommerce_customer_pre_search_customers', false, $term, $limit );
		if ( is_array( $results ) ) {
			return $results;
		}

		$query = new WP_User_Query(
			apply_filters(
				'woocommerce_customer_search_customers', array(
					'search'         => '*' . esc_attr( $term ) . '*',
					'search_columns' => array( 'user_login', 'user_url', 'user_email', 'user_nicename', 'display_name' ),
					'fields'         => 'ID',
					'number'         => $limit,
				), $term, $limit, 'main_query'
			)
		);

		$query2 = new WP_User_Query(
			apply_filters(
				'woocommerce_customer_search_customers', array(
					'fields'     => 'ID',
					'number'     => $limit,
					'meta_query' => array(
						'relation' => 'OR',
						array(
							'key'     => 'first_name',
							'value'   => $term,
							'compare' => 'LIKE',
						),
						array(
							'key'     => 'last_name',
							'value'   => $term,
							'compare' => 'LIKE',
						),
					),
				), $term, $limit, 'meta_query'
			)
		);

		$results = wp_parse_id_list( array_merge( (array) $query->get_results(), (array) $query2->get_results() ) );

		if ( $limit && count( $results ) > $limit ) {
			$results = array_slice( $results, 0, $limit );
		}

		return $results;
	}

WooCommerce User Reports - Customers List.sql

/* Get info for all administrators */
SELECT SQL_CALC_FOUND_ROWS bca_users.ID
FROM bca_users
INNER JOIN bca_usermeta
ON ( bca_users.ID = bca_usermeta.user_id )
LEFT JOIN bca_usermeta as meta2
ON (bca_users.ID = meta2.user_id) 
WHERE 1=1
AND ( ( ( bca_usermeta.meta_key = 'bca_capabilities'
AND bca_usermeta.meta_value LIKE '%\"administrator1\"%' ) ) )
AND meta2.meta_key = 'last_name' 
ORDER BY meta2.meta_value, user_login ASC;

/* Get info for all shop managers */
SELECT SQL_CALC_FOUND_ROWS bca_users.ID
FROM bca_users
INNER JOIN bca_usermeta
ON ( bca_users.ID = bca_usermeta.user_id )
LEFT JOIN bca_usermeta as meta2
ON (bca_users.ID = meta2.user_id) 
WHERE 1=1
AND ( ( ( bca_usermeta.meta_key = 'bca_capabilities'
AND bca_usermeta.meta_value LIKE '%\"shop\\_manager\"%' ) ) )
AND meta2.meta_key = 'last_name' 
ORDER BY meta2.meta_value, user_login ASC;

/* Get info for all others */
SELECT SQL_CALC_FOUND_ROWS bca_users.*
FROM bca_users
LEFT JOIN bca_usermeta as meta2
ON (bca_users.ID = meta2.user_id) 
WHERE 1=1
AND bca_users.ID NOT IN (9,6,10,21)
AND meta2.meta_key = 'last_name' 
ORDER BY meta2.meta_value, user_login ASC 
LIMIT 0, 20;

WooCommerce User Reports - Customers vs. Guests.sql

/* Get IDs of all administrators */
SELECT SQL_CALC_FOUND_ROWS wp_users.ID
FROM wp_users
INNER JOIN wp_usermeta
ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND ( ( ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"administrator\"%' ) ) )
ORDER BY user_login ASC;

/* Get IDs of all shop managers */
SELECT SQL_CALC_FOUND_ROWS wp_users.ID
FROM wp_users
INNER JOIN wp_usermeta
ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND ( ( ( wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%\"shop\\_manager\"%' ) ) )
ORDER BY user_login ASC;

/* Then get all other users not in those IDs */
SELECT SQL_CALC_FOUND_ROWS wp_users.user_registered
FROM wp_users
WHERE 1=1
AND wp_users.ID NOT IN (32,1,8,2,30,17,23,5,24,19,22,9,6,21,10)
ORDER BY user_login ASC;