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

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.