[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * BuddyPress XProfile Classes.
   4   *
   5   * @package BuddyPress
   6   * @subpackage XProfileClasses
   7   * @since 2.2.0
   8   */
   9  
  10  // Exit if accessed directly.
  11  defined( 'ABSPATH' ) || exit;
  12  
  13  /**
  14   * Class for generating SQL clauses to filter a user query by xprofile data.
  15   *
  16   * @since 2.2.0
  17   */
  18  class BP_XProfile_Query {
  19  
  20      /**
  21       * Array of xprofile queries.
  22       *
  23       * See {@see WP_XProfile_Query::__construct()} for information on parameters.
  24       *
  25       * @since 2.2.0
  26       * @var array
  27       */
  28      public $queries = array();
  29  
  30      /**
  31       * Database table that where the metadata's objects are stored (eg $wpdb->users).
  32       *
  33       * @since 2.2.0
  34       * @var string
  35       */
  36      public $primary_table;
  37  
  38      /**
  39       * Column in primary_table that represents the ID of the object.
  40       *
  41       * @since 2.2.0
  42       * @var string
  43       */
  44      public $primary_id_column;
  45  
  46      /**
  47       * A flat list of table aliases used in JOIN clauses.
  48       *
  49       * @since 2.2.0
  50       * @var array
  51       */
  52      protected $table_aliases = array();
  53  
  54      /**
  55       * Constructor.
  56       *
  57       * @since 2.2.0
  58       *
  59       * @param array $xprofile_query {
  60       *     Array of xprofile query clauses.
  61       *
  62       *     @type string $relation Optional. The MySQL keyword used to join the clauses of the query.
  63       *                            Accepts 'AND', or 'OR'. Default 'AND'.
  64       *     @type array {
  65       *         Optional. An array of first-order clause parameters, or another fully-formed xprofile query.
  66       *
  67       *         @type string|int $field   XProfile field to filter by. Accepts a field name or ID.
  68       *         @type string     $value   XProfile value to filter by.
  69       *         @type string     $compare MySQL operator used for comparing the $value. Accepts '=', '!=', '>',
  70       *                                   '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN',
  71       *                                   'NOT BETWEEN', 'REGEXP', 'NOT REGEXP', or 'RLIKE'. Default is 'IN'
  72       *                                   when `$value` is an array, '=' otherwise.
  73       *         @type string     $type    MySQL data type that the `value` column will be CAST to for comparisons.
  74       *                                   Accepts 'NUMERIC', 'BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL',
  75       *                                   'SIGNED', 'TIME', or 'UNSIGNED'. Default is 'CHAR'.
  76       *     }
  77       * }
  78       */
  79  	public function __construct( $xprofile_query ) {
  80          if ( empty( $xprofile_query ) ) {
  81              return;
  82          }
  83  
  84          $this->queries = $this->sanitize_query( $xprofile_query );
  85      }
  86  
  87      /**
  88       * Ensure the `xprofile_query` argument passed to the class constructor is well-formed.
  89       *
  90       * Eliminates empty items and ensures that a 'relation' is set.
  91       *
  92       * @since 2.2.0
  93       *
  94       * @param array $queries Array of query clauses.
  95       * @return array Sanitized array of query clauses.
  96       */
  97  	public function sanitize_query( $queries ) {
  98          $clean_queries = array();
  99  
 100          if ( ! is_array( $queries ) ) {
 101              return $clean_queries;
 102          }
 103  
 104          foreach ( $queries as $key => $query ) {
 105              if ( 'relation' === $key ) {
 106                  $relation = $query;
 107  
 108              } elseif ( ! is_array( $query ) ) {
 109                  continue;
 110  
 111              // First-order clause.
 112              } elseif ( $this->is_first_order_clause( $query ) ) {
 113                  if ( isset( $query['value'] ) && array() === $query['value'] ) {
 114                      unset( $query['value'] );
 115                  }
 116  
 117                  $clean_queries[] = $query;
 118  
 119              // Otherwise, it's a nested query, so we recurse.
 120              } else {
 121                  $cleaned_query = $this->sanitize_query( $query );
 122  
 123                  if ( ! empty( $cleaned_query ) ) {
 124                      $clean_queries[] = $cleaned_query;
 125                  }
 126              }
 127          }
 128  
 129          if ( empty( $clean_queries ) ) {
 130              return $clean_queries;
 131          }
 132  
 133          // Sanitize the 'relation' key provided in the query.
 134          if ( isset( $relation ) && 'OR' === strtoupper( $relation ) ) {
 135              $clean_queries['relation'] = 'OR';
 136  
 137          /*
 138           * If there is only a single clause, call the relation 'OR'.
 139           * This value will not actually be used to join clauses, but it
 140           * simplifies the logic around combining key-only queries.
 141           */
 142          } elseif ( 1 === count( $clean_queries ) ) {
 143              $clean_queries['relation'] = 'OR';
 144  
 145          // Default to AND.
 146          } else {
 147              $clean_queries['relation'] = 'AND';
 148          }
 149  
 150          return $clean_queries;
 151      }
 152  
 153      /**
 154       * Determine whether a query clause is first-order.
 155       *
 156       * A first-order query clause is one that has either a 'key' or a 'value' array key.
 157       *
 158       * @since 2.2.0
 159       *
 160       * @param  array $query XProfile query arguments.
 161       * @return bool  Whether the query clause is a first-order clause.
 162       */
 163  	protected function is_first_order_clause( $query ) {
 164          return isset( $query['field'] ) || isset( $query['value'] );
 165      }
 166  
 167      /**
 168       * Return the appropriate alias for the given field type if applicable.
 169       *
 170       * @since 2.2.0
 171       *
 172       * @param string $type MySQL type to cast `value`.
 173       * @return string MySQL type.
 174       */
 175  	public function get_cast_for_type( $type = '' ) {
 176          if ( empty( $type ) ) {
 177              return 'CHAR';
 178          }
 179  
 180          $meta_type = strtoupper( $type );
 181  
 182          if ( ! preg_match( '/^(?:BINARY|CHAR|DATE|DATETIME|SIGNED|UNSIGNED|TIME|NUMERIC(?:\(\d+(?:,\s?\d+)?\))?|DECIMAL(?:\(\d+(?:,\s?\d+)?\))?)$/', $meta_type ) ) {
 183              return 'CHAR';
 184          }
 185  
 186          if ( 'NUMERIC' === $meta_type ) {
 187              $meta_type = 'SIGNED';
 188          }
 189  
 190          return $meta_type;
 191      }
 192  
 193      /**
 194       * Generate SQL clauses to be appended to a main query.
 195       *
 196       * Called by the public {@see BP_XProfile_Query::get_sql()}, this method is abstracted out to maintain parity
 197       * with WP's Query classes.
 198       *
 199       * @since 2.2.0
 200       *
 201       * @return array {
 202       *     Array containing JOIN and WHERE SQL clauses to append to the main query.
 203       *
 204       *     @type string $join  SQL fragment to append to the main JOIN clause.
 205       *     @type string $where SQL fragment to append to the main WHERE clause.
 206       * }
 207       */
 208  	protected function get_sql_clauses() {
 209          /*
 210           * $queries are passed by reference to get_sql_for_query() for recursion.
 211           * To keep $this->queries unaltered, pass a copy.
 212           */
 213          $queries = $this->queries;
 214          $sql = $this->get_sql_for_query( $queries );
 215  
 216          if ( ! empty( $sql['where'] ) ) {
 217              $sql['where'] = ' AND ' . $sql['where'];
 218          }
 219  
 220          return $sql;
 221      }
 222  
 223      /**
 224       * Generate SQL clauses for a single query array.
 225       *
 226       * If nested subqueries are found, this method recurses the tree to produce the properly nested SQL.
 227       *
 228       * @since 2.2.0
 229       *
 230       * @param  array $query Query to parse. Passed by reference.
 231       * @param  int   $depth Optional. Number of tree levels deep we currently are. Used to calculate indentation.
 232       * @return array {
 233       *     Array containing JOIN and WHERE SQL clauses to append to a single query array.
 234       *
 235       *     @type string $join  SQL fragment to append to the main JOIN clause.
 236       *     @type string $where SQL fragment to append to the main WHERE clause.
 237       * }
 238       */
 239  	protected function get_sql_for_query( &$query, $depth = 0 ) {
 240          $sql_chunks = array(
 241              'join'  => array(),
 242              'where' => array(),
 243          );
 244  
 245          $sql = array(
 246              'join'  => '',
 247              'where' => '',
 248          );
 249  
 250          $indent = '';
 251          for ( $i = 0; $i < $depth; $i++ ) {
 252              $indent .= "  ";
 253          }
 254  
 255          foreach ( $query as $key => &$clause ) {
 256              if ( 'relation' === $key ) {
 257                  $relation = $query['relation'];
 258              } elseif ( is_array( $clause ) ) {
 259  
 260                  // This is a first-order clause.
 261                  if ( $this->is_first_order_clause( $clause ) ) {
 262                      $clause_sql = $this->get_sql_for_clause( $clause, $query );
 263  
 264                      $where_count = count( $clause_sql['where'] );
 265                      if ( ! $where_count ) {
 266                          $sql_chunks['where'][] = '';
 267                      } elseif ( 1 === $where_count ) {
 268                          $sql_chunks['where'][] = $clause_sql['where'][0];
 269                      } else {
 270                          $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
 271                      }
 272  
 273                      $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
 274                  // This is a subquery, so we recurse.
 275                  } else {
 276                      $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );
 277  
 278                      $sql_chunks['where'][] = $clause_sql['where'];
 279                      $sql_chunks['join'][]  = $clause_sql['join'];
 280                  }
 281              }
 282          }
 283  
 284          // Filter to remove empties.
 285          $sql_chunks['join']  = array_filter( $sql_chunks['join'] );
 286          $sql_chunks['where'] = array_filter( $sql_chunks['where'] );
 287  
 288          if ( empty( $relation ) ) {
 289              $relation = 'AND';
 290          }
 291  
 292          // Filter duplicate JOIN clauses and combine into a single string.
 293          if ( ! empty( $sql_chunks['join'] ) ) {
 294              $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
 295          }
 296  
 297          // Generate a single WHERE clause with proper brackets and indentation.
 298          if ( ! empty( $sql_chunks['where'] ) ) {
 299              $sql['where'] = '( ' . "\n  " . $indent . implode( ' ' . "\n  " . $indent . $relation . ' ' . "\n  " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
 300          }
 301  
 302          return $sql;
 303      }
 304  
 305      /**
 306       * Generates SQL clauses to be appended to a main query.
 307       *
 308       * @since 2.2.0
 309       *
 310       * @param string $primary_table     Database table where the object being filtered is stored (eg wp_users).
 311       * @param string $primary_id_column ID column for the filtered object in $primary_table.
 312       * @return array {
 313       *     Array containing JOIN and WHERE SQL clauses to append to the main query.
 314       *
 315       *     @type string $join  SQL fragment to append to the main JOIN clause.
 316       *     @type string $where SQL fragment to append to the main WHERE clause.
 317       * }
 318       */
 319  	public function get_sql( $primary_table, $primary_id_column ) {
 320  
 321          $this->primary_table     = $primary_table;
 322          $this->primary_id_column = $primary_id_column;
 323  
 324          $sql = $this->get_sql_clauses();
 325  
 326          /*
 327           * If any JOINs are LEFT JOINs (as in the case of NOT EXISTS), then all JOINs should
 328           * be LEFT. Otherwise posts with no metadata will be excluded from results.
 329           */
 330          if ( false !== strpos( $sql['join'], 'LEFT JOIN' ) ) {
 331              $sql['join'] = str_replace( 'INNER JOIN', 'LEFT JOIN', $sql['join'] );
 332          }
 333  
 334          return $sql;
 335      }
 336  
 337      /**
 338       * Generate SQL JOIN and WHERE clauses for a first-order query clause.
 339       *
 340       * "First-order" means that it's an array with a 'field' or 'value'.
 341       *
 342       * @since 2.2.0
 343       *
 344       * @param array $clause       Query clause.
 345       * @param array $parent_query Parent query array.
 346       * @return array {
 347       *     Array containing JOIN and WHERE SQL clauses to append to a first-order query.
 348       *
 349       *     @type string $join  SQL fragment to append to the main JOIN clause.
 350       *     @type string $where SQL fragment to append to the main WHERE clause.
 351       * }
 352       */
 353  	public function get_sql_for_clause( &$clause, $parent_query ) {
 354          global $wpdb;
 355  
 356          $sql_chunks = array(
 357              'where' => array(),
 358              'join'  => array(),
 359          );
 360  
 361          if ( isset( $clause['compare'] ) ) {
 362              $clause['compare'] = strtoupper( $clause['compare'] );
 363          } else {
 364              $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
 365          }
 366  
 367          if ( ! in_array( $clause['compare'], array(
 368              '=', '!=', '>', '>=', '<', '<=',
 369              'LIKE', 'NOT LIKE',
 370              'IN', 'NOT IN',
 371              'BETWEEN', 'NOT BETWEEN',
 372              'EXISTS', 'NOT EXISTS',
 373              'REGEXP', 'NOT REGEXP', 'RLIKE'
 374          ) ) ) {
 375              $clause['compare'] = '=';
 376          }
 377  
 378          $field_compare = $clause['compare'];
 379  
 380          // First build the JOIN clause, if one is required.
 381          $join = '';
 382  
 383          $data_table = buddypress()->profile->table_name_data;
 384  
 385          // We prefer to avoid joins if possible. Look for an existing join compatible with this clause.
 386          $alias = $this->find_compatible_table_alias( $clause, $parent_query );
 387          if ( false === $alias ) {
 388              $i = count( $this->table_aliases );
 389              $alias = $i ? 'xpq' . $i : $data_table;
 390  
 391              // JOIN clauses for NOT EXISTS have their own syntax.
 392              if ( 'NOT EXISTS' === $field_compare ) {
 393                  $join .= " LEFT JOIN $data_table";
 394                  $join .= $i ? " AS $alias" : '';
 395                  $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.user_id AND $alias.field_id = %d )", $clause['field'] );
 396  
 397              // All other JOIN clauses.
 398              } else {
 399                  $join .= " INNER JOIN $data_table";
 400                  $join .= $i ? " AS $alias" : '';
 401                  $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.user_id )";
 402              }
 403  
 404              $this->table_aliases[] = $alias;
 405              $sql_chunks['join'][] = $join;
 406          }
 407  
 408          // Save the alias to this clause, for future siblings to find.
 409          $clause['alias'] = $alias;
 410  
 411          // Next, build the WHERE clause.
 412          $where = '';
 413  
 414          // Field_id.
 415          if ( array_key_exists( 'field', $clause ) ) {
 416              // Convert field name to ID if necessary.
 417              if ( ! is_numeric( $clause['field'] ) ) {
 418                  $clause['field'] = xprofile_get_field_id_from_name( $clause['field'] );
 419              }
 420  
 421              // NOT EXISTS has its own syntax.
 422              if ( 'NOT EXISTS' === $field_compare ) {
 423                  $sql_chunks['where'][] = $alias . '.user_id IS NULL';
 424              } else {
 425                  $sql_chunks['where'][] = $wpdb->prepare( "$alias.field_id = %d", $clause['field'] );
 426              }
 427          }
 428  
 429          // Value.
 430          if ( array_key_exists( 'value', $clause ) ) {
 431              $field_value = $clause['value'];
 432              $field_type = $this->get_cast_for_type( isset( $clause['type'] ) ? $clause['type'] : '' );
 433  
 434              if ( in_array( $field_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
 435                  if ( ! is_array( $field_value ) ) {
 436                      $field_value = preg_split( '/[,\s]+/', $field_value );
 437                  }
 438              } else {
 439                  $field_value = trim( $field_value );
 440              }
 441  
 442              switch ( $field_compare ) {
 443                  case 'IN' :
 444                  case 'NOT IN' :
 445                      $field_compare_string = '(' . substr( str_repeat( ',%s', count( $field_value ) ), 1 ) . ')';
 446                      $where = $wpdb->prepare( $field_compare_string, $field_value );
 447                      break;
 448  
 449                  case 'BETWEEN' :
 450                  case 'NOT BETWEEN' :
 451                      $field_value = array_slice( $field_value, 0, 2 );
 452                      $where = $wpdb->prepare( '%s AND %s', $field_value );
 453                      break;
 454  
 455                  case 'LIKE' :
 456                  case 'NOT LIKE' :
 457                      $field_value = '%' . bp_esc_like( $field_value ) . '%';
 458                      $where = $wpdb->prepare( '%s', $field_value );
 459                      break;
 460  
 461                  default :
 462                      $where = $wpdb->prepare( '%s', $field_value );
 463                      break;
 464  
 465              }
 466  
 467              if ( $where ) {
 468                  $sql_chunks['where'][] = "CAST($alias.value AS {$field_type}) {$field_compare} {$where}";
 469              }
 470          }
 471  
 472          /*
 473           * Multiple WHERE clauses (`field` and `value` pairs) should be joined in parentheses.
 474           */
 475          if ( 1 < count( $sql_chunks['where'] ) ) {
 476              $sql_chunks['where'] = array( '( ' . implode( ' AND ', $sql_chunks['where'] ) . ' )' );
 477          }
 478  
 479          return $sql_chunks;
 480      }
 481  
 482      /**
 483       * Identify an existing table alias that is compatible with the current query clause.
 484       *
 485       * We avoid unnecessary table joins by allowing each clause to look for an existing table alias that is
 486       * compatible with the query that it needs to perform. An existing alias is compatible if (a) it is a
 487       * sibling of $clause (ie, it's under the scope of the same relation), and (b) the combination of
 488       * operator and relation between the clauses allows for a shared table join. In the case of BP_XProfile_Query,
 489       * this * only applies to IN clauses that are connected by the relation OR.
 490       *
 491       * @since 2.2.0
 492       *
 493       * @param array $clause       Query clause.
 494       * @param array $parent_query Parent query of $clause.
 495       * @return string|bool Table alias if found, otherwise false.
 496       */
 497  	protected function find_compatible_table_alias( $clause, $parent_query ) {
 498          $alias = false;
 499  
 500          foreach ( $parent_query as $sibling ) {
 501              // If the sibling has no alias yet, there's nothing to check.
 502              if ( empty( $sibling['alias'] ) ) {
 503                  continue;
 504              }
 505  
 506              // We're only interested in siblings that are first-order clauses.
 507              if ( ! is_array( $sibling ) || ! $this->is_first_order_clause( $sibling ) ) {
 508                  continue;
 509              }
 510  
 511              $compatible_compares = array();
 512  
 513              // Clauses connected by OR can share joins as long as they have "positive" operators.
 514              if ( 'OR' === $parent_query['relation'] ) {
 515                  $compatible_compares = array( '=', 'IN', 'BETWEEN', 'LIKE', 'REGEXP', 'RLIKE', '>', '>=', '<', '<=' );
 516  
 517              // Clauses joined by AND with "negative" operators share a join only if they also share a key.
 518              } elseif ( isset( $sibling['field'] ) && isset( $clause['field'] ) && $sibling['field'] === $clause['field'] ) {
 519                  $compatible_compares = array( '!=', 'NOT IN', 'NOT LIKE' );
 520              }
 521  
 522              $clause_compare  = strtoupper( $clause['compare'] );
 523              $sibling_compare = strtoupper( $sibling['compare'] );
 524              if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) {
 525                  $alias = $sibling['alias'];
 526                  break;
 527              }
 528          }
 529  
 530          return $alias;
 531      }
 532  }