+ return $sql;
+ }
+
+ /**
+ * Generate SQL JOIN and WHERE clauses for a first-order query clause.
+ *
+ * "First-order" means that it's an array with a 'key' or 'value'.
+ *
+ * @since 4.1.0
+ * @access public
+ *
+ * @param array $clause Query clause, passed by reference.
+ * @param array $parent_query Parent query array.
+ * @param string $clause_key Optional. The array key used to name the clause in the original `$meta_query`
+ * parameters. If not provided, a key will be generated automatically.
+ * @return array {
+ * Array containing JOIN and WHERE SQL clauses to append to a first-order query.
+ *
+ * @type string $join SQL fragment to append to the main JOIN clause.
+ * @type string $where SQL fragment to append to the main WHERE clause.
+ * }
+ */
+ public function get_sql_for_clause( &$clause, $parent_query, $clause_key = '' ) {
+ global $wpdb;
+
+ $sql_chunks = array(
+ 'where' => array(),
+ 'join' => array(),
+ );
+
+ if ( isset( $clause['compare'] ) ) {
+ $clause['compare'] = strtoupper( $clause['compare'] );
+ } else {
+ $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'
+ ) ) ) {
+ $clause['compare'] = '=';
+ }
+
+ $meta_compare = $clause['compare'];
+
+ // First build the JOIN clause, if one is required.
+ $join = '';
+
+ // We prefer to avoid joins if possible. Look for an existing join compatible with this clause.
+ $alias = $this->find_compatible_table_alias( $clause, $parent_query );
+ if ( false === $alias ) {
+ $i = count( $this->table_aliases );
+ $alias = $i ? 'mt' . $i : $this->meta_table;
+
+ // JOIN clauses for NOT EXISTS have their own syntax.
+ if ( 'NOT EXISTS' === $meta_compare ) {
+ $join .= " LEFT JOIN $this->meta_table";
+ $join .= $i ? " AS $alias" : '';
+ $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] );
+
+ // All other JOIN clauses.
+ } else {
+ $join .= " INNER JOIN $this->meta_table";
+ $join .= $i ? " AS $alias" : '';
+ $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )";
+ }
+
+ $this->table_aliases[] = $alias;
+ $sql_chunks['join'][] = $join;
+ }
+
+ // Save the alias to this clause, for future siblings to find.
+ $clause['alias'] = $alias;
+
+ // Determine the data type.
+ $_meta_type = isset( $clause['type'] ) ? $clause['type'] : '';
+ $meta_type = $this->get_cast_for_type( $_meta_type );
+ $clause['cast'] = $meta_type;
+
+ // Fallback for clause keys is the table alias.
+ if ( ! $clause_key ) {
+ $clause_key = $clause['alias'];
+ }
+
+ // Ensure unique clause keys, so none are overwritten.
+ $iterator = 1;
+ $clause_key_base = $clause_key;
+ while ( isset( $this->clauses[ $clause_key ] ) ) {
+ $clause_key = $clause_key_base . '-' . $iterator;
+ $iterator++;
+ }
+
+ // Store the clause in our flat array.
+ $this->clauses[ $clause_key ] =& $clause;
+
+ // Next, build the WHERE clause.
+
+ // meta_key.
+ if ( array_key_exists( 'key', $clause ) ) {
+ if ( 'NOT EXISTS' === $meta_compare ) {
+ $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
+ } else {
+ $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) );
+ }
+ }
+
+ // meta_value.
+ if ( array_key_exists( 'value', $clause ) ) {
+ $meta_value = $clause['value'];