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: (Required) Query clause.
$parent_query: (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;
}

Expand full source code Collapse full source code

Changelog

Changelog
Version	Description
BuddyPress 2.2.0	Introduced.

Uses

Uses
Uses	Description
bp-loader.php: buddypress()	The main function responsible for returning the one true BuddyBoss Instance to functions everywhere.
bp-xprofile/bp-xprofile-functions.php: xprofile_get_field_id_from_name()	Returns the ID for the field based on the field name.
bp-xprofile/classes/class-bp-xprofile-query.php: BP_XProfile_Query::find_compatible_table_alias()	Identify an existing table alias that is compatible with the current query clause.
bp-xprofile/classes/class-bp-xprofile-query.php: BP_XProfile_Query::get_cast_for_type()	Return the appropriate alias for the given field type if applicable.
bp-core/bp-core-functions.php: bp_esc_like()	Escape special characters in a SQL LIKE clause.

Used By
Used By	Description
bp-xprofile/classes/class-bp-xprofile-query.php: BP_XProfile_Query::get_sql_for_query()	Generate SQL clauses for a single query array.

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.

Contents