BP_XProfile_Meta_Query

Class for generating SQL clauses that filter a primary query according to XProfile metadata keys and values.

Defined (1)

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

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