BP_XProfile_Meta_Query::get_sql_for_clause( array $clause, array $parent_query, string $clause_key = '' )

Generate SQL JOIN and WHERE clauses for a first-order query clause.

Description

"First-order" means that it’s an array with a ‘key’ or ‘value’.

Parameters

$clause

(Required) Query clause, passed by reference.

$parent_query

(Required) Parent query array.

$clause_key

(Optional) The array key used to name the clause in the original $meta_query parameters. If not provided, a key will be generated automatically.

Default value: ''

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-meta-query.php

public function get_sql_for_clause( &$clause, $parent_query, $clause_key = '' ) {
    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'] = '=';
    }
 
    $meta_compare = $clause['compare'];
 
    // First build the JOIN clause, if one is required.
    $join = '';
 
    // 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 ? 'mt' . $i : $this->meta_table;
 
        // JOIN clauses for NOT EXISTS have their own syntax.
        if ( 'NOT EXISTS' === $meta_compare ) {
            $join .= " LEFT JOIN $this->meta_table";
            $join .= $i ? " AS $alias" : '';
            $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] );
 
        // All other JOIN clauses.
        } else {
            $join .= " INNER JOIN $this->meta_table";
            $join .= $i ? " AS $alias" : '';
            $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )";
        }
 
        $this->table_aliases[] = $alias;
        $sql_chunks['join'][]  = $join;
    }
 
    // Save the alias to this clause, for future siblings to find.
    $clause['alias'] = $alias;
 
    // Determine the data type.
    $_meta_type     = isset( $clause['type'] ) ? $clause['type'] : '';
    $meta_type      = $this->get_cast_for_type( $_meta_type );
    $clause['cast'] = $meta_type;
 
    // Fallback for clause keys is the table alias.
    if ( ! $clause_key ) {
        $clause_key = $clause['alias'];
    }
 
    // Ensure unique clause keys, so none are overwritten.
    $iterator = 1;
    $clause_key_base = $clause_key;
    while ( isset( $this->clauses[ $clause_key ] ) ) {
        $clause_key = $clause_key_base . '-' . $iterator;
        $iterator++;
    }
 
    // Store the clause in our flat array.
    $this->clauses[ $clause_key ] =& $clause;
 
    // Next, build the WHERE clause.
    // Meta_key.
    if ( array_key_exists( 'key', $clause ) ) {
        if ( 'NOT EXISTS' === $meta_compare ) {
            $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
        } else {
            $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) );
        }
    }
 
    // Meta_value.
    if ( array_key_exists( 'value', $clause ) ) {
        $meta_value = $clause['value'];
 
        if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
            if ( ! is_array( $meta_value ) ) {
                $meta_value = preg_split( '/[,\s]+/', $meta_value );
            }
        } else {
            $meta_value = trim( $meta_value );
        }
 
        switch ( $meta_compare ) {
            case 'IN' :
            case 'NOT IN' :
                $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $meta_value ) ), 1 ) . ')';
                $where = $wpdb->prepare( $meta_compare_string, $meta_value );
                break;
 
            case 'BETWEEN' :
            case 'NOT BETWEEN' :
                $meta_value = array_slice( $meta_value, 0, 2 );
                $where = $wpdb->prepare( '%s AND %s', $meta_value );
                break;
 
            case 'LIKE' :
            case 'NOT LIKE' :
                $meta_value = '%' . $wpdb->esc_like( $meta_value ) . '%';
                $where = $wpdb->prepare( '%s', $meta_value );
                break;
 
            // EXISTS with a value is interpreted as '='.
            case 'EXISTS' :
                $meta_compare = '=';
                $where = $wpdb->prepare( '%s', $meta_value );
                break;
 
            // 'value' is ignored for NOT EXISTS.
            case 'NOT EXISTS' :
                $where = '';
                break;
 
            default :
                $where = $wpdb->prepare( '%s', $meta_value );
                break;
 
        }
 
        if ( $where ) {
            $sql_chunks['where'][] = "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$where}";
        }
    }
 
    // Object_type.
    if ( array_key_exists( 'object', $clause ) ) {
        $object_type = $clause['object'];
 
        if ( in_array( $meta_compare, array( 'IN', 'NOT IN' ) ) ) {
            if ( ! is_array( $object_type ) ) {
                $object_type = preg_split( '/[,\s]+/', $object_type );
            }
        } else {
            $object_type = trim( $object_type );
        }
 
        switch ( $meta_compare ) {
            case 'IN' :
            case 'NOT IN' :
                $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $object_type ) ), 1 ) . ')';
                $object_where        = $wpdb->prepare( $meta_compare_string, $object_type );
                break;
 
            case 'LIKE' :
            case 'NOT LIKE' :
                $object_type  = '%' . $wpdb->esc_like( $object_type ) . '%';
                $object_where = $wpdb->prepare( '%s', $object_type );
                break;
 
            // EXISTS with a value is interpreted as '='.
            case 'EXISTS' :
                $meta_compare = '=';
                $object_where = $wpdb->prepare( '%s', $object_type );
                break;
 
            // 'value' is ignored for NOT EXISTS.
            case 'NOT EXISTS' :
                $object_where = '';
                break;
 
            default :
                $object_where = $wpdb->prepare( '%s', $object_type );
                break;
        }
 
        if ( ! empty( $object_where ) ) {
            $sql_chunks['where'][] = "{$alias}.object_type {$meta_compare} {$object_where}";
        }
    }
 
    /*
     * Multiple WHERE clauses (for meta_key and meta_value) 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.3.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.

Contents