BP_XProfile_Query::get_sql_for_clause( array $clause, array $parent_query )

	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;
	}