WordPoints_DB_Query::prepare_meta_where() - Code Metrics - Inspection of "Try omitting the MySQL password when empty" - WordPoints/hooks-api - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( b43b5b...dcfda7 )

by J.D.

created 2016-02-20 16:08 UTC

WordPoints_DB_Query::prepare_meta_where() B

↳ Parent: WordPoints_DB_Query

Complexity

Conditions	4
Paths	4

Size

Total Lines	37
Code Lines	23

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	37
rs	8.5806
cc	4
eloc	23
nc	4
nop	0

<?php

/**
 * Database query class.
 *
 * @package wordpoints-hooks-api
 * @since   1.0.0
 */

/**
 * Database query bootstrap.
 *
 * This class provides a bootstrap that can be extended to provide a simple, common
 * interface for querying a database. The child class defines the table schema, and
 * this bootstrap takes care of the rest.
 *
 * @since 1.0.0
 */
class WordPoints_DB_Query {

	/**
	 * The name of the table this query class is for.
	 *
	 * This should be the full name of the table, including the prefix. You will
	 * therefore likely need to define it from inside your constructor.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	protected $table_name;

	/**
	 * The columns in the table being queried.
	 *
	 * The keys are the names of the columns. The values are arrays that support the
	 * following keys:
	 *
	 * - format (required) The format (%s, %d, or %f) to use when passing the values
	 *   for this format to $wpdb->prepare().
	 * - values (optional) An array of values that this column can have. Any values
	 *   that aren't in this list will be discarded from a query.
	 * - unsigned (optional) Whether the value is unsigned. If this is true, values
	 *   for this column will be rejected if they are not positive.
	 * - is_date (optional) Whether this is a DATETIME field. If so date queries will
	 *   be supported.
	 *
	 * For each column in this array, the following query args are supported:
	 *
	 * - "{$column}" A single value that this column should have.
	 * - "{$column}__compare" How to compare the above value to the value in the DB.
	 * - "{$column}__in" An array of values that this column may have.
	 * - "{$column}__not_in" An array of values that this column may not have.
	 *
	 * Where {$column} is the name of the column.
	 *
	 * The "{$column}" query arg takes precedence over the "{$column}__in" and
	 * "{$column}__not_in" query args.
	 *
	 * However, if the column specifies that is_date is true, then the above are not
	 * supported, and the following are offered instead:
	 *
	 * - "{$column}_query" Arguments to pass to a WP_Date_Query.
	 *
	 * @since 1.0.0
	 *
	 * @var array[]
	 */
	protected $columns = array();

	/**
	 * The slug of the meta type.
	 *
	 * If this is defined, the 'meta_query', 'meta_key', 'meta_value',
	 * 'meta_compare', and 'meta_type' args are supported, and will be passed to
	 * WP_Meta_Query.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	protected $meta_type;

	/**
	 * The default values for the query args.
	 *
	 * You can override this entirely if needed, or just modify it in your
	 * constructor before calling parent::__construct().
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $defaults = array(
		'start'  => 0,
		'order'  => 'DESC',
	);

	/**
	 * The query arguments.
	 *
	 * @since 1.0.0
	 *
	 * @type array $args
	 */
	protected $args = array();

	/**
	 * Whether the query is ready for execution, or still needs to be prepared.
	 *
	 * @since 1.0.0
	 *
	 * @type bool $is_query_ready
	 */
	protected $is_query_ready = false;

	/**
	 * The SELECT statement for the query.
	 *
	 * @since 1.0.0
	 *
	 * @type string $select
	 */
	protected $select;

	/**
	 * The SELECT COUNT statement for a count query.
	 *
	 * @since 1.0.0
	 *
	 * @type string $select_count
	 */
	protected $select_count = 'SELECT COUNT(*)';

	/**
	 * The JOIN query with the meta table.
	 *
	 * @since 1.0.0
	 *
	 * @type string $meta_join
	 */
	protected $meta_join;

	/**
	 * The WHERE clause for the query.
	 *
	 * @since 1.0.0
	 *
	 * @type string $where
	 */
	protected $where;

	/**
	 * The array of conditions for the WHERE clause.
	 *
	 * @since 1.0.0
	 *
	 * @type array $wheres
	 */
	protected $wheres = array();

	/**
	 * The LIMIT clause for the query.
	 *
	 * @since 1.0.0
	 *
	 * @type string $limit
	 */
	protected $limit;

	/**
	 * The ORDER clause for the query.
	 *
	 * @since 1.0.0
	 *
	 * @type string $order
	 */
	protected $order;

	/**
	 * Holds the meta query object when a meta query is being performed.
	 *
	 * @since 1.0.0
	 *
	 * @type WP_Meta_Query $meta_query
	 */
	protected $meta_query;

	//
	// Public Methods.
	//

	/**
	 * Construct the class.
	 *
	 * All of the arguments are expected *not* to be SQL escaped.
	 *
	 * @since 1.0.0
	 *
	 * @see WP_Meta_Query for the proper arguments for 'meta_query', 'meta_key', 'meta_value', 'meta_compare', and 'meta_type'.
	 *
	 * @param array $args {
	 *        The arguments for the query.
	 *
	 *        @type string|array $fields              Fields to include in the results.
	 *        @type int          $limit               The maximum number of results to return. Default is null (no limit).
	 *        @type int          $start               The start for the LIMIT clause. Default: 0.
	 *        @type string       $order_by             The field to use to order the results. Default: 'date'.
	 *        @type string       $order               The order for the query: ASC or DESC (default).
	 *        @type string       $meta_key            See WP_Meta_Query.
	 *        @type mixed        $meta_value          See WP_Meta_Query.
	 *        @type string       $meta_compare        See WP_Meta_Query.
	 *        @type string       $meta_type           See WP_Meta_Query.
	 *        @type array        $meta_query          See WP_Meta_Query.
	 * }
	 */
	public function __construct( $args = array() ) {
		$this->args = array_merge( $this->defaults, $args );
	}

	/**
	 * Get a query arg.
	 *
	 * @since 1.0.0
	 *
	 * @param string $arg The query arg whose value to retrieve.
	 *
	 * @return mixed|null The query arg's value, or null if it isn't set.
	 */
	public function get_arg( $arg ) {

		if ( isset( $this->args[ $arg ] ) ) {
			return $this->args[ $arg ];
		} else {
			return null;
		}
	}

	/**
	 * Set arguments for the query.
	 *
	 * All of the arguments supported by the constructor may be passed in here, and
	 * will be merged into the array of existing args.
	 *
	 * @since 1.0.0
	 *
	 * @param array $args A list of arguments to set and their values.
	 */
	public function set_args( array $args ) {

		$this->args = array_merge( $this->args, $args );

		$this->is_query_ready = false;
	}

	/**
	 * Count the number of results.
	 *
	 * When used with a query that contains a LIMIT clause, this method currently
	 * returns the count of the query ignoring the LIMIT, as would be the case with
	 * any similar query. However, this behaviour is not hardened and should not be
	 * relied upon. Make inquiry before assuming the constancy of this behaviour.
	 *
	 * @since 1.0.0
	 *
	 * @return int The number of results.
	 */
	public function count() {

		global $wpdb;

		$count = (int) $wpdb->get_var( $this->get_sql( 'SELECT COUNT' ) );

		return $count;
	}

	/**
	 * Get the results for the query.
	 *
	 * @since 1.0.0
	 *
	 * @param string $method The method to use. Options are 'results', 'row', 'col',
	 *                       and 'var'.
	 *
	 * @return mixed The results of the query, or false on failure.
	 */
	public function get( $method = 'results' ) {

		global $wpdb;

		$methods = array( 'results', 'row', 'col', 'var' );

		if ( ! in_array( $method, $methods ) ) {

			_doing_it_wrong( __METHOD__, esc_html( sprintf( 'WordPoints Debug Error: invalid get method %s, possible values are %s', $method, implode( ', ', $methods ) ) ), '1.0.0' );

			return false;
		}

		$result = $wpdb->{"get_{$method}"}( $this->get_sql() );

		return $result;
	}

	/**
	 * Get the SQL for the query.
	 *
	 * This function can return the SQL for a SELECT or SELECT COUNT query. To
	 * specify which one to return, set the $select_type parameter. Defaults to
	 * SELECT.
	 *
	 * This function is public for debugging purposes.
	 *
	 * @since 1.0.0
	 *
	 * @param string $select_type The type of query, SELECT, or SELECT COUNT.
	 *
	 * @return string The SQL for the query.
	 */
	public function get_sql( $select_type = 'SELECT' ) {

		$this->prepare_query();

		$select = ( 'SELECT COUNT' === $select_type )
			? $this->select_count
			: $this->select;

		return $select
			. "\nFROM `{$this->table_name}`\n"
			. $this->meta_join
			. $this->where
			. $this->order
			. $this->limit;
	}

	//
	// Filter Methods.
	//

	/**
	 * Filter date query valid columns for WP_Date_Query.
	 *
	 * @since 1.0.0
	 *
	 * @WordPress\filter date_query_valid_columns Added and subsequently removed by
	 *                                            self::prepare_date_where().
	 *
	 * @param string[] $valid_columns The names of the valid columns for date queries.
	 *
	 * @return string[] The valid columns.
	 */
	public function date_query_valid_columns_filter( $valid_columns ) {

		$valid_columns = array_merge(
			$valid_columns
			, array_keys(
				wp_list_filter( $this->columns, array( 'is_date' => true ) )
			)
		);

		return $valid_columns;
	}

	//
	// Protected Methods.
	//

	/**
	 * Prepare the query.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_query() {

		if ( ! $this->is_query_ready ) {

			$this->prepare_select();
			$this->prepare_where();
			$this->prepare_order_by();
			$this->prepare_limit();

			$this->is_query_ready = true;
		}
	}

	/**
	 * Prepare the select statement.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_select() {

		$all_fields = array_keys( $this->columns );
		$fields = array();

		if ( ! empty( $this->args['fields'] ) ) {

			$fields = (array) $this->args['fields'];
			$diff   = array_diff( $fields, $all_fields );
			$fields = array_intersect( $all_fields, $fields );

			if ( ! empty( $diff ) ) {
				_doing_it_wrong( __METHOD__, esc_html( 'WordPoints Debug Error: invalid field(s) "' . implode( '", "', $diff ) . '" given' ), '1.0.0' );
			}
		}

		// Pull all fields by default.
		if ( empty( $fields ) ) {
			$fields = $all_fields;
		}

		$fields = implode( ', ', array_map( 'wordpoints_escape_mysql_identifier', $fields ) );

		$this->select = "SELECT {$fields}";
	}

	/**
	 * Validates a value against an array of sanitizing functions.
	 *
	 * @since 1.0.0
	 *
	 * @param mixed      $value      The value to validate.
	 * @param callable[] $validators The validators to validate it against.
	 *
	 * @return mixed The validated value, or false if invalid.
	 */
	protected function validate_value( $value, $validators ) {

		foreach ( $validators as $validator ) {

			$value = call_user_func_array( $validator, array( &$value ) );

			if ( false === $value ) {
				break;
			}
		}

		return $value;
	}

	/**
	 * Validates an array of values against an array of sanitizing functions.
	 *
	 * @since 1.0.0
	 *
	 * @param array      $values     The values to validate.
	 * @param callable[] $validators The validators to validate each value against.
	 *
	 * @return array The validated values, with any invalid ones removed.
	 */
	protected function validate_values( $values, $validators ) {

		foreach ( $values as $index => $value ) {

			$value = $this->validate_value( $value, $validators );

			if ( false === $value ) {
				unset( $values[ $index ] );
			}
		}

		return $values;
	}

	/**
	 * Validate an unsigned column.
	 *
	 * The value must be positive, zero-inclusive. We can't just use
	 * wordpoints_posint() because it is zero exclusive.
	 *
	 * @since 1.0.0
	 *
	 * @param mixed $value The value to validate.
	 *
	 * @return int|false The validated value or false.
	 */
	protected function validate_unsigned_column( $value ) {

		 if ( false !== wordpoints_int( $value ) && $value >= 0 ) {
			 return $value;
		 }

		return false;
	}

	/**
	 * Get an array of validating/sanitizing functions for the values of a column.
	 *
	 * @since 1.0.0
	 *
	 * @param array $data The data for the column.
	 *
	 * @return callable[] The validation functions.
	 */
	protected function get_validators_for_column( $data ) {

		$validators = array();

		// Default validators for integer columns.
		if ( '%d' === $data['format'] ) {
			if ( ! empty( $data['unsigned'] ) ) {
				$validators[] = array( $this, 'validate_unsigned_column' );
			} else {
				$validators[] = 'wordpoints_int';
			}
		}

		return $validators;
	}

	/**
	 * Prepare the conditions for the WHERE clause for a column.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The column name.
	 * @param array  $data   The column data.
	 */
	protected function prepare_column_where( $column, $data ) {

		// If a single value has been supplied for the column, it takes precedence.
		if ( isset( $this->args[ $column ] ) ) {
			$this->prepare_column( $column, $data );
		} elseif ( isset( $this->args[ "{$column}__in" ] ) ) {
			$this->prepare_column__in( $column, $data );
		} elseif ( isset( $this->args[ "{$column}__not_in" ] ) ) {
			$this->prepare_column__in( $column, $data, 'NOT IN' );
		}
	}

	/**
	 * Prepare a single-value condition for the WHERE clause for a column.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The name of the column
	 * @param array  $data   The column data.
	 */
	protected function prepare_column( $column, $data ) {

		global $wpdb;

		if (
			isset( $data['values'] )
			&& ! in_array( $this->args[ $column ], $data['values'], true )
		) {
			return;
		}

		$value = $this->validate_value(
			$this->args[ $column ]
			, $this->get_validators_for_column( $data )
		);

		if ( false === $value ) {
			return;
		}

		$compare = $this->get_comparator_for_column( $column, $data );

		$column = wordpoints_escape_mysql_identifier( $column );

		$this->wheres[] = $wpdb->prepare(
			"{$column} {$compare} {$data['format']}"
			, $value
		);
	}

	/**
	 * Get the comparator for a column.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The column name.
	 * @param array  $data   The column data.
	 *
	 * @return string The comparator for the column.
	 */
	protected function get_comparator_for_column( $column, $data ) {

		$comparisons = array( '=', '<', '>', '<>', '!=', '<=', '>=' );

		// MySQL doesn't support LIKE and NOT LIKE for int columns.
		// See http://stackoverflow.com/q/8422455/1924128
		if ( '%s' === $data['format'] ) {
			$comparisons = array_merge( $comparisons, array( 'LIKE', 'NOT LIKE' ) );
		}

		$comparator = '=';

		if (
			isset( $this->args["{$column}__compare"] )
			&& in_array( $this->args["{$column}__compare"], $comparisons, true )
		) {
			$comparator = $this->args["{$column}__compare"];
		}

		return $comparator;
	}

	/**
	 * Prepare the IN or NOT IN conditions for a column.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The name of the column.
	 * @param array  $data   The column data.
	 * @param string $type   The type of IN clause, IN or NOT IN.
	 */
	protected function prepare_column__in( $column, $data, $type = 'IN' ) {

		$key = "{$column}__" . strtolower( str_replace( ' ', '_', $type ) );

		if ( empty( $this->args[ $key ] ) || ! is_array( $this->args[ $key ] ) ) {
			return;
		}

		$values = $this->args[ $key ];

		if ( isset( $data['values'] ) ) {
			$values = array_intersect( $values, $data['values'] );
		} else {
			$values = $this->validate_values(
				$values
				, $this->get_validators_for_column( $data )
			);
		}

		if ( empty( $values ) ) {
			return;
		}

		$in = wordpoints_prepare__in( $values, $data['format'] );

		if ( false === $in ) {
			return;
		}

		$column = wordpoints_escape_mysql_identifier( $column );

		$this->wheres[] = "{$column} {$type} ({$in})";
	}

	/**
	 * Prepare the WHERE clause for the query.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_where() {

		$this->wheres = array();

		foreach ( $this->columns as $column => $data ) {

			if ( ! empty( $data['is_date'] ) ) {
				$this->prepare_date_where( $column );
			} else {
				$this->prepare_column_where( $column, $data );
			}
		}

		$this->prepare_meta_where();

		if ( ! empty( $this->wheres ) ) {
			$this->where = 'WHERE ' . implode( ' AND ', $this->wheres ) . "\n";
		}
	}

	/**
	 * Prepare the LIMIT clause for the query.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_limit() {

		// MySQL doesn't allow for the offset without a limit, so if no limit is set
		// we can ignore the start arg. See http://stackoverflow.com/a/271650/1924128
		if ( ! isset( $this->args['limit'] ) ) {
			return;
		}

		foreach ( array( 'limit', 'start' ) as $key ) {

			// Save a backup of the arg value since wordpoints_int() is by reference.
			$arg = $this->args[ $key ];

			if ( false === wordpoints_int( $this->args[ $key ] ) ) {

				_doing_it_wrong(
					__METHOD__
					, sprintf(
						"WordPoints Debug Error: '%s' must be a positive integer, %s given"
						, esc_html( $key )
						, esc_html( strval( $arg ) ? $arg : gettype( $arg ) )
					)
					, '1.0.0'
				);

				$this->args[ $key ] = 0;
			}
		}

		if ( $this->args['limit'] > 0 && $this->args['start'] >= 0 ) {
			$this->limit = "LIMIT {$this->args['start']}, {$this->args['limit']}";
		}
	}

	/**
	 * Prepare the ORDER BY clause for the query.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_order_by() {

		if ( empty( $this->args['order_by'] ) ) {
			return;
		}

		$order    = $this->args['order'];
		$order_by = $this->args['order_by'];

		if ( ! in_array( $order, array( 'DESC', 'ASC' ) ) ) {

			_doing_it_wrong( __METHOD__, esc_html( "WordPoints Debug Error: invalid 'order' \"{$order}\", possible values are DESC and ASC" ), '1.0.0' );
			$order = 'DESC';
		}

		if ( 'meta_value' === $order_by ) {

			global $wpdb;

			$meta_table_name = wordpoints_escape_mysql_identifier(
				$wpdb->{"{$this->meta_type}meta"}
			);

			if ( isset( $this->args['meta_type'] ) ) {

				$meta_type = $this->meta_query->get_cast_for_type( $this->args['meta_type'] );
				$order_by  = "CAST({$meta_table_name}.meta_value AS {$meta_type})";

			} else {

				$order_by = "{$meta_table_name}.meta_value";
			}

		} elseif ( isset( $this->columns[ $order_by ] ) ) {

			$order_by = wordpoints_escape_mysql_identifier( $order_by );

		} else {

			_doing_it_wrong( __METHOD__, esc_html( "WordPoints Debug Error: invalid 'order_by' \"{$order_by}\", possible values are " . implode( ', ', array_keys( $this->columns ) ) ), '1.0.0' );
			return;
		}

		$this->order = "ORDER BY {$order_by} {$order}\n";
	}

	/**
	 * Prepare the date query for a column.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The name of the column.
	 */
	protected function prepare_date_where( $column ) {

		if (
			empty( $this->args["{$column}_query"] )
			|| ! is_array( $this->args["{$column}_query"] )
		) {
			return;
		}

		add_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_filter' ) );

		$date_query = new WP_Date_Query( $this->args["{$column}_query"], $column );
		$date_query = $date_query->get_sql();

		if ( ! empty( $date_query ) ) {
			$this->wheres[] = ltrim( $date_query, ' AND' );
		}

		remove_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_filter' ) );
	}

	/**
	 * Prepare the meta query.
	 *
	 * @since 1.0.0
	 */
	protected function prepare_meta_where() {

		if ( empty( $this->meta_type ) ) {
			return;
		}

		$meta_args = array_intersect_key(
			$this->args
			, array(
				'meta_key'     => '',
				'meta_value'   => '',
				'meta_compare' => '',
				'meta_type'    => '',
				'meta_query'   => '',
			)
		);

		if ( empty( $meta_args ) ) {
			return;
		}

		$this->meta_query = new WP_Meta_Query();
		$this->meta_query->parse_query_vars( $meta_args );

		$meta_query = $this->meta_query->get_sql(
			$this->meta_type
			, $this->table_name
			, 'id'
			, $this
		);

		if ( ! empty( $meta_query['where'] ) ) {
			$this->wheres[] = ltrim( $meta_query['where'], ' AND' );
		}

		$this->meta_join = $meta_query['join'] . "\n";
	}
}

// EOF


1			<?php
2
3			/**
4			* Database query class.
5			*
6			* @package wordpoints-hooks-api
7			* @since 1.0.0
8			*/
9
10			/**
11			* Database query bootstrap.
12			*
13			* This class provides a bootstrap that can be extended to provide a simple, common
14			* interface for querying a database. The child class defines the table schema, and
15			* this bootstrap takes care of the rest.
16			*
17			* @since 1.0.0
18			*/
19			class WordPoints_DB_Query {
20
21			/**
22			* The name of the table this query class is for.
23			*
24			* This should be the full name of the table, including the prefix. You will
25			* therefore likely need to define it from inside your constructor.
26			*
27			* @since 1.0.0
28			*
29			* @var string
30			*/
31			protected $table_name;
32
33			/**
34			* The columns in the table being queried.
35			*
36			* The keys are the names of the columns. The values are arrays that support the
37			* following keys:
38			*
39			* - format (required) The format (%s, %d, or %f) to use when passing the values
40			* for this format to $wpdb->prepare().
41			* - values (optional) An array of values that this column can have. Any values
42			* that aren't in this list will be discarded from a query.
43			* - unsigned (optional) Whether the value is unsigned. If this is true, values
44			* for this column will be rejected if they are not positive.
45			* - is_date (optional) Whether this is a DATETIME field. If so date queries will
46			* be supported.
47			*
48			* For each column in this array, the following query args are supported:
49			*
50			* - "{$column}" A single value that this column should have.
51			* - "{$column}__compare" How to compare the above value to the value in the DB.
52			* - "{$column}__in" An array of values that this column may have.
53			* - "{$column}__not_in" An array of values that this column may not have.
54			*
55			* Where {$column} is the name of the column.
56			*
57			* The "{$column}" query arg takes precedence over the "{$column}__in" and
58			* "{$column}__not_in" query args.
59			*
60			* However, if the column specifies that is_date is true, then the above are not
61			* supported, and the following are offered instead:
62			*
63			* - "{$column}_query" Arguments to pass to a WP_Date_Query.
64			*
65			* @since 1.0.0
66			*
67			* @var array[]
68			*/
69			protected $columns = array();
70
71			/**
72			* The slug of the meta type.
73			*
74			* If this is defined, the 'meta_query', 'meta_key', 'meta_value',
75			* 'meta_compare', and 'meta_type' args are supported, and will be passed to
76			* WP_Meta_Query.
77			*
78			* @since 1.0.0
79			*
80			* @var string
81			*/
82			protected $meta_type;
83
84			/**
85			* The default values for the query args.
86			*
87			* You can override this entirely if needed, or just modify it in your
88			* constructor before calling parent::__construct().
89			*
90			* @since 1.0.0
91			*
92			* @var array
93			*/
94			protected $defaults = array(
95			'start' => 0,
96			'order' => 'DESC',
97			);
98
99			/**
100			* The query arguments.
101			*
102			* @since 1.0.0
103			*
104			* @type array $args
105			*/
106			protected $args = array();
107
108			/**
109			* Whether the query is ready for execution, or still needs to be prepared.
110			*
111			* @since 1.0.0
112			*
113			* @type bool $is_query_ready
114			*/
115			protected $is_query_ready = false;
116
117			/**
118			* The SELECT statement for the query.
119			*
120			* @since 1.0.0
121			*
122			* @type string $select
123			*/
124			protected $select;
125
126			/**
127			* The SELECT COUNT statement for a count query.
128			*
129			* @since 1.0.0
130			*
131			* @type string $select_count
132			*/
133			protected $select_count = 'SELECT COUNT(*)';
134
135			/**
136			* The JOIN query with the meta table.
137			*
138			* @since 1.0.0
139			*
140			* @type string $meta_join
141			*/
142			protected $meta_join;
143
144			/**
145			* The WHERE clause for the query.
146			*
147			* @since 1.0.0
148			*
149			* @type string $where
150			*/
151			protected $where;
152
153			/**
154			* The array of conditions for the WHERE clause.
155			*
156			* @since 1.0.0
157			*
158			* @type array $wheres
159			*/
160			protected $wheres = array();
161
162			/**
163			* The LIMIT clause for the query.
164			*
165			* @since 1.0.0
166			*
167			* @type string $limit
168			*/
169			protected $limit;
170
171			/**
172			* The ORDER clause for the query.
173			*
174			* @since 1.0.0
175			*
176			* @type string $order
177			*/
178			protected $order;
179
180			/**
181			* Holds the meta query object when a meta query is being performed.
182			*
183			* @since 1.0.0
184			*
185			* @type WP_Meta_Query $meta_query
186			*/
187			protected $meta_query;
188
189			//
190			// Public Methods.
191			//
192
193			/**
194			* Construct the class.
195			*
196			* All of the arguments are expected not to be SQL escaped.
197			*
198			* @since 1.0.0
199			*
200			* @see WP_Meta_Query for the proper arguments for 'meta_query', 'meta_key', 'meta_value', 'meta_compare', and 'meta_type'.
201			*
202			* @param array $args {
203			* The arguments for the query.
204			*
205			* @type string\|array $fields Fields to include in the results.
206			* @type int $limit The maximum number of results to return. Default is null (no limit).
207			* @type int $start The start for the LIMIT clause. Default: 0.
208			* @type string $order_by The field to use to order the results. Default: 'date'.
209			* @type string $order The order for the query: ASC or DESC (default).
210			* @type string $meta_key See WP_Meta_Query.
211			* @type mixed $meta_value See WP_Meta_Query.
212			* @type string $meta_compare See WP_Meta_Query.
213			* @type string $meta_type See WP_Meta_Query.
214			* @type array $meta_query See WP_Meta_Query.
215			* }
216			*/
217			public function __construct( $args = array() ) {
218			$this->args = array_merge( $this->defaults, $args );
219			}
220
221			/**
222			* Get a query arg.
223			*
224			* @since 1.0.0
225			*
226			* @param string $arg The query arg whose value to retrieve.
227			*
228			* @return mixed\|null The query arg's value, or null if it isn't set.
229			*/
230			public function get_arg( $arg ) {
231
232			if ( isset( $this->args[ $arg ] ) ) {
233			return $this->args[ $arg ];
234			} else {
235			return null;
236			}
237			}
238
239			/**
240			* Set arguments for the query.
241			*
242			* All of the arguments supported by the constructor may be passed in here, and
243			* will be merged into the array of existing args.
244			*
245			* @since 1.0.0
246			*
247			* @param array $args A list of arguments to set and their values.
248			*/
249			public function set_args( array $args ) {
250
251			$this->args = array_merge( $this->args, $args );
252
253			$this->is_query_ready = false;
254			}
255
256			/**
257			* Count the number of results.
258			*
259			* When used with a query that contains a LIMIT clause, this method currently
260			* returns the count of the query ignoring the LIMIT, as would be the case with
261			* any similar query. However, this behaviour is not hardened and should not be
262			* relied upon. Make inquiry before assuming the constancy of this behaviour.
263			*
264			* @since 1.0.0
265			*
266			* @return int The number of results.
267			*/
268			public function count() {
269
270			global $wpdb;
271
272			$count = (int) $wpdb->get_var( $this->get_sql( 'SELECT COUNT' ) );
273
274			return $count;
275			}
276
277			/**
278			* Get the results for the query.
279			*
280			* @since 1.0.0
281			*
282			* @param string $method The method to use. Options are 'results', 'row', 'col',
283			* and 'var'.
284			*
285			* @return mixed The results of the query, or false on failure.
286			*/
287			public function get( $method = 'results' ) {
288
289			global $wpdb;
290
291			$methods = array( 'results', 'row', 'col', 'var' );
292
293			if ( ! in_array( $method, $methods ) ) {
294
295			_doing_it_wrong( __METHOD__, esc_html( sprintf( 'WordPoints Debug Error: invalid get method %s, possible values are %s', $method, implode( ', ', $methods ) ) ), '1.0.0' );
296
297			return false;
298			}
299
300			$result = $wpdb->{"get_{$method}"}( $this->get_sql() );
301
302			return $result;
303			}
304
305			/**
306			* Get the SQL for the query.
307			*
308			* This function can return the SQL for a SELECT or SELECT COUNT query. To
309			* specify which one to return, set the $select_type parameter. Defaults to
310			* SELECT.
311			*
312			* This function is public for debugging purposes.
313			*
314			* @since 1.0.0
315			*
316			* @param string $select_type The type of query, SELECT, or SELECT COUNT.
317			*
318			* @return string The SQL for the query.
319			*/
320			public function get_sql( $select_type = 'SELECT' ) {
321
322			$this->prepare_query();
323
324			$select = ( 'SELECT COUNT' === $select_type )
325			? $this->select_count
326			: $this->select;
327
328			return $select
329			. "\nFROM `{$this->table_name}`\n"
330			. $this->meta_join
331			. $this->where
332			. $this->order
333			. $this->limit;
334			}
335
336			//
337			// Filter Methods.
338			//
339
340			/**
341			* Filter date query valid columns for WP_Date_Query.
342			*
343			* @since 1.0.0
344			*
345			* @WordPress\filter date_query_valid_columns Added and subsequently removed by
346			* self::prepare_date_where().
347			*
348			* @param string[] $valid_columns The names of the valid columns for date queries.
349			*
350			* @return string[] The valid columns.
351			*/
352			public function date_query_valid_columns_filter( $valid_columns ) {
353
354			$valid_columns = array_merge(
355			$valid_columns
356			, array_keys(
357			wp_list_filter( $this->columns, array( 'is_date' => true ) )
358			)
359			);
360
361			return $valid_columns;
362			}
363
364			//
365			// Protected Methods.
366			//
367
368			/**
369			* Prepare the query.
370			*
371			* @since 1.0.0
372			*/
373			protected function prepare_query() {
374
375			if ( ! $this->is_query_ready ) {
376
377			$this->prepare_select();
378			$this->prepare_where();
379			$this->prepare_order_by();
380			$this->prepare_limit();
381
382			$this->is_query_ready = true;
383			}
384			}
385
386			/**
387			* Prepare the select statement.
388			*
389			* @since 1.0.0
390			*/
391			protected function prepare_select() {
392
393			$all_fields = array_keys( $this->columns );
394			$fields = array();
395
396			if ( ! empty( $this->args['fields'] ) ) {
397
398			$fields = (array) $this->args['fields'];
399			$diff = array_diff( $fields, $all_fields );
400			$fields = array_intersect( $all_fields, $fields );
401
402			if ( ! empty( $diff ) ) {
403			_doing_it_wrong( __METHOD__, esc_html( 'WordPoints Debug Error: invalid field(s) "' . implode( '", "', $diff ) . '" given' ), '1.0.0' );
404			}
405			}
406
407			// Pull all fields by default.
408			if ( empty( $fields ) ) {
409			$fields = $all_fields;
410			}
411
412			$fields = implode( ', ', array_map( 'wordpoints_escape_mysql_identifier', $fields ) );
413
414			$this->select = "SELECT {$fields}";
415			}
416
417			/**
418			* Validates a value against an array of sanitizing functions.
419			*
420			* @since 1.0.0
421			*
422			* @param mixed $value The value to validate.
423			* @param callable[] $validators The validators to validate it against.
424			*
425			* @return mixed The validated value, or false if invalid.
426			*/
427			protected function validate_value( $value, $validators ) {
428
429			foreach ( $validators as $validator ) {
430
431			$value = call_user_func_array( $validator, array( &$value ) );
432
433			if ( false === $value ) {
434			break;
435			}
436			}
437
438			return $value;
439			}
440
441			/**
442			* Validates an array of values against an array of sanitizing functions.
443			*
444			* @since 1.0.0
445			*
446			* @param array $values The values to validate.
447			* @param callable[] $validators The validators to validate each value against.
448			*
449			* @return array The validated values, with any invalid ones removed.
450			*/
451			protected function validate_values( $values, $validators ) {
452
453			foreach ( $values as $index => $value ) {
454
455			$value = $this->validate_value( $value, $validators );
456
457			if ( false === $value ) {
458			unset( $values[ $index ] );
459			}
460			}
461
462			return $values;
463			}
464
465			/**
466			* Validate an unsigned column.
467			*
468			* The value must be positive, zero-inclusive. We can't just use
469			* wordpoints_posint() because it is zero exclusive.
470			*
471			* @since 1.0.0
472			*
473			* @param mixed $value The value to validate.
474			*
475			* @return int\|false The validated value or false.
476			*/
477			protected function validate_unsigned_column( $value ) {
478
479			if ( false !== wordpoints_int( $value ) && $value >= 0 ) {
480			return $value;
481			}
482
483			return false;
484			}
485
486			/**
487			* Get an array of validating/sanitizing functions for the values of a column.
488			*
489			* @since 1.0.0
490			*
491			* @param array $data The data for the column.
492			*
493			* @return callable[] The validation functions.
494			*/
495			protected function get_validators_for_column( $data ) {
496
497			$validators = array();
498
499			// Default validators for integer columns.
500			if ( '%d' === $data['format'] ) {
501			if ( ! empty( $data['unsigned'] ) ) {
502			$validators[] = array( $this, 'validate_unsigned_column' );
503			} else {
504			$validators[] = 'wordpoints_int';
505			}
506			}
507
508			return $validators;
509			}
510
511			/**
512			* Prepare the conditions for the WHERE clause for a column.
513			*
514			* @since 1.0.0
515			*
516			* @param string $column The column name.
517			* @param array $data The column data.
518			*/
519			protected function prepare_column_where( $column, $data ) {
520
521			// If a single value has been supplied for the column, it takes precedence.
522			if ( isset( $this->args[ $column ] ) ) {
523			$this->prepare_column( $column, $data );
524			} elseif ( isset( $this->args[ "{$column}__in" ] ) ) {
525			$this->prepare_column__in( $column, $data );
526			} elseif ( isset( $this->args[ "{$column}__not_in" ] ) ) {
527			$this->prepare_column__in( $column, $data, 'NOT IN' );
528			}
529			}
530
531			/**
532			* Prepare a single-value condition for the WHERE clause for a column.
533			*
534			* @since 1.0.0
535			*
536			* @param string $column The name of the column
537			* @param array $data The column data.
538			*/
539			protected function prepare_column( $column, $data ) {
540
541			global $wpdb;
542
543			if (
544			isset( $data['values'] )
545			&& ! in_array( $this->args[ $column ], $data['values'], true )
546			) {
547			return;
548			}
549
550			$value = $this->validate_value(
551			$this->args[ $column ]
552			, $this->get_validators_for_column( $data )
553			);
554
555			if ( false === $value ) {
556			return;
557			}
558
559			$compare = $this->get_comparator_for_column( $column, $data );
560
561			$column = wordpoints_escape_mysql_identifier( $column );
562
563			$this->wheres[] = $wpdb->prepare(
564			"{$column} {$compare} {$data['format']}"
565			, $value
566			);
567			}
568
569			/**
570			* Get the comparator for a column.
571			*
572			* @since 1.0.0
573			*
574			* @param string $column The column name.
575			* @param array $data The column data.
576			*
577			* @return string The comparator for the column.
578			*/
579			protected function get_comparator_for_column( $column, $data ) {
580
581			$comparisons = array( '=', '<', '>', '<>', '!=', '<=', '>=' );
582
583			// MySQL doesn't support LIKE and NOT LIKE for int columns.
584			// See http://stackoverflow.com/q/8422455/1924128
585			if ( '%s' === $data['format'] ) {
586			$comparisons = array_merge( $comparisons, array( 'LIKE', 'NOT LIKE' ) );
587			}
588
589			$comparator = '=';
590
591			if (
592			isset( $this->args["{$column}__compare"] )
593			&& in_array( $this->args["{$column}__compare"], $comparisons, true )
594			) {
595			$comparator = $this->args["{$column}__compare"];
596			}
597
598			return $comparator;
599			}
600
601			/**
602			* Prepare the IN or NOT IN conditions for a column.
603			*
604			* @since 1.0.0
605			*
606			* @param string $column The name of the column.
607			* @param array $data The column data.
608			* @param string $type The type of IN clause, IN or NOT IN.
609			*/
610			protected function prepare_column__in( $column, $data, $type = 'IN' ) {
611
612			$key = "{$column}__" . strtolower( str_replace( ' ', '_', $type ) );
613
614			if ( empty( $this->args[ $key ] ) \|\| ! is_array( $this->args[ $key ] ) ) {
615			return;
616			}
617
618			$values = $this->args[ $key ];
619
620			if ( isset( $data['values'] ) ) {
621			$values = array_intersect( $values, $data['values'] );
622			} else {
623			$values = $this->validate_values(
624			$values
625			, $this->get_validators_for_column( $data )
626			);
627			}
628
629			if ( empty( $values ) ) {
630			return;
631			}
632
633			$in = wordpoints_prepare__in( $values, $data['format'] );
634
635			if ( false === $in ) {
636			return;
637			}
638
639			$column = wordpoints_escape_mysql_identifier( $column );
640
641			$this->wheres[] = "{$column} {$type} ({$in})";
642			}
643
644			/**
645			* Prepare the WHERE clause for the query.
646			*
647			* @since 1.0.0
648			*/
649			protected function prepare_where() {
650
651			$this->wheres = array();
652
653			foreach ( $this->columns as $column => $data ) {
654
655			if ( ! empty( $data['is_date'] ) ) {
656			$this->prepare_date_where( $column );
657			} else {
658			$this->prepare_column_where( $column, $data );
659			}
660			}
661
662			$this->prepare_meta_where();
663
664			if ( ! empty( $this->wheres ) ) {
665			$this->where = 'WHERE ' . implode( ' AND ', $this->wheres ) . "\n";
666			}
667			}
668
669			/**
670			* Prepare the LIMIT clause for the query.
671			*
672			* @since 1.0.0
673			*/
674			protected function prepare_limit() {
675
676			// MySQL doesn't allow for the offset without a limit, so if no limit is set
677			// we can ignore the start arg. See http://stackoverflow.com/a/271650/1924128
678			if ( ! isset( $this->args['limit'] ) ) {
679			return;
680			}
681
682			foreach ( array( 'limit', 'start' ) as $key ) {
683
684			// Save a backup of the arg value since wordpoints_int() is by reference.
685			$arg = $this->args[ $key ];
686
687			if ( false === wordpoints_int( $this->args[ $key ] ) ) {
688
689			_doing_it_wrong(
690			__METHOD__
691			, sprintf(
692			"WordPoints Debug Error: '%s' must be a positive integer, %s given"
693			, esc_html( $key )
694			, esc_html( strval( $arg ) ? $arg : gettype( $arg ) )
695			)
696			, '1.0.0'
697			);
698
699			$this->args[ $key ] = 0;
700			}
701			}
702
703			if ( $this->args['limit'] > 0 && $this->args['start'] >= 0 ) {
704			$this->limit = "LIMIT {$this->args['start']}, {$this->args['limit']}";
705			}
706			}
707
708			/**
709			* Prepare the ORDER BY clause for the query.
710			*
711			* @since 1.0.0
712			*/
713			protected function prepare_order_by() {
714
715			if ( empty( $this->args['order_by'] ) ) {
716			return;
717			}
718
719			$order = $this->args['order'];
720			$order_by = $this->args['order_by'];
721
722			if ( ! in_array( $order, array( 'DESC', 'ASC' ) ) ) {
723
724			_doing_it_wrong( __METHOD__, esc_html( "WordPoints Debug Error: invalid 'order' \"{$order}\", possible values are DESC and ASC" ), '1.0.0' );
725			$order = 'DESC';
726			}
727
728			if ( 'meta_value' === $order_by ) {
729
730			global $wpdb;
731
732			$meta_table_name = wordpoints_escape_mysql_identifier(
733			$wpdb->{"{$this->meta_type}meta"}
734			);
735
736			if ( isset( $this->args['meta_type'] ) ) {
737
738			$meta_type = $this->meta_query->get_cast_for_type( $this->args['meta_type'] );
739			$order_by = "CAST({$meta_table_name}.meta_value AS {$meta_type})";
740
741			} else {
742
743			$order_by = "{$meta_table_name}.meta_value";
744			}
745
746			} elseif ( isset( $this->columns[ $order_by ] ) ) {
747
748			$order_by = wordpoints_escape_mysql_identifier( $order_by );
749
750			} else {
751
752			_doing_it_wrong( __METHOD__, esc_html( "WordPoints Debug Error: invalid 'order_by' \"{$order_by}\", possible values are " . implode( ', ', array_keys( $this->columns ) ) ), '1.0.0' );
753			return;
754			}
755
756			$this->order = "ORDER BY {$order_by} {$order}\n";
757			}
758
759			/**
760			* Prepare the date query for a column.
761			*
762			* @since 1.0.0
763			*
764			* @param string $column The name of the column.
765			*/
766			protected function prepare_date_where( $column ) {
767
768			if (
769			empty( $this->args["{$column}_query"] )
770			\|\| ! is_array( $this->args["{$column}_query"] )
771			) {
772			return;
773			}
774
775			add_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_filter' ) );
776
777			$date_query = new WP_Date_Query( $this->args["{$column}_query"], $column );
778			$date_query = $date_query->get_sql();
779
780			if ( ! empty( $date_query ) ) {
781			$this->wheres[] = ltrim( $date_query, ' AND' );
782			}
783
784			remove_filter( 'date_query_valid_columns', array( $this, 'date_query_valid_columns_filter' ) );
785			}
786
787			/**
788			* Prepare the meta query.
789			*
790			* @since 1.0.0
791			*/
792			protected function prepare_meta_where() {
793
794			if ( empty( $this->meta_type ) ) {
795			return;
796			}
797
798			$meta_args = array_intersect_key(
799			$this->args
800			, array(
801			'meta_key' => '',
802			'meta_value' => '',
803			'meta_compare' => '',
804			'meta_type' => '',
805			'meta_query' => '',
806			)
807			);
808
809			if ( empty( $meta_args ) ) {
810			return;
811			}
812
813			$this->meta_query = new WP_Meta_Query();
814			$this->meta_query->parse_query_vars( $meta_args );
815
816			$meta_query = $this->meta_query->get_sql(
817			$this->meta_type
818			, $this->table_name
819			, 'id'
820			, $this
821			);
822
823			if ( ! empty( $meta_query['where'] ) ) {
824			$this->wheres[] = ltrim( $meta_query['where'], ' AND' );
825			}
826
827			$this->meta_join = $meta_query['join'] . "\n";
828			}
829			}
830
831			// EOF
832

WordPoints / hooks-api

Push — master ( b43b5b...dcfda7 )

WordPoints_DB_Query::prepare_meta_where() B

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like