BP_User_Query::prepare_user_ids_query()

	public function prepare_user_ids_query() {
		global $wpdb;

		$bp = buddypress();

		// Default query variables used here.
		$type         = '';
		$per_page     = 0;
		$page         = 1;
		$user_id      = 0;
		$include      = false;
		$search_terms = false;
		$exclude      = false;
		$meta_key     = false;
		$meta_value   = false;

		extract( $this->query_vars );

		// Setup the main SQL query container.
		$sql = array(
			'select'  => '',
			'where'   => array('1=1'),
			'orderby' => '',
			'order'   => '',
			'limit'   => ''
		);

		/* TYPE **************************************************************/

		// Determines the sort order, which means it also determines where the
		// user IDs are drawn from (the SELECT and WHERE statements).
		switch ( $type ) {

			// 'online' query happens against the last_activity usermeta key
			// Filter 'bp_user_query_online_interval' to modify the
			// number of minutes used as an interval.
			case 'online' :
				$this->uid_name = 'user_id';
				$this->uid_table = $bp->members->table_name_last_activity;
				$sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
				$sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id );

				/**
				 * Filters the threshold for activity timestamp minutes since to indicate online status.
				 *
				 * @since BuddyPress 1.8.0
				 *
				 * @param int $value Amount of minutes for threshold. Default 15.
				 */
				$sql['where'][] = $wpdb->prepare( "u.date_recorded >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters( 'bp_user_query_online_interval', 15 ) );
				$sql['orderby'] = "ORDER BY u.date_recorded";
				$sql['order']   = "DESC";

				break;

			// 'active', 'newest', and 'random' queries
			// all happen against the last_activity usermeta key.
			case 'active' :
			case 'newest' :
			case 'random' :
				$this->uid_name = 'ID';
				$this->uid_table = $wpdb->users;
				$sql['select']  = $wpdb->prepare( "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u LEFT JOIN {$bp->members->table_name_last_activity} a ON u.ID = a.user_id AND a.component = %s AND a.type = 'last_activity' ", buddypress()->members->id );
				$sql['where'][] = " u.user_status = 0 ";

				if ( 'newest' == $type ) {
					$sql['orderby'] = "ORDER BY u.ID";
					$sql['order'] = "DESC";
				} elseif ( 'random' == $type ) {
					$sql['orderby'] = "ORDER BY rand()";
				} else {
					$sql['orderby'] = array(
                        array( "COALESCE( a.date_recorded, NULL )", "DESC" ),
                        array( "u.display_name", "ASC" ),
                    );
				}

				break;

			// 'popular' sorts by the 'total_friend_count' usermeta.
			case 'popular' :
				$this->uid_name = 'user_id';
				$this->uid_table = $wpdb->usermeta;
				$sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
				$sql['where'][] = $wpdb->prepare( "u.meta_key = %s", bp_get_user_meta_key( 'total_friend_count' ) );
				$sql['orderby'] = "ORDER BY CONVERT(u.meta_value, SIGNED)";
				$sql['order']   = "DESC";

				break;

			// 'alphabetical' sorts depend on the xprofile setup.
			case 'alphabetical' :

				// We prefer to do alphabetical sorts against the display_name field
				// of wp_users, because the table is smaller and better indexed. We
				// can do so if xprofile sync is enabled, or if xprofile is inactive.
				//
				// @todo remove need for bp_is_active() check.
				if ( ! bp_disable_profile_sync() || ! bp_is_active( 'xprofile' ) ) {
					$this->uid_name = 'ID';
					$this->uid_table = $wpdb->users;
					$sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
					$sql['orderby'] = "ORDER BY u.display_name";
					$sql['order']   = "ASC";

				// When profile sync is disabled, alphabetical sorts must happen against
				// the xprofile table.
				} else {
					$this->uid_name = 'user_id';
					$this->uid_table = $bp->profile->table_name_data;
					$sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
					$sql['where'][] = $wpdb->prepare( "u.field_id = %d", bp_xprofile_fullname_field_id() );
					$sql['orderby'] = "ORDER BY u.value";
					$sql['order']   = "ASC";
				}

				// Alphabetical queries ignore last_activity, while BP uses last_activity
				// to infer spam/deleted/non-activated users. To ensure that these users
				// are filtered out, we add an appropriate sub-query.
				$sql['where'][] = "u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE " . bp_core_get_status_sql( '' ) . " )";

				break;

			// Any other 'type' falls through.
			default :
				$this->uid_name = 'ID';
				$this->uid_table = $wpdb->users;
				$sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";

				// In this case, we assume that a plugin is
				// handling order, so we leave those clauses
				// blank.
				break;
		}

		/* WHERE *************************************************************/

		// 'include' - User ids to include in the results.
		$include     = false !== $include ? wp_parse_id_list( $include ) : array();
		$include_ids = $this->get_include_ids( $include );

		// An array containing nothing but 0 should always fail.
		if ( 1 === count( $include_ids ) && 0 == reset( $include_ids ) ) {
			$sql['where'][] = $this->no_results['where'];
		} elseif ( ! empty( $include_ids ) ) {
			$include_ids    = implode( ',', wp_parse_id_list( $include_ids ) );
			$sql['where'][] = "u.{$this->uid_name} IN ({$include_ids})";
		}

		// 'exclude' - User ids to exclude from the results.
		if ( false !== $exclude && '' !== $exclude ) {
			$exclude_ids    = implode( ',', wp_parse_id_list( $exclude ) );
			$sql['where'][] = "u.{$this->uid_name} NOT IN ({$exclude_ids})";
		}

		// 'user_id' - When a user id is passed, limit to the friends of the user
		// @todo remove need for bp_is_active() check.
		if ( ! empty( $user_id ) && bp_is_active( 'friends' ) ) {
			$friend_ids = friends_get_friend_user_ids( $user_id );
			$friend_ids = implode( ',', wp_parse_id_list( $friend_ids ) );

			if ( ! empty( $friend_ids ) ) {
				$sql['where'][] = "u.{$this->uid_name} IN ({$friend_ids})";

			// If the user has no friends, the query should always
			// return no users.
			} else {
				$sql['where'][] = $this->no_results['where'];
			}
		}

		/* Search Terms ******************************************************/

		// 'search_terms' searches user_login and user_nicename
		// xprofile field matches happen in bp_xprofile_bp_user_query_search().
		if ( false !== $search_terms ) {
			$search_terms = bp_esc_like( wp_kses_normalize_entities( $search_terms ) );

			if ( $search_wildcard === 'left' ) {
				$search_terms_nospace = '%' . $search_terms;
				$search_terms_space   = '%' . $search_terms . ' %';
			} elseif ( $search_wildcard === 'right' ) {
				$search_terms_nospace =        $search_terms . '%';
				$search_terms_space   = '% ' . $search_terms . '%';
			} else {
				$search_terms_nospace = '%' . $search_terms . '%';
				$search_terms_space   = '%' . $search_terms . '%';
			}

			$matched_user_ids = $wpdb->get_col( $wpdb->prepare(
				"SELECT ID FROM {$wpdb->users} WHERE (display_name LIKE %s OR display_name LIKE %s)",
				$search_terms_nospace,
				$search_terms_space
			) );

			$match_in_clause = empty( $matched_user_ids) ? 'NULL' : implode( ',', $matched_user_ids );
			$sql['where']['search'] = "u.{$this->uid_name} IN ({$match_in_clause})";
		}

		// Only use $member_type__in if $member_type is not set.
		if ( empty( $member_type ) && ! empty( $member_type__in ) ) {
			$member_type = $member_type__in;
		}

		// Profile types to exclude. Note that this takes precedence over inclusions.
		if ( ! empty( $member_type__not_in ) ) {
			$member_type_clause = $this->get_sql_clause_for_member_types( $member_type__not_in, 'NOT IN' );

		// Profile types to include.
		} elseif ( ! empty( $member_type ) ) {
			$member_type_clause = $this->get_sql_clause_for_member_types( $member_type, 'IN' );
		}

		if ( ! empty( $member_type_clause ) ) {
			$sql['where']['member_type'] = $member_type_clause;
		}

		// 'meta_key', 'meta_value' allow usermeta search
		// To avoid global joins, do a separate query.
		if ( false !== $meta_key ) {
			$meta_sql = $wpdb->prepare( "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s", $meta_key );

			if ( false !== $meta_value ) {
				$meta_sql .= $wpdb->prepare( " AND meta_value = %s", $meta_value );
			}

			$found_user_ids = $wpdb->get_col( $meta_sql );

			if ( ! empty( $found_user_ids ) ) {
				$sql['where'][] = "u.{$this->uid_name} IN (" . implode( ',', wp_parse_id_list( $found_user_ids ) ) . ")";
			} else {
				$sql['where'][] = '1 = 0';
			}
		}

		// 'per_page', 'page' - handles LIMIT.
		if ( !empty( $per_page ) && !empty( $page ) ) {
			$sql['limit'] = $wpdb->prepare( "LIMIT %d, %d", intval( ( $page - 1 ) * $per_page ), intval( $per_page ) );
		} else {
			$sql['limit'] = '';
		}

		/**
		 * Filters the clauses for the user query.
		 *
		 * @since BuddyPress 2.0.0
		 *
		 * @param array         $sql  Array of SQL clauses to be used in the query.
		 * @param BP_User_Query $this Current BP_User_Query instance.
		 */
		$sql = apply_filters_ref_array( 'bp_user_query_uid_clauses', array( $sql, &$this ) );

		// Assemble the query chunks.
		$this->uid_clauses['select']  = $sql['select'];
		$this->uid_clauses['where']   = ! empty( $sql['where'] ) ? 'WHERE ' . implode( ' AND ', $sql['where'] ) : '';
		$this->uid_clauses['orderby'] = $sql['orderby'];
		$this->uid_clauses['order']   = $sql['order'];
		$this->uid_clauses['limit']   = $sql['limit'];

		/**
		 * Fires before the BP_User_Query query is made.
		 *
		 * @since BuddyPress 1.7.0
		 *
		 * @param BP_User_Query $this Current BP_User_Query instance. Passed by reference.
		 */
		do_action_ref_array( 'bp_pre_user_query', array( &$this ) );
	}