[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * BuddyPress XProfile Classes.
   4   *
   5   * @package BuddyPress
   6   * @subpackage XProfileClasses
   7   * @since 2.3.0
   8   */
   9  
  10  // Exit if accessed directly.
  11  defined( 'ABSPATH' ) || exit;
  12  
  13  /**
  14   * Class for generating SQL clauses that filter a primary query according to
  15   * XProfile metadata keys and values.
  16   *
  17   * `BP_XProfile_Meta_Query` is a helper that allows primary query classes, such
  18   * as {@see WP_Query} and {@see WP_User_Query}, to filter their results by object
  19   * metadata, by generating `JOIN` and `WHERE` subclauses to be attached
  20   * to the primary SQL query string.
  21   *
  22   * @since 2.3.0
  23   */
  24  class BP_XProfile_Meta_Query extends WP_Meta_Query {
  25  
  26      /**
  27       * Determine whether a query clause is first-order.
  28       *
  29       * A first-order meta query clause is one that has either a 'key', 'value',
  30       * or 'object' array key.
  31       *
  32       * @since 2.3.0
  33       *
  34       * @param array $query Meta query arguments.
  35       * @return bool Whether the query clause is a first-order clause.
  36       */
  37  	protected function is_first_order_clause( $query ) {
  38          return isset( $query['key'] ) || isset( $query['value'] ) || isset( $query['object'] );
  39      }
  40  
  41      /**
  42       * Constructs a meta query based on 'meta_*' query vars.
  43       *
  44       * @since 2.3.0
  45       *
  46       * @param array $qv The query variables.
  47       */
  48  	public function parse_query_vars( $qv ) {
  49          $meta_query = array();
  50  
  51          /*
  52           * For orderby=meta_value to work correctly, simple query needs to be
  53           * first (so that its table join is against an unaliased meta table) and
  54           * needs to be its own clause (so it doesn't interfere with the logic of
  55           * the rest of the meta_query).
  56           */
  57          $primary_meta_query = array();
  58          foreach ( array( 'key', 'compare', 'type' ) as $key ) {
  59              if ( ! empty( $qv[ "meta_$key" ] ) ) {
  60                  $primary_meta_query[ $key ] = $qv[ "meta_$key" ];
  61              }
  62          }
  63  
  64          // WP_Query sets 'meta_value' = '' by default.
  65          if ( isset( $qv['meta_value'] ) && ( '' !== $qv['meta_value'] ) && ( ! is_array( $qv['meta_value'] ) || $qv['meta_value'] ) ) {
  66              $primary_meta_query['value'] = $qv['meta_value'];
  67          }
  68  
  69          // BP_XProfile_Query sets 'object_type' = '' by default.
  70          if ( isset( $qv[ 'object_type' ] ) && ( '' !== $qv[ 'object_type' ] ) && ( ! is_array( $qv[ 'object_type' ] ) || $qv[ 'object_type' ] ) ) {
  71              $meta_query[0]['object'] = $qv[ 'object_type' ];
  72          }
  73  
  74          $existing_meta_query = isset( $qv['meta_query'] ) && is_array( $qv['meta_query'] ) ? $qv['meta_query'] : array();
  75  
  76          if ( ! empty( $primary_meta_query ) && ! empty( $existing_meta_query ) ) {
  77              $meta_query = array(
  78                  'relation' => 'AND',
  79                  $primary_meta_query,
  80                  $existing_meta_query,
  81              );
  82          } elseif ( ! empty( $primary_meta_query ) ) {
  83              $meta_query = array(
  84                  $primary_meta_query,
  85              );
  86          } elseif ( ! empty( $existing_meta_query ) ) {
  87              $meta_query = $existing_meta_query;
  88          }
  89  
  90          $this->__construct( $meta_query );
  91      }
  92  
  93      /**
  94       * Generates SQL clauses to be appended to a main query.
  95       *
  96       * @since 2.3.0
  97       *
  98       * @param string      $type              Type of meta, eg 'user', 'post'.
  99       * @param string      $primary_table     Database table where the object being filtered is stored (eg wp_users).
 100       * @param string      $primary_id_column ID column for the filtered object in $primary_table.
 101       * @param object|null $context           Optional. The main query object.
 102       * @return array {
 103       *     Array containing JOIN and WHERE SQL clauses to append to the main query.
 104       *
 105       *     @type string $join  SQL fragment to append to the main JOIN clause.
 106       *     @type string $where SQL fragment to append to the main WHERE clause.
 107       * }
 108       */
 109  	public function get_sql( $type, $primary_table, $primary_id_column, $context = null ) {
 110          if ( ! $meta_table = _get_meta_table( $type ) ) {
 111              return false;
 112          }
 113  
 114          $this->meta_table     = $meta_table;
 115          $this->meta_id_column = 'object_id';
 116  
 117          $this->primary_table     = $primary_table;
 118          $this->primary_id_column = $primary_id_column;
 119  
 120          $sql = $this->get_sql_clauses();
 121  
 122          /*
 123           * If any JOINs are LEFT JOINs (as in the case of NOT EXISTS), then all JOINs should
 124           * be LEFT. Otherwise posts with no metadata will be excluded from results.
 125           */
 126          if ( false !== strpos( $sql['join'], 'LEFT JOIN' ) ) {
 127              $sql['join'] = str_replace( 'INNER JOIN', 'LEFT JOIN', $sql['join'] );
 128          }
 129  
 130          /**
 131           * Filter the meta query's generated SQL.
 132           *
 133           * @since 2.3.0
 134           *
 135           * @param array $args {
 136           *     An array of meta query SQL arguments.
 137           *
 138           *     @type array  $clauses           Array containing the query's JOIN and WHERE clauses.
 139           *     @type array  $queries           Array of meta queries.
 140           *     @type string $type              Type of meta.
 141           *     @type string $primary_table     Primary table.
 142           *     @type string $primary_id_column Primary column ID.
 143           *     @type object $context           The main query object.
 144           * }
 145           */
 146          return apply_filters_ref_array( 'bp_xprofile_get_meta_sql', array( $sql, $this->queries, $type, $primary_table, $primary_id_column, $context ) );
 147      }
 148  
 149      /**
 150       * Generate SQL JOIN and WHERE clauses for a first-order query clause.
 151       *
 152       * "First-order" means that it's an array with a 'key' or 'value'.
 153       *
 154       * @since 2.3.0
 155       *
 156       * @param array  $clause       Query clause, passed by reference.
 157       * @param array  $parent_query Parent query array.
 158       * @param string $clause_key   Optional. The array key used to name the clause in the original `$meta_query`
 159       *                             parameters. If not provided, a key will be generated automatically.
 160       * @return array {
 161       *     Array containing JOIN and WHERE SQL clauses to append to a first-order query.
 162       *
 163       *     @type string $join  SQL fragment to append to the main JOIN clause.
 164       *     @type string $where SQL fragment to append to the main WHERE clause.
 165       * }
 166       */
 167  	public function get_sql_for_clause( &$clause, $parent_query, $clause_key = '' ) {
 168          global $wpdb;
 169  
 170          $sql_chunks = array(
 171              'where' => array(),
 172              'join'  => array(),
 173          );
 174  
 175          if ( isset( $clause['compare'] ) ) {
 176              $clause['compare'] = strtoupper( $clause['compare'] );
 177          } else {
 178              $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
 179          }
 180  
 181          if ( ! in_array( $clause['compare'], array(
 182              '=', '!=', '>', '>=', '<', '<=',
 183              'LIKE', 'NOT LIKE',
 184              'IN', 'NOT IN',
 185              'BETWEEN', 'NOT BETWEEN',
 186              'EXISTS', 'NOT EXISTS',
 187              'REGEXP', 'NOT REGEXP', 'RLIKE'
 188          ) ) ) {
 189              $clause['compare'] = '=';
 190          }
 191  
 192          $meta_compare = $clause['compare'];
 193  
 194          // First build the JOIN clause, if one is required.
 195          $join = '';
 196  
 197          // We prefer to avoid joins if possible. Look for an existing join compatible with this clause.
 198          $alias = $this->find_compatible_table_alias( $clause, $parent_query );
 199          if ( false === $alias ) {
 200              $i = count( $this->table_aliases );
 201              $alias = $i ? 'mt' . $i : $this->meta_table;
 202  
 203              // JOIN clauses for NOT EXISTS have their own syntax.
 204              if ( 'NOT EXISTS' === $meta_compare ) {
 205                  $join .= " LEFT JOIN $this->meta_table";
 206                  $join .= $i ? " AS $alias" : '';
 207                  $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] );
 208  
 209              // All other JOIN clauses.
 210              } else {
 211                  $join .= " INNER JOIN $this->meta_table";
 212                  $join .= $i ? " AS $alias" : '';
 213                  $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )";
 214              }
 215  
 216              $this->table_aliases[] = $alias;
 217              $sql_chunks['join'][]  = $join;
 218          }
 219  
 220          // Save the alias to this clause, for future siblings to find.
 221          $clause['alias'] = $alias;
 222  
 223          // Determine the data type.
 224          $_meta_type     = isset( $clause['type'] ) ? $clause['type'] : '';
 225          $meta_type      = $this->get_cast_for_type( $_meta_type );
 226          $clause['cast'] = $meta_type;
 227  
 228          // Fallback for clause keys is the table alias.
 229          if ( ! $clause_key ) {
 230              $clause_key = $clause['alias'];
 231          }
 232  
 233          // Ensure unique clause keys, so none are overwritten.
 234          $iterator = 1;
 235          $clause_key_base = $clause_key;
 236          while ( isset( $this->clauses[ $clause_key ] ) ) {
 237              $clause_key = $clause_key_base . '-' . $iterator;
 238              $iterator++;
 239          }
 240  
 241          // Store the clause in our flat array.
 242          $this->clauses[ $clause_key ] =& $clause;
 243  
 244          // Next, build the WHERE clause.
 245          // Meta_key.
 246          if ( array_key_exists( 'key', $clause ) ) {
 247              if ( 'NOT EXISTS' === $meta_compare ) {
 248                  $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
 249              } else {
 250                  $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) );
 251              }
 252          }
 253  
 254          // Meta_value.
 255          if ( array_key_exists( 'value', $clause ) ) {
 256              $meta_value = $clause['value'];
 257  
 258              if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
 259                  if ( ! is_array( $meta_value ) ) {
 260                      $meta_value = preg_split( '/[,\s]+/', $meta_value );
 261                  }
 262              } else {
 263                  $meta_value = trim( $meta_value );
 264              }
 265  
 266              switch ( $meta_compare ) {
 267                  case 'IN' :
 268                  case 'NOT IN' :
 269                      $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $meta_value ) ), 1 ) . ')';
 270                      $where = $wpdb->prepare( $meta_compare_string, $meta_value );
 271                      break;
 272  
 273                  case 'BETWEEN' :
 274                  case 'NOT BETWEEN' :
 275                      $meta_value = array_slice( $meta_value, 0, 2 );
 276                      $where = $wpdb->prepare( '%s AND %s', $meta_value );
 277                      break;
 278  
 279                  case 'LIKE' :
 280                  case 'NOT LIKE' :
 281                      $meta_value = '%' . $wpdb->esc_like( $meta_value ) . '%';
 282                      $where = $wpdb->prepare( '%s', $meta_value );
 283                      break;
 284  
 285                  // EXISTS with a value is interpreted as '='.
 286                  case 'EXISTS' :
 287                      $meta_compare = '=';
 288                      $where = $wpdb->prepare( '%s', $meta_value );
 289                      break;
 290  
 291                  // 'value' is ignored for NOT EXISTS.
 292                  case 'NOT EXISTS' :
 293                      $where = '';
 294                      break;
 295  
 296                  default :
 297                      $where = $wpdb->prepare( '%s', $meta_value );
 298                      break;
 299  
 300              }
 301  
 302              if ( $where ) {
 303                  $sql_chunks['where'][] = "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$where}";
 304              }
 305          }
 306  
 307          // Object_type.
 308          if ( array_key_exists( 'object', $clause ) ) {
 309              $object_type = $clause['object'];
 310  
 311              if ( in_array( $meta_compare, array( 'IN', 'NOT IN' ) ) ) {
 312                  if ( ! is_array( $object_type ) ) {
 313                      $object_type = preg_split( '/[,\s]+/', $object_type );
 314                  }
 315              } else {
 316                  $object_type = trim( $object_type );
 317              }
 318  
 319              switch ( $meta_compare ) {
 320                  case 'IN' :
 321                  case 'NOT IN' :
 322                      $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $object_type ) ), 1 ) . ')';
 323                      $object_where        = $wpdb->prepare( $meta_compare_string, $object_type );
 324                      break;
 325  
 326                  case 'LIKE' :
 327                  case 'NOT LIKE' :
 328                      $object_type  = '%' . $wpdb->esc_like( $object_type ) . '%';
 329                      $object_where = $wpdb->prepare( '%s', $object_type );
 330                      break;
 331  
 332                  // EXISTS with a value is interpreted as '='.
 333                  case 'EXISTS' :
 334                      $meta_compare = '=';
 335                      $object_where = $wpdb->prepare( '%s', $object_type );
 336                      break;
 337  
 338                  // 'value' is ignored for NOT EXISTS.
 339                  case 'NOT EXISTS' :
 340                      $object_where = '';
 341                      break;
 342  
 343                  default :
 344                      $object_where = $wpdb->prepare( '%s', $object_type );
 345                      break;
 346              }
 347  
 348              if ( ! empty( $object_where ) ) {
 349                  $sql_chunks['where'][] = "{$alias}.object_type {$meta_compare} {$object_where}";
 350              }
 351          }
 352  
 353          /*
 354           * Multiple WHERE clauses (for meta_key and meta_value) should
 355           * be joined in parentheses.
 356           */
 357          if ( 1 < count( $sql_chunks['where'] ) ) {
 358              $sql_chunks['where'] = array( '( ' . implode( ' AND ', $sql_chunks['where'] ) . ' )' );
 359          }
 360  
 361          return $sql_chunks;
 362      }
 363  }