--- a/wp/wp-includes/class-wp-meta-query.php Tue Oct 22 16:11:46 2019 +0200
+++ b/wp/wp-includes/class-wp-meta-query.php Tue Dec 15 13:49:49 2020 +0100
@@ -100,6 +100,8 @@
* @since 3.2.0
* @since 4.2.0 Introduced support for naming query clauses by associative array keys.
* @since 5.1.0 Introduced $compare_key clause parameter, which enables LIKE key matches.
+ * @since 5.3.0 Increased the number of operators available to $compare_key. Introduced $type_key,
+ * which enables the $key to be cast to a new data type for comparisons.
*
* @param array $meta_query {
* Array of meta query clauses. When first-order clauses or sub-clauses use strings as
@@ -107,12 +109,17 @@
*
* @type string $relation Optional. The MySQL keyword used to join
* the clauses of the query. Accepts 'AND', or 'OR'. Default 'AND'.
- * @type array {
+ * @type array ...$0 {
* Optional. An array of first-order clause parameters, or another fully-formed meta query.
*
* @type string $key Meta key to filter by.
- * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=' and 'LIKE'.
- * Default '='.
+ * @type string $compare_key MySQL operator used for comparing the $key. Accepts '=', '!='
+ * 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'REGEXP', 'NOT REGEXP', 'RLIKE',
+ * 'EXISTS' (alias of '=') or 'NOT EXISTS' (alias of '!=').
+ * Default is 'IN' when `$key` is an array, '=' otherwise.
+ * @type string $type_key MySQL data type that the meta_key column will be CAST to for
+ * comparisons. Accepts 'BINARY' for case-sensitive regular expression
+ * comparisons. Default is ''.
* @type string $value Meta value to filter by.
* @type string $compare MySQL operator used for comparing the $value. Accepts '=',
* '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE',
@@ -131,7 +138,7 @@
return;
}
- if ( isset( $meta_query['relation'] ) && strtoupper( $meta_query['relation'] ) == 'OR' ) {
+ if ( isset( $meta_query['relation'] ) && 'OR' === strtoupper( $meta_query['relation'] ) ) {
$this->relation = 'OR';
} else {
$this->relation = 'AND';
@@ -239,7 +246,7 @@
* the rest of the meta_query).
*/
$primary_meta_query = array();
- foreach ( array( 'key', 'compare', 'type', 'compare_key' ) as $key ) {
+ foreach ( array( 'key', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) {
if ( ! empty( $qv[ "meta_$key" ] ) ) {
$primary_meta_query[ $key ] = $qv[ "meta_$key" ];
}
@@ -288,7 +295,7 @@
return 'CHAR';
}
- if ( 'NUMERIC' == $meta_type ) {
+ if ( 'NUMERIC' === $meta_type ) {
$meta_type = 'SIGNED';
}
@@ -304,7 +311,7 @@
* @param string $primary_table Database table where the object being filtered is stored (eg wp_users).
* @param string $primary_id_column ID column for the filtered object in $primary_table.
* @param object $context Optional. The main query object.
- * @return false|array {
+ * @return array|false {
* Array containing JOIN and WHERE SQL clauses to append to the main query.
*
* @type string $join SQL fragment to append to the main JOIN clause.
@@ -312,7 +319,8 @@
* }
*/
public function get_sql( $type, $primary_table, $primary_id_column, $context = null ) {
- if ( ! $meta_table = _get_meta_table( $type ) ) {
+ $meta_table = _get_meta_table( $type );
+ if ( ! $meta_table ) {
return false;
}
@@ -497,34 +505,40 @@
$clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
}
- if ( ! in_array(
- $clause['compare'],
- array(
- '=',
- '!=',
- '>',
- '>=',
- '<',
- '<=',
- 'LIKE',
- 'NOT LIKE',
- 'IN',
- 'NOT IN',
- 'BETWEEN',
- 'NOT BETWEEN',
- 'EXISTS',
- 'NOT EXISTS',
- 'REGEXP',
- 'NOT REGEXP',
- 'RLIKE',
- )
- ) ) {
+ $non_numeric_operators = array(
+ '=',
+ '!=',
+ 'LIKE',
+ 'NOT LIKE',
+ 'IN',
+ 'NOT IN',
+ 'EXISTS',
+ 'NOT EXISTS',
+ 'RLIKE',
+ 'REGEXP',
+ 'NOT REGEXP',
+ );
+
+ $numeric_operators = array(
+ '>',
+ '>=',
+ '<',
+ '<=',
+ 'BETWEEN',
+ 'NOT BETWEEN',
+ );
+
+ if ( ! in_array( $clause['compare'], $non_numeric_operators, true ) && ! in_array( $clause['compare'], $numeric_operators, true ) ) {
$clause['compare'] = '=';
}
- if ( isset( $clause['compare_key'] ) && 'LIKE' === strtoupper( $clause['compare_key'] ) ) {
+ if ( isset( $clause['compare_key'] ) ) {
$clause['compare_key'] = strtoupper( $clause['compare_key'] );
} else {
+ $clause['compare_key'] = isset( $clause['key'] ) && is_array( $clause['key'] ) ? 'IN' : '=';
+ }
+
+ if ( ! in_array( $clause['compare_key'], $non_numeric_operators, true ) ) {
$clause['compare_key'] = '=';
}
@@ -593,11 +607,79 @@
if ( 'NOT EXISTS' === $meta_compare ) {
$sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
} else {
- if ( 'LIKE' === $meta_compare_key ) {
- $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key LIKE %s", '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%' );
- } else {
- $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) );
+ /**
+ * In joined clauses negative operators have to be nested into a
+ * NOT EXISTS clause and flipped, to avoid returning records with
+ * matching post IDs but different meta keys. Here we prepare the
+ * nested clause.
+ */
+ if ( in_array( $meta_compare_key, array( '!=', 'NOT IN', 'NOT LIKE', 'NOT EXISTS', 'NOT REGEXP' ), true ) ) {
+ // Negative clauses may be reused.
+ $i = count( $this->table_aliases );
+ $subquery_alias = $i ? 'mt' . $i : $this->meta_table;
+ $this->table_aliases[] = $subquery_alias;
+
+ $meta_compare_string_start = 'NOT EXISTS (';
+ $meta_compare_string_start .= "SELECT 1 FROM $wpdb->postmeta $subquery_alias ";
+ $meta_compare_string_start .= "WHERE $subquery_alias.post_ID = $alias.post_ID ";
+ $meta_compare_string_end = 'LIMIT 1';
+ $meta_compare_string_end .= ')';
}
+
+ switch ( $meta_compare_key ) {
+ case '=':
+ case 'EXISTS':
+ $where = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ break;
+ case 'LIKE':
+ $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%';
+ $where = $wpdb->prepare( "$alias.meta_key LIKE %s", $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ break;
+ case 'IN':
+ $meta_compare_string = "$alias.meta_key IN (" . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ')';
+ $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ break;
+ case 'RLIKE':
+ case 'REGEXP':
+ $operator = $meta_compare_key;
+ if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
+ $cast = 'BINARY';
+ } else {
+ $cast = '';
+ }
+ $where = $wpdb->prepare( "$alias.meta_key $operator $cast %s", trim( $clause['key'] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ break;
+
+ case '!=':
+ case 'NOT EXISTS':
+ $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key = %s " . $meta_compare_string_end;
+ $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ break;
+ case 'NOT LIKE':
+ $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key LIKE %s " . $meta_compare_string_end;
+
+ $meta_compare_value = '%' . $wpdb->esc_like( trim( $clause['key'] ) ) . '%';
+ $where = $wpdb->prepare( $meta_compare_string, $meta_compare_value ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ break;
+ case 'NOT IN':
+ $array_subclause = '(' . substr( str_repeat( ',%s', count( $clause['key'] ) ), 1 ) . ') ';
+ $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key IN " . $array_subclause . $meta_compare_string_end;
+ $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ break;
+ case 'NOT REGEXP':
+ $operator = $meta_compare_key;
+ if ( isset( $clause['type_key'] ) && 'BINARY' === strtoupper( $clause['type_key'] ) ) {
+ $cast = 'BINARY';
+ } else {
+ $cast = '';
+ }
+
+ $meta_compare_string = $meta_compare_string_start . "AND $subquery_alias.meta_key REGEXP $cast %s " . $meta_compare_string_end;
+ $where = $wpdb->prepare( $meta_compare_string, $clause['key'] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ break;
+ }
+
+ $sql_chunks['where'][] = $where;
}
}
@@ -605,7 +687,7 @@
if ( array_key_exists( 'value', $clause ) ) {
$meta_value = $clause['value'];
- if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
+ if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ), true ) ) {
if ( ! is_array( $meta_value ) ) {
$meta_value = preg_split( '/[,\s]+/', $meta_value );
}
@@ -622,8 +704,7 @@
case 'BETWEEN':
case 'NOT BETWEEN':
- $meta_value = array_slice( $meta_value, 0, 2 );
- $where = $wpdb->prepare( '%s AND %s', $meta_value );
+ $where = $wpdb->prepare( '%s AND %s', $meta_value[0], $meta_value[1] );
break;
case 'LIKE':
@@ -699,9 +780,9 @@
*
* @since 4.1.0
*
- * @param array $clause Query clause.
- * @param array $parent_query Parent query of $clause.
- * @return string|bool Table alias if found, otherwise false.
+ * @param array $clause Query clause.
+ * @param array $parent_query Parent query of $clause.
+ * @return string|false Table alias if found, otherwise false.
*/
protected function find_compatible_table_alias( $clause, $parent_query ) {
$alias = false;
@@ -730,7 +811,7 @@
$clause_compare = strtoupper( $clause['compare'] );
$sibling_compare = strtoupper( $sibling['compare'] );
- if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) {
+ if ( in_array( $clause_compare, $compatible_compares, true ) && in_array( $sibling_compare, $compatible_compares, true ) ) {
$alias = $sibling['alias'];
break;
}
@@ -741,10 +822,10 @@
*
* @since 4.1.0
*
- * @param string|bool $alias Table alias, or false if none was found.
- * @param array $clause First-order query clause.
- * @param array $parent_query Parent of $clause.
- * @param object $this WP_Meta_Query object.
+ * @param string|bool $alias Table alias, or false if none was found.
+ * @param array $clause First-order query clause.
+ * @param array $parent_query Parent of $clause.
+ * @param WP_Meta_Query $this WP_Meta_Query object.
*/
return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this );
}