BP_XProfile_Query::get_sql_for_query( array $query, int $depth )

Generate SQL clauses for a single query array.

Description

If nested subqueries are found, this method recurses the tree to produce the properly nested SQL.

Parameters

$query

(Required) Query to parse. Passed by reference.

$depth

(Optional) Number of tree levels deep we currently are. Used to calculate indentation.

Return

(array) Array containing JOIN and WHERE SQL clauses to append to a single query array.

  • '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

238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
protected function get_sql_for_query( &$query, $depth = 0 ) {
    $sql_chunks = array(
        'join'  => array(),
        'where' => array(),
    );
 
    $sql = array(
        'join'  => '',
        'where' => '',
    );
 
    $indent = '';
    for ( $i = 0; $i < $depth; $i++ ) {
        $indent .= "  ";
    }
 
    foreach ( $query as $key => &$clause ) {
        if ( 'relation' === $key ) {
            $relation = $query['relation'];
        } elseif ( is_array( $clause ) ) {
 
            // This is a first-order clause.
            if ( $this->is_first_order_clause( $clause ) ) {
                $clause_sql = $this->get_sql_for_clause( $clause, $query );
 
                $where_count = count( $clause_sql['where'] );
                if ( ! $where_count ) {
                    $sql_chunks['where'][] = '';
                } elseif ( 1 === $where_count ) {
                    $sql_chunks['where'][] = $clause_sql['where'][0];
                } else {
                    $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
                }
 
                $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
            // This is a subquery, so we recurse.
            } else {
                $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );
 
                $sql_chunks['where'][] = $clause_sql['where'];
                $sql_chunks['join'][]  = $clause_sql['join'];
            }
        }
    }
 
    // Filter to remove empties.
    $sql_chunks['join']  = array_filter( $sql_chunks['join'] );
    $sql_chunks['where'] = array_filter( $sql_chunks['where'] );
 
    if ( empty( $relation ) ) {
        $relation = 'AND';
    }
 
    // Filter duplicate JOIN clauses and combine into a single string.
    if ( ! empty( $sql_chunks['join'] ) ) {
        $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
    }
 
    // Generate a single WHERE clause with proper brackets and indentation.
    if ( ! empty( $sql_chunks['where'] ) ) {
        $sql['where'] = '( ' . "\n  " . $indent . implode( ' ' . "\n  " . $indent . $relation . ' ' . "\n  " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
    }
 
    return $sql;
}

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.