WP_User_Query::prepare_query( string|array $query = array() )

Prepare the query variables.

Contents

Description Description

Parameters Parameters

$query

(string|array) (Optional) Array or string of Query parameters.

  • 'blog_id'
    (int) The site ID. Default is the current site.
  • 'role'
    (string|array) An array or a comma-separated list of role names that users must match to be included in results. Note that this is an inclusive list: users must match *each* role.
  • 'role__in'
    (array) An array of role names. Matched users must have at least one of these roles.
  • 'role__not_in'
    (array) An array of role names to exclude. Users matching one or more of these roles will not be included in results.
  • 'meta_key'
    (string) User meta key.
  • 'meta_value'
    (string) User meta value.
  • 'meta_compare'
    (string) Comparison operator to test the $meta_value. Accepts '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'EXISTS', 'NOT EXISTS', 'REGEXP', 'NOT REGEXP', or 'RLIKE'. Default '='.
  • 'include'
    (array) An array of user IDs to include.
  • 'exclude'
    (array) An array of user IDs to exclude.
  • 'search'
    (string) Search keyword. Searches for possible string matches on columns. When $search_columns is left empty, it tries to determine which column to search in based on search string.
  • 'search_columns'
    (array) Array of column names to be searched. Accepts 'ID', 'user_login', 'user_email', 'user_url', 'user_nicename', 'display_name'.
  • 'orderby'
    (string|array) Field(s) to sort the retrieved users by. May be a single value, an array of values, or a multi-dimensional array with fields as keys and orders ('ASC' or 'DESC') as values. Accepted values are 'ID', 'display_name' (or 'name'), 'include', 'user_login' (or 'login'), 'login__in', 'user_nicename' (or 'nicename'), 'nicename__in', 'user_email (or 'email'), 'user_url' (or 'url'), 'user_registered' (or 'registered'), 'post_count', 'meta_value', 'meta_value_num', the value of $meta_key, or an array key of $meta_query. To use 'meta_value' or 'meta_value_num', $meta_key must be also be defined. Default 'user_login'.
  • 'order'
    (string) Designates ascending or descending order of users. Order values passed as part of an $orderby array take precedence over this parameter. Accepts 'ASC', 'DESC'. Default 'ASC'.
  • 'offset'
    (int) Number of users to offset in retrieved results. Can be used in conjunction with pagination. Default 0.
  • 'number'
    (int) Number of users to limit the query for. Can be used in conjunction with pagination. Value -1 (all) is supported, but should be used with caution on larger sites. Default -1 (all users).
  • 'paged'
    (int) When used with number, defines the page of results to return. Default 1.
  • 'count_total'
    (bool) Whether to count the total number of users found. If pagination is not needed, setting this to false can improve performance. Default true.
  • 'fields'
    (string|array) Which fields to return. Single or all fields (string), or array of fields. Accepts 'ID', 'display_name', 'user_login', 'user_nicename', 'user_email', 'user_url', 'user_registered'. Use 'all' for all fields and 'all_with_meta' to include meta fields. Default 'all'.
  • 'who'
    (string) Type of users to query. Accepts 'authors'. Default empty (all users).
  • 'has_published_posts'
    (bool|array) Pass an array of post types to filter results to users who have published posts in those post types. true is an alias for all public post types.
  • 'nicename'
    (string) The user nicename.
  • 'nicename__in'
    (array) An array of nicenames to include. Users matching one of these nicenames will be included in results.
  • 'nicename__not_in'
    (array) An array of nicenames to exclude. Users matching one of these nicenames will not be included in results.
  • 'login'
    (string) The user login.
  • 'login__in'
    (array) An array of logins to include. Users matching one of these logins will be included in results.
  • 'login__not_in'
    (array) An array of logins to exclude. Users matching one of these logins will not be included in results.

Default value: array()

Top ↑

Source Source

File: wp-includes/class-wp-user-query.php 

	public function prepare_query( $query = array() ) {
		global $wpdb;

		if ( empty( $this->query_vars ) || ! empty( $query ) ) {
			$this->query_limit = null;
			$this->query_vars  = $this->fill_query_vars( $query );
		}

		/**
		 * Fires before the WP_User_Query has been parsed.
		 *
		 * The passed WP_User_Query object contains the query variables, not
		 * yet passed into SQL.
		 *
		 * @since 4.0.0
		 *
		 * @param WP_User_Query $this The current WP_User_Query instance,
		 *                            passed by reference.
		 */
		do_action( 'pre_get_users', $this );

		// Ensure that query vars are filled after 'pre_get_users'.
		$qv =& $this->query_vars;
		$qv = $this->fill_query_vars( $qv );

		if ( is_array( $qv['fields'] ) ) {
			$qv['fields'] = array_unique( $qv['fields'] );

			$this->query_fields = array();
			foreach ( $qv['fields'] as $field ) {
				$field                = 'ID' === $field ? 'ID' : sanitize_key( $field );
				$this->query_fields[] = "$wpdb->users.$field";
			}
			$this->query_fields = implode( ',', $this->query_fields );
		} elseif ( 'all' == $qv['fields'] ) {
			$this->query_fields = "$wpdb->users.*";
		} else {
			$this->query_fields = "$wpdb->users.ID";
		}

		if ( isset( $qv['count_total'] ) && $qv['count_total'] ) {
			$this->query_fields = 'SQL_CALC_FOUND_ROWS ' . $this->query_fields;
		}

		$this->query_from  = "FROM $wpdb->users";
		$this->query_where = 'WHERE 1=1';

		// Parse and sanitize 'include', for use by 'orderby' as well as 'include' below.
		if ( ! empty( $qv['include'] ) ) {
			$include = wp_parse_id_list( $qv['include'] );
		} else {
			$include = false;
		}

		$blog_id = 0;
		if ( isset( $qv['blog_id'] ) ) {
			$blog_id = absint( $qv['blog_id'] );
		}

		if ( $qv['has_published_posts'] && $blog_id ) {
			if ( true === $qv['has_published_posts'] ) {
				$post_types = get_post_types( array( 'public' => true ) );
			} else {
				$post_types = (array) $qv['has_published_posts'];
			}

			foreach ( $post_types as &$post_type ) {
				$post_type = $wpdb->prepare( '%s', $post_type );
			}

			$posts_table        = $wpdb->get_blog_prefix( $blog_id ) . 'posts';
			$this->query_where .= " AND $wpdb->users.ID IN ( SELECT DISTINCT $posts_table.post_author FROM $posts_table WHERE $posts_table.post_status = 'publish' AND $posts_table.post_type IN ( " . join( ', ', $post_types ) . ' ) )';
		}

		// nicename
		if ( '' !== $qv['nicename'] ) {
			$this->query_where .= $wpdb->prepare( ' AND user_nicename = %s', $qv['nicename'] );
		}

		if ( ! empty( $qv['nicename__in'] ) ) {
			$sanitized_nicename__in = array_map( 'esc_sql', $qv['nicename__in'] );
			$nicename__in           = implode( "','", $sanitized_nicename__in );
			$this->query_where     .= " AND user_nicename IN ( '$nicename__in' )";
		}

		if ( ! empty( $qv['nicename__not_in'] ) ) {
			$sanitized_nicename__not_in = array_map( 'esc_sql', $qv['nicename__not_in'] );
			$nicename__not_in           = implode( "','", $sanitized_nicename__not_in );
			$this->query_where         .= " AND user_nicename NOT IN ( '$nicename__not_in' )";
		}

		// login
		if ( '' !== $qv['login'] ) {
			$this->query_where .= $wpdb->prepare( ' AND user_login = %s', $qv['login'] );
		}

		if ( ! empty( $qv['login__in'] ) ) {
			$sanitized_login__in = array_map( 'esc_sql', $qv['login__in'] );
			$login__in           = implode( "','", $sanitized_login__in );
			$this->query_where  .= " AND user_login IN ( '$login__in' )";
		}

		if ( ! empty( $qv['login__not_in'] ) ) {
			$sanitized_login__not_in = array_map( 'esc_sql', $qv['login__not_in'] );
			$login__not_in           = implode( "','", $sanitized_login__not_in );
			$this->query_where      .= " AND user_login NOT IN ( '$login__not_in' )";
		}

		// Meta query.
		$this->meta_query = new WP_Meta_Query();
		$this->meta_query->parse_query_vars( $qv );

		if ( isset( $qv['who'] ) && 'authors' == $qv['who'] && $blog_id ) {
			$who_query = array(
				'key'     => $wpdb->get_blog_prefix( $blog_id ) . 'user_level',
				'value'   => 0,
				'compare' => '!=',
			);

			// Prevent extra meta query.
			$qv['blog_id'] = 0;
			$blog_id       = 0;

			if ( empty( $this->meta_query->queries ) ) {
				$this->meta_query->queries = array( $who_query );
			} else {
				// Append the cap query to the original queries and reparse the query.
				$this->meta_query->queries = array(
					'relation' => 'AND',
					array( $this->meta_query->queries, $who_query ),
				);
			}

			$this->meta_query->parse_query_vars( $this->meta_query->queries );
		}

		$roles = array();
		if ( isset( $qv['role'] ) ) {
			if ( is_array( $qv['role'] ) ) {
				$roles = $qv['role'];
			} elseif ( is_string( $qv['role'] ) && ! empty( $qv['role'] ) ) {
				$roles = array_map( 'trim', explode( ',', $qv['role'] ) );
			}
		}

		$role__in = array();
		if ( isset( $qv['role__in'] ) ) {
			$role__in = (array) $qv['role__in'];
		}

		$role__not_in = array();
		if ( isset( $qv['role__not_in'] ) ) {
			$role__not_in = (array) $qv['role__not_in'];
		}

		if ( $blog_id && ( ! empty( $roles ) || ! empty( $role__in ) || ! empty( $role__not_in ) || is_multisite() ) ) {
			$role_queries = array();

			$roles_clauses = array( 'relation' => 'AND' );
			if ( ! empty( $roles ) ) {
				foreach ( $roles as $role ) {
					$roles_clauses[] = array(
						'key'     => $wpdb->get_blog_prefix( $blog_id ) . 'capabilities',
						'value'   => '"' . $role . '"',
						'compare' => 'LIKE',
					);
				}

				$role_queries[] = $roles_clauses;
			}

			$role__in_clauses = array( 'relation' => 'OR' );
			if ( ! empty( $role__in ) ) {
				foreach ( $role__in as $role ) {
					$role__in_clauses[] = array(
						'key'     => $wpdb->get_blog_prefix( $blog_id ) . 'capabilities',
						'value'   => '"' . $role . '"',
						'compare' => 'LIKE',
					);
				}

				$role_queries[] = $role__in_clauses;
			}

			$role__not_in_clauses = array( 'relation' => 'AND' );
			if ( ! empty( $role__not_in ) ) {
				foreach ( $role__not_in as $role ) {
					$role__not_in_clauses[] = array(
						'key'     => $wpdb->get_blog_prefix( $blog_id ) . 'capabilities',
						'value'   => '"' . $role . '"',
						'compare' => 'NOT LIKE',
					);
				}

				$role_queries[] = $role__not_in_clauses;
			}

			// If there are no specific roles named, make sure the user is a member of the site.
			if ( empty( $role_queries ) ) {
				$role_queries[] = array(
					'key'     => $wpdb->get_blog_prefix( $blog_id ) . 'capabilities',
					'compare' => 'EXISTS',
				);
			}

			// Specify that role queries should be joined with AND.
			$role_queries['relation'] = 'AND';

			if ( empty( $this->meta_query->queries ) ) {
				$this->meta_query->queries = $role_queries;
			} else {
				// Append the cap query to the original queries and reparse the query.
				$this->meta_query->queries = array(
					'relation' => 'AND',
					array( $this->meta_query->queries, $role_queries ),
				);
			}

			$this->meta_query->parse_query_vars( $this->meta_query->queries );
		}

		if ( ! empty( $this->meta_query->queries ) ) {
			$clauses            = $this->meta_query->get_sql( 'user', $wpdb->users, 'ID', $this );
			$this->query_from  .= $clauses['join'];
			$this->query_where .= $clauses['where'];

			if ( $this->meta_query->has_or_relation() ) {
				$this->query_fields = 'DISTINCT ' . $this->query_fields;
			}
		}

		// sorting
		$qv['order'] = isset( $qv['order'] ) ? strtoupper( $qv['order'] ) : '';
		$order       = $this->parse_order( $qv['order'] );

		if ( empty( $qv['orderby'] ) ) {
			// Default order is by 'user_login'.
			$ordersby = array( 'user_login' => $order );
		} elseif ( is_array( $qv['orderby'] ) ) {
			$ordersby = $qv['orderby'];
		} else {
			// 'orderby' values may be a comma- or space-separated list.
			$ordersby = preg_split( '/[,\s]+/', $qv['orderby'] );
		}

		$orderby_array = array();
		foreach ( $ordersby as $_key => $_value ) {
			if ( ! $_value ) {
				continue;
			}

			if ( is_int( $_key ) ) {
				// Integer key means this is a flat array of 'orderby' fields.
				$_orderby = $_value;
				$_order   = $order;
			} else {
				// Non-integer key means this the key is the field and the value is ASC/DESC.
				$_orderby = $_key;
				$_order   = $_value;
			}

			$parsed = $this->parse_orderby( $_orderby );

			if ( ! $parsed ) {
				continue;
			}

			if ( 'nicename__in' === $_orderby || 'login__in' === $_orderby ) {
				$orderby_array[] = $parsed;
			} else {
				$orderby_array[] = $parsed . ' ' . $this->parse_order( $_order );
			}
		}

		// If no valid clauses were found, order by user_login.
		if ( empty( $orderby_array ) ) {
			$orderby_array[] = "user_login $order";
		}

		$this->query_orderby = 'ORDER BY ' . implode( ', ', $orderby_array );

		// limit
		if ( isset( $qv['number'] ) && $qv['number'] > 0 ) {
			if ( $qv['offset'] ) {
				$this->query_limit = $wpdb->prepare( 'LIMIT %d, %d', $qv['offset'], $qv['number'] );
			} else {
				$this->query_limit = $wpdb->prepare( 'LIMIT %d, %d', $qv['number'] * ( $qv['paged'] - 1 ), $qv['number'] );
			}
		}

		$search = '';
		if ( isset( $qv['search'] ) ) {
			$search = trim( $qv['search'] );
		}

		if ( $search ) {
			$leading_wild  = ( ltrim( $search, '*' ) != $search );
			$trailing_wild = ( rtrim( $search, '*' ) != $search );
			if ( $leading_wild && $trailing_wild ) {
				$wild = 'both';
			} elseif ( $leading_wild ) {
				$wild = 'leading';
			} elseif ( $trailing_wild ) {
				$wild = 'trailing';
			} else {
				$wild = false;
			}
			if ( $wild ) {
				$search = trim( $search, '*' );
			}

			$search_columns = array();
			if ( $qv['search_columns'] ) {
				$search_columns = array_intersect( $qv['search_columns'], array( 'ID', 'user_login', 'user_email', 'user_url', 'user_nicename', 'display_name' ) );
			}
			if ( ! $search_columns ) {
				if ( false !== strpos( $search, '@' ) ) {
					$search_columns = array( 'user_email' );
				} elseif ( is_numeric( $search ) ) {
					$search_columns = array( 'user_login', 'ID' );
				} elseif ( preg_match( '|^https?://|', $search ) && ! ( is_multisite() && wp_is_large_network( 'users' ) ) ) {
					$search_columns = array( 'user_url' );
				} else {
					$search_columns = array( 'user_login', 'user_url', 'user_email', 'user_nicename', 'display_name' );
				}
			}

			/**
			 * Filters the columns to search in a WP_User_Query search.
			 *
			 * The default columns depend on the search term, and include 'ID', 'user_login',
			 * 'user_email', 'user_url', 'user_nicename', and 'display_name'.
			 *
			 * @since 3.6.0
			 *
			 * @param string[]      $search_columns Array of column names to be searched.
			 * @param string        $search         Text being searched.
			 * @param WP_User_Query $this           The current WP_User_Query instance.
			 */
			$search_columns = apply_filters( 'user_search_columns', $search_columns, $search, $this );

			$this->query_where .= $this->get_search_sql( $search, $search_columns, $wild );
		}

		if ( ! empty( $include ) ) {
			// Sanitized earlier.
			$ids                = implode( ',', $include );
			$this->query_where .= " AND $wpdb->users.ID IN ($ids)";
		} elseif ( ! empty( $qv['exclude'] ) ) {
			$ids                = implode( ',', wp_parse_id_list( $qv['exclude'] ) );
			$this->query_where .= " AND $wpdb->users.ID NOT IN ($ids)";
		}

		// Date queries are allowed for the user_registered field.
		if ( ! empty( $qv['date_query'] ) && is_array( $qv['date_query'] ) ) {
			$date_query         = new WP_Date_Query( $qv['date_query'], 'user_registered' );
			$this->query_where .= $date_query->get_sql();
		}

		/**
		 * Fires after the WP_User_Query has been parsed, and before
		 * the query is executed.
		 *
		 * The passed WP_User_Query object contains SQL parts formed
		 * from parsing the given query.
		 *
		 * @since 3.1.0
		 *
		 * @param WP_User_Query $this The current WP_User_Query instance,
		 *                            passed by reference.
		 */
		do_action_ref_array( 'pre_user_query', array( &$this ) );
	}

Expand full source code Collapse full source code View on Trac

Top ↑

Changelog Changelog

Changelog
Version Description
4.7.0 Added 'nicename', 'nicename__in', 'nicename__not_in', 'login', 'login__in', and 'login__not_in' parameters.
4.4.0 Added 'paged', 'role__in', and 'role__not_in' parameters. The 'role' parameter was updated to permit an array or comma-separated list of values. The 'number' parameter was updated to support querying for all users with using -1.
4.3.0 Added 'has_published_posts' parameter.
4.2.0 Added 'meta_value_num' support for $orderby parameter. Added multi-dimensional array syntax for $orderby parameter.
4.1.0 Added the ability to order by the include value.
3.1.0 Introduced.

Top ↑

Top ↑

Uses Uses

Uses
Uses Description
wp-includes/class-wp-user-query.php: WP_User_Query::fill_query_vars()

Fills in missing query variables with default values.
wp-includes/class-wp-user-query.php: WP_User_Query::parse_order()

Parse an ‘order’ query variable and cast it to ASC or DESC as necessary.
wp-includes/class-wp-user-query.php: WP_User_Query::parse_orderby()

Parse and sanitize ‘orderby’ keys passed to the user query.
wp-includes/class-wp-user-query.php: pre_get_users

Fires before the WP_User_Query has been parsed.
wp-includes/formatting.php: sanitize_key()

Sanitizes a string key.
wp-includes/load.php: is_multisite()

If Multisite is enabled.
wp-includes/functions.php: wp_parse_id_list()

Clean up an array, comma- or space-separated list of IDs.
wp-includes/functions.php: absint()

Convert a value to non-negative integer.
wp-includes/class-wp-date-query.php: WP_Date_Query::__construct()

Constructor.
wp-includes/plugin.php: do_action()

Execute functions hooked on a specific action hook.
wp-includes/plugin.php: apply_filters()

Calls the callback functions that have been added to a filter hook.
wp-includes/plugin.php: do_action_ref_array()

Calls the callback functions that have been added to an action hook, specifying arguments in an array.
wp-includes/class-wp-user-query.php: WP_User_Query::get_search_sql()

Used internally to generate an SQL string for searching across multiple columns
wp-includes/class-wp-user-query.php: user_search_columns

Filters the columns to search in a WP_User_Query search.
wp-includes/class-wp-user-query.php: pre_user_query

Fires after the WP_User_Query has been parsed, and before the query is executed.
wp-includes/post.php: get_post_types()

Get a list of all registered post type objects.
wp-includes/ms-functions.php: wp_is_large_network()

Whether or not we have a large network.
wp-includes/wp-db.php: wpdb::prepare()

Prepares a SQL query for safe execution. Uses sprintf()-like syntax.
wp-includes/wp-db.php: wpdb::get_blog_prefix()

Gets blog prefix.
wp-includes/class-wp-meta-query.php: WP_Meta_Query::__construct()

Constructor.
Show 15 more uses Hide more uses

Top ↑

User Contributed Notes User Contributed Notes

  3. Skip to note 3 content
    You must log in to vote on the helpfulness of this noteVote results for this note: 0You must log in to vote on the helpfulness of this note
    Contributed by bcworkz —

    The specification for providing meta_query keys as orderby parameters is confusing at best. The docs say to use “fields as keys” and an accepted value is “an array key of $meta_query“. In fact values must be the array key from the array returned by $WP_Meta_Query::get_clauses(). You could get this array by instantiating WP_Meta_Query with your meta_query arguments, then calling WP_Meta_Query::get_sql('user','wp_users','ID'), (provide your correct table name for wp_users) then calling $WP_Meta_Query::get_clauses(). The array keys in the returned array are the correct keys to assign to the orderby argument.

    A perhaps easier way to get the proper keys is to instantiate WP_User_Query with your meta_query arguments, then examining the SQL query in WP_User_Query::request. In particular the WHERE portion related to user meta. The keys are the table aliases used in the SQL query. The first meta_query argument’s key is always the table name, usually wp_usermeta unless you specified a different prefix in wp-config.php. Additional meta_query arguments are assigned aliases like mt1, mt2, etc. The related meta key name occurs to the right of the = sign in each case. Thus your orderby argument might be array('mt1', 'wp_usermeta',) or if you need differing order arguments, array('mt1'=>'ASC', 'wp_usermeta'=>'DESC',)

    For reference, the user_meta part of the SQL WHERE clause may look something like this:
    (( wp_usermeta.meta_key = 'foo' AND wp_usermeta.meta_value LIKE '%bar%' )
    AND
    ( mt1.meta_key = 'sna' AND mt1.meta_value LIKE '%fu%' ))

You must log in before being able to contribute a note or feedback.