/bp-xprofile/classes/class-bp-xprofile-meta-query.php

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