BP_XProfile_Query

Class for generating SQL clauses to filter a user query by xprofile data.

Defined (1)

The class is defined in the following location(s).

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