BP_XProfile_Query::get_sql_for_clause( array $clause, array $parent_query )
Generate SQL JOIN and WHERE clauses for a first-order query clause.
Description
"First-order" means that it’s an array with a ‘field’ or ‘value’.
Parameters
- $clause
-
(array) (Required) Query clause.
- $parent_query
-
(array) (Required) Parent query array.
Return
(array) Array containing JOIN and WHERE SQL clauses to append to a first-order query.
- 'join'
(string) SQL fragment to append to the main JOIN clause. - 'where'
(string) SQL fragment to append to the main WHERE clause.
Source
File: bp-xprofile/classes/class-bp-xprofile-query.php
public function get_sql_for_clause( &$clause, $parent_query ) { global $wpdb; $sql_chunks = array( 'where' => array(), 'join' => array(), ); if ( isset( $clause['compare'] ) ) { $clause['compare'] = strtoupper( $clause['compare'] ); } else { $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '='; } if ( ! in_array( $clause['compare'], array( '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'EXISTS', 'NOT EXISTS', 'REGEXP', 'NOT REGEXP', 'RLIKE' ) ) ) { $clause['compare'] = '='; } $field_compare = $clause['compare']; // First build the JOIN clause, if one is required. $join = ''; $data_table = buddypress()->profile->table_name_data; // We prefer to avoid joins if possible. Look for an existing join compatible with this clause. $alias = $this->find_compatible_table_alias( $clause, $parent_query ); if ( false === $alias ) { $i = count( $this->table_aliases ); $alias = $i ? 'xpq' . $i : $data_table; // JOIN clauses for NOT EXISTS have their own syntax. if ( 'NOT EXISTS' === $field_compare ) { $join .= " LEFT JOIN $data_table"; $join .= $i ? " AS $alias" : ''; $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.user_id AND $alias.field_id = %d )", $clause['field'] ); // All other JOIN clauses. } else { $join .= " INNER JOIN $data_table"; $join .= $i ? " AS $alias" : ''; $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.user_id )"; } $this->table_aliases[] = $alias; $sql_chunks['join'][] = $join; } // Save the alias to this clause, for future siblings to find. $clause['alias'] = $alias; // Next, build the WHERE clause. $where = ''; // Field_id. if ( array_key_exists( 'field', $clause ) ) { // Convert field name to ID if necessary. if ( ! is_numeric( $clause['field'] ) ) { $clause['field'] = xprofile_get_field_id_from_name( $clause['field'] ); } // NOT EXISTS has its own syntax. if ( 'NOT EXISTS' === $field_compare ) { $sql_chunks['where'][] = $alias . '.user_id IS NULL'; } else { $sql_chunks['where'][] = $wpdb->prepare( "$alias.field_id = %d", $clause['field'] ); } } // Value. if ( array_key_exists( 'value', $clause ) ) { $field_value = $clause['value']; $field_type = $this->get_cast_for_type( isset( $clause['type'] ) ? $clause['type'] : '' ); if ( in_array( $field_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) { if ( ! is_array( $field_value ) ) { $field_value = preg_split( '/[,\s]+/', $field_value ); } } else { $field_value = trim( $field_value ); } switch ( $field_compare ) { case 'IN' : case 'NOT IN' : $field_compare_string = '(' . substr( str_repeat( ',%s', count( $field_value ) ), 1 ) . ')'; $where = $wpdb->prepare( $field_compare_string, $field_value ); break; case 'BETWEEN' : case 'NOT BETWEEN' : $field_value = array_slice( $field_value, 0, 2 ); $where = $wpdb->prepare( '%s AND %s', $field_value ); break; case 'LIKE' : case 'NOT LIKE' : $field_value = '%' . bp_esc_like( $field_value ) . '%'; $where = $wpdb->prepare( '%s', $field_value ); break; default : $where = $wpdb->prepare( '%s', $field_value ); break; } if ( $where ) { $sql_chunks['where'][] = "CAST($alias.value AS {$field_type}) {$field_compare} {$where}"; } } /* * Multiple WHERE clauses (`field` and `value` pairs) should be joined in parentheses. */ if ( 1 < count( $sql_chunks['where'] ) ) { $sql_chunks['where'] = array( '( ' . implode( ' AND ', $sql_chunks['where'] ) . ' )' ); } return $sql_chunks; }
Changelog
Version | Description |
---|---|
BuddyPress 2.2.0 | Introduced. |
Questions?
We're always happy to help with code or other questions you might have! Search our developer docs, contact support, or connect with our sales team.