Skip to:
Content

BuddyPress.org

Changeset 9178


Ignore:
Timestamp:
11/25/2014 04:40:27 PM (10 years ago)
Author:
boonebgorges
Message:

Introduce BP_XProfile_Query.

Based on WP_Meta_Query, BP_XProfile_Query is a helper class for generating
queries against users' XProfile data. It allows developers to filter user
queries to those users who have profile data matching a wide range of
comparisons, from simple equality ("city=Chicago") to complex combinations of
various comparison operators.

An 'xprofile_query' argument has been introduced to BP_User_Query to
support use of BP_XProfile_Query inside of bp_has_members() loops.

See #5839.

Location:
trunk
Files:
1 added
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/src/bp-core/bp-core-classes.php

    r9119 r9178  
    1313 * BuddyPress User Query class.
    1414 *
    15  * Used for querying users in a BuddyPress context, in situations where
    16  * WP_User_Query won't do the trick: Member directories, the Friends component,
    17  * etc.
     15 * Used for querying users in a BuddyPress context, in situations where WP_User_Query won't do the trick:
     16 * Member directories, the Friends component, etc.
    1817 *
    1918 * @since BuddyPress (1.7.0)
    … …  
    2120 * @param array $query {
    2221 *     Query arguments. All items are optional.
    23  *     @type string $type Determines sort order. Select from 'newest', 'active',
    24  *           'online', 'random', 'popular', 'alphabetical'. Default: 'newest'.
    25  *     @type int $per_page Number of results to return. Default: 0 (no limit).
    26  *     @type int $page Page offset (together with $per_page). Default: 1.
    27  *     @type int $user_id ID of a user. If present, and if the friends
    28  *           component is activated, results will be limited to the friends of
    29  *           that user. Default: 0.
    30  *     @type string|bool $search_terms Terms to search by. Search happens
    31  *           across xprofile fields. Requires XProfile component.
    32  *           Default: false.
    33  *     @type string $search_wildcard When searching with $search_terms,
    34  *           set where wildcards around the term should be positioned.
    35  *           Default: 'both'. Other values: 'left', 'right'.
    36  *     @type array|string|bool $include An array or comma-separated list of
    37  *           user IDs to which query should be limited.
    38  *           Default: false.
    39  *     @type array|string|bool $exclude An array or comma-separated list of
    40  *           user IDs that will be excluded from query results. Default: false.
    41  *     @type array|string|bool $user_ids An array or comma-separated list of
    42  *           IDs corresponding to the users that should be returned. When this
    43  *           parameter is passed, it will override all others; BP User objects
    44  *           will be constructed using these IDs only. Default: false.
    45  *     @type string|bool $meta_key Limit results to users that have usermeta
    46  *           associated with this meta_key. Usually used with $meta_value.
    47  *           Default: false.
    48  *     @type string|bool $meta_value When used with $meta_key, limits results
    49  *           to users whose usermeta value associated with $meta_key matches
    50  *           $meta_value. Default: false.
    51  *     @type bool $populate_extras True if you want to fetch extra metadata
    52  *           about returned users, such as total group and friend counts.
    53  *     @type string $count_total Determines how BP_User_Query will do a count
    54  *           of total users matching the other filter criteria. Default value
    55  *           is 'count_query', which does a separate SELECT COUNT query to
    56  *           determine the total. 'sql_count_found_rows' uses
    57  *           SQL_COUNT_FOUND_ROWS and SELECT FOUND_ROWS(). Pass an empty string
    58  *           to skip the total user count query.
     22 *     @type string            $type            Determines sort order. Select from 'newest', 'active', 'online',
     23 *                                              'random', 'popular', 'alphabetical'. Default: 'newest'.
     24 *     @type int               $per_page Number of results to return. Default: 0 (no limit).
     25 *     @type int               $page            Page offset (together with $per_page). Default: 1.
     26 *     @type int               $user_id         ID of a user. If present, and if the friends component is activated,
     27 *                                              results will be limited to the friends of that user. Default: 0.
     28 *     @type string|bool       $search_terms    Terms to search by. Search happens across xprofile fields. Requires
     29 *                                              XProfile component. Default: false.
     30 *     @type string            $search_wildcard When searching with $search_terms, set where wildcards around the term
     31 *                                              should be positioned. Accepts 'both', 'left', 'right'. Default: 'both'.
     32 *     @type array|string|bool $include         An array or comma-separated list of user IDs to which query should
     33 *                                              be limited. Default: false.
     34 *     @type array|string|bool $exclude         An array or comma-separated list of user IDs that will be excluded from
     35 *                                              query results. Default: false.
     36 *     @type array|string|bool $user_ids        An array or comma-separated list of IDs corresponding to the users
     37 *                                              that should be returned. When this parameter is passed, it will
     38 *                                              override all others; BP User objects will be constructed using these
     39 *                                              IDs only. Default: false.
     40 *     @type string|bool       $meta_key        Limit results to users that have usermeta associated with this meta_key.
     41 *                                              Usually used with $meta_value. Default: false.
     42 *     @type string|bool       $meta_value      When used with $meta_key, limits results to users whose usermeta value
     43 *                                              associated with $meta_key matches $meta_value. Default: false.
     44 *     @type array             $xprofile_query  Filter results by xprofile data. Requires the xprofile component. See
     45 *                                              {@see BP_XProfile_Query} for details.
     46 *     @type bool              $populate_extras True if you want to fetch extra metadata
     47 *                                              about returned users, such as total group and friend counts.
     48 *     @type string            $count_total     Determines how BP_User_Query will do a count of total users matching
     49 *                                              the other filter criteria. Default value is 'count_query', which does
     50 *                                              a separate SELECT COUNT query to determine the total.
     51 *                                              'sql_count_found_rows' uses SQL_COUNT_FOUND_ROWS and
     52 *                                              SELECT FOUND_ROWS(). Pass an empty string to skip the total user
     53 *                                              count query.
    5954 * }
    6055 */
    … …  
    114109     */
    115110    public $uid_clauses = array();
     111
     112    /**
     113     * SQL table where the user ID is being fetched from.
     114     *
     115     * @since BuddyPress (2.2.0)
     116     * @access public
     117     * @var string
     118     */
     119    public $uid_table = '';
    116120
    117121    /**
    … …  
    163167                'meta_key'        => false,
    164168                'meta_value'      => false,
     169                'xprofile_query'  => false,
    165170                'populate_extras' => true,
    166171                'count_total'     => 'count_query'
    … …  
    250255            case 'online' :
    251256                $this->uid_name = 'user_id';
    252                 $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$bp->members->table_name_last_activity} u";
     257                $this->uid_table = $bp->members->table_name_last_activity;
     258                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    253259                $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id );
    254260                $sql['where'][] = $wpdb->prepare( "u.date_recorded >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters( 'bp_user_query_online_interval', 15 ) );
    … …  
    264270            case 'random' :
    265271                $this->uid_name = 'user_id';
    266                 $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$bp->members->table_name_last_activity} u";
     272                $this->uid_table = $bp->members->table_name_last_activity;
     273                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    267274                $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id );
    268275
    … …  
    282289            case 'popular' :
    283290                $this->uid_name = 'user_id';
    284                 $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$wpdb->usermeta} u";
     291                $this->uid_table = $wpdb->usermeta;
     292                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    285293                $sql['where'][] = $wpdb->prepare( "u.meta_key = %s", bp_get_user_meta_key( 'total_friend_count' ) );
    286294                $sql['orderby'] = "ORDER BY CONVERT(u.meta_value, SIGNED)";
    … …  
    299307                if ( ! bp_disable_profile_sync() || ! bp_is_active( 'xprofile' ) ) {
    300308                    $this->uid_name = 'ID';
    301                     $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$wpdb->users} u";
     309                    $this->uid_table = $wpdb->users;
     310                    $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    302311                    $sql['orderby'] = "ORDER BY u.display_name";
    303312                    $sql['order']   = "ASC";
    … …  
    307316                } else {
    308317                    $this->uid_name = 'user_id';
    309                     $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$bp->profile->table_name_data} u";
     318                    $this->uid_table = $bp->profile->table_name_data;
     319                    $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    310320                    $sql['where'][] = $wpdb->prepare( "u.field_id = %d", bp_xprofile_fullname_field_id() );
    311321                    $sql['orderby'] = "ORDER BY u.value";
    … …  
    323333            default :
    324334                $this->uid_name = 'ID';
    325                 $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$wpdb->users} u";
     335                $this->uid_table = $wpdb->users;
     336                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
    326337
    327338                // In this case, we assume that a plugin is

  • trunk/src/bp-xprofile/bp-xprofile-classes.php

    r9084 r9178  
    32633263    }
    32643264}
     3265
     3266/**
     3267 * Class for generating SQL clauses to filter a user query by xprofile data.
     3268 *
     3269 * @since BuddyPress (2.2.0)
     3270 */
     3271class BP_XProfile_Query {
     3272    /**
     3273     * Array of xprofile queries.
     3274     *
     3275     * See {@see WP_XProfile_Query::__construct()} for information on parameters.
     3276     *
     3277     * @since  BuddyPress (2.2.0)
     3278     * @access public
     3279     * @var    array
     3280     */
     3281    public $queries = array();
     3282
     3283    /**
     3284     * Database table that where the metadata's objects are stored (eg $wpdb->users).
     3285     *
     3286     * @since  BuddyPress (2.2.0)
     3287     * @access public
     3288     * @var    string
     3289     */
     3290    public $primary_table;
     3291
     3292    /**
     3293     * Column in primary_table that represents the ID of the object.
     3294     *
     3295     * @since  BuddyPress (2.2.0)
     3296     * @access public
     3297     * @var    string
     3298     */
     3299    public $primary_id_column;
     3300
     3301    /**
     3302     * A flat list of table aliases used in JOIN clauses.
     3303     *
     3304     * @since  BuddyPress (2.2.0)
     3305     * @access protected
     3306     * @var    array
     3307     */
     3308    protected $table_aliases = array();
     3309
     3310    /**
     3311     * Constructor.
     3312     *
     3313     * @since  BuddyPress (2.2.0)
     3314     * @access public
     3315     *
     3316     * @param array $xprofile_query {
     3317     *     Array of xprofile query clauses.
     3318     *
     3319     *     @type string $relation Optional. The MySQL keyword used to join the clauses of the query.
     3320     *                            Accepts 'AND', or 'OR'. Default 'AND'.
     3321     *     @type array {
     3322     *         Optional. An array of first-order clause parameters, or another fully-formed xprofile query.
     3323     *
     3324     *         @type string|int $field   XProfile field to filter by. Accepts a field name or ID.
     3325     *         @type string     $value   XProfile value to filter by.
     3326     *         @type string     $compare MySQL operator used for comparing the $value. Accepts '=', '!=', '>',
     3327     *                                   '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN',
     3328     *                                   'NOT BETWEEN', 'REGEXP', 'NOT REGEXP', or 'RLIKE'. Default is 'IN'
     3329     *                                   when `$value` is an array, '=' otherwise.
     3330     *         @type string     $type    MySQL data type that the `value` column will be CAST to for comparisons.
     3331     *                                   Accepts 'NUMERIC', 'BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL',
     3332     *                                   'SIGNED', 'TIME', or 'UNSIGNED'. Default is 'CHAR'.
     3333     *     }
     3334     * }
     3335     */
     3336    public function __construct( $xprofile_query ) {
     3337        if ( empty( $xprofile_query ) ) {
     3338            return;
     3339        }
     3340
     3341        $this->queries = $this->sanitize_query( $xprofile_query );
     3342    }
     3343
     3344    /**
     3345     * Ensure the `xprofile_query` argument passed to the class constructor is well-formed.
     3346     *
     3347     * Eliminates empty items and ensures that a 'relation' is set.
     3348     *
     3349     * @since  BuddyPress (2.2.0)
     3350     * @access public
     3351     *
     3352     * @param  array $queries Array of query clauses.
     3353     * @return array Sanitized array of query clauses.
     3354     */
     3355    public function sanitize_query( $queries ) {
     3356        $clean_queries = array();
     3357
     3358        if ( ! is_array( $queries ) ) {
     3359            return $clean_queries;
     3360        }
     3361
     3362        foreach ( $queries as $key => $query ) {
     3363            if ( 'relation' === $key ) {
     3364                $relation = $query;
     3365
     3366            } else if ( ! is_array( $query ) ) {
     3367                continue;
     3368
     3369            // First-order clause.
     3370            } else if ( $this->is_first_order_clause( $query ) ) {
     3371                if ( isset( $query['value'] ) && array() === $query['value'] ) {
     3372                    unset( $query['value'] );
     3373                }
     3374
     3375                $clean_queries[] = $query;
     3376
     3377            // Otherwise, it's a nested query, so we recurse.
     3378            } else {
     3379                $cleaned_query = $this->sanitize_query( $query );
     3380
     3381                if ( ! empty( $cleaned_query ) ) {
     3382                    $clean_queries[] = $cleaned_query;
     3383                }
     3384            }
     3385        }
     3386
     3387        if ( empty( $clean_queries ) ) {
     3388            return $clean_queries;
     3389        }
     3390
     3391        // Sanitize the 'relation' key provided in the query.
     3392        if ( isset( $relation ) && 'OR' === strtoupper( $relation ) ) {
     3393            $clean_queries['relation'] = 'OR';
     3394
     3395        /*
     3396         * If there is only a single clause, call the relation 'OR'.
     3397         * This value will not actually be used to join clauses, but it
     3398         * simplifies the logic around combining key-only queries.
     3399         */
     3400        } else if ( 1 === count( $clean_queries ) ) {
     3401            $clean_queries['relation'] = 'OR';
     3402
     3403        // Default to AND.
     3404        } else {
     3405            $clean_queries['relation'] = 'AND';
     3406        }
     3407
     3408        return $clean_queries;
     3409    }
     3410
     3411    /**
     3412     * Determine whether a query clause is first-order.
     3413     *
     3414     * A first-order query clause is one that has either a 'key' or a 'value' array key.
     3415     *
     3416     * @since  BuddyPress (2.2.0)
     3417     * @access protected
     3418     *
     3419     * @param  array $query XProfile query arguments.
     3420     * @return bool  Whether the query clause is a first-order clause.
     3421     */
     3422    protected function is_first_order_clause( $query ) {
     3423        return isset( $query['field'] ) || isset( $query['value'] );
     3424    }
     3425
     3426    /**
     3427     * Return the appropriate alias for the given field type if applicable.
     3428     *
     3429     * @since  BuddyPress (2.2.0)
     3430     * @access public
     3431     *
     3432     * @param  string $type MySQL type to cast `value`.
     3433     * @return string MySQL type.
     3434     */
     3435    public function get_cast_for_type( $type = '' ) {
     3436        if ( empty( $type ) )
     3437            return 'CHAR';
     3438
     3439        $meta_type = strtoupper( $type );
     3440
     3441        if ( ! preg_match( '/^(?:BINARY|CHAR|DATE|DATETIME|SIGNED|UNSIGNED|TIME|NUMERIC(?:\(\d+(?:,\s?\d+)?\))?|DECIMAL(?:\(\d+(?:,\s?\d+)?\))?)$/', $meta_type ) )
     3442            return 'CHAR';
     3443
     3444        if ( 'NUMERIC' == $meta_type )
     3445            $meta_type = 'SIGNED';
     3446
     3447        return $meta_type;
     3448    }
     3449
     3450    /**
     3451     * Generate SQL clauses to be appended to a main query.
     3452     *
     3453     * Called by the public {@see BP_XProfile_Query::get_sql()}, this method is abstracted out to maintain parity
     3454     * with WP's Query classes.
     3455     *
     3456     * @since  BuddyPress (2.2.0)
     3457     * @access protected
     3458     *
     3459     * @return array {
     3460     *     Array containing JOIN and WHERE SQL clauses to append to the main query.
     3461     *
     3462     *     @type string $join  SQL fragment to append to the main JOIN clause.
     3463     *     @type string $where SQL fragment to append to the main WHERE clause.
     3464     * }
     3465     */
     3466    protected function get_sql_clauses() {
     3467        /*
     3468         * $queries are passed by reference to get_sql_for_query() for recursion.
     3469         * To keep $this->queries unaltered, pass a copy.
     3470         */
     3471        $queries = $this->queries;
     3472        $sql = $this->get_sql_for_query( $queries );
     3473
     3474        if ( ! empty( $sql['where'] ) ) {
     3475            $sql['where'] = ' AND ' . $sql['where'];
     3476        }
     3477
     3478        return $sql;
     3479    }
     3480
     3481    /**
     3482     * Generate SQL clauses for a single query array.
     3483     *
     3484     * If nested subqueries are found, this method recurses the tree to produce the properly nested SQL.
     3485     *
     3486     * @since  BuddyPress (2.2.0)
     3487     * @access protected
     3488     *
     3489     * @param  array $query Query to parse.
     3490     * @param  int   $depth Optional. Number of tree levels deep we currently are. Used to calculate indentation.
     3491     * @return array {
     3492     *     Array containing JOIN and WHERE SQL clauses to append to a single query array.
     3493     *
     3494     *     @type string $join  SQL fragment to append to the main JOIN clause.
     3495     *     @type string $where SQL fragment to append to the main WHERE clause.
     3496     * }
     3497     */
     3498    protected function get_sql_for_query( &$query, $depth = 0 ) {
     3499        $sql_chunks = array(
     3500            'join'  => array(),
     3501            'where' => array(),
     3502        );
     3503
     3504        $sql = array(
     3505            'join'  => '',
     3506            'where' => '',
     3507        );
     3508
     3509        $indent = '';
     3510        for ( $i = 0; $i < $depth; $i++ ) {
     3511            $indent .= "  ";
     3512        }
     3513
     3514        foreach ( $query as $key => &$clause ) {
     3515            if ( 'relation' === $key ) {
     3516                $relation = $query['relation'];
     3517            } else if ( is_array( $clause ) ) {
     3518
     3519                // This is a first-order clause.
     3520                if ( $this->is_first_order_clause( $clause ) ) {
     3521                    $clause_sql = $this->get_sql_for_clause( $clause, $query );
     3522
     3523                    $where_count = count( $clause_sql['where'] );
     3524                    if ( ! $where_count ) {
     3525                        $sql_chunks['where'][] = '';
     3526                    } else if ( 1 === $where_count ) {
     3527                        $sql_chunks['where'][] = $clause_sql['where'][0];
     3528                    } else {
     3529                        $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
     3530                    }
     3531
     3532                    $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
     3533                // This is a subquery, so we recurse.
     3534                } else {
     3535                    $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );
     3536
     3537                    $sql_chunks['where'][] = $clause_sql['where'];
     3538                    $sql_chunks['join'][]  = $clause_sql['join'];
     3539                }
     3540            }
     3541        }
     3542
     3543        // Filter to remove empties.
     3544        $sql_chunks['join']  = array_filter( $sql_chunks['join'] );
     3545        $sql_chunks['where'] = array_filter( $sql_chunks['where'] );
     3546
     3547        if ( empty( $relation ) ) {
     3548            $relation = 'AND';
     3549        }
     3550
     3551        // Filter duplicate JOIN clauses and combine into a single string.
     3552        if ( ! empty( $sql_chunks['join'] ) ) {
     3553            $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
     3554        }
     3555
     3556        // Generate a single WHERE clause with proper brackets and indentation.
     3557        if ( ! empty( $sql_chunks['where'] ) ) {
     3558            $sql['where'] = '( ' . "\n  " . $indent . implode( ' ' . "\n  " . $indent . $relation . ' ' . "\n  " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
     3559        }
     3560
     3561        return $sql;
     3562    }
     3563
     3564    /**
     3565     * Generates SQL clauses to be appended to a main query.
     3566     *
     3567     * @since  BuddyPress (2.2.0)
     3568     * @access public
     3569     *
     3570     * @param string $primary_table     Database table where the object being filtered is stored (eg wp_users).
     3571     * @param string $primary_id_column ID column for the filtered object in $primary_table.
     3572     * @return array {
     3573     *     Array containing JOIN and WHERE SQL clauses to append to the main query.
     3574     *
     3575     *     @type string $join  SQL fragment to append to the main JOIN clause.
     3576     *     @type string $where SQL fragment to append to the main WHERE clause.
     3577     * }
     3578     */
     3579    public function get_sql( $primary_table, $primary_id_column, $context = null ) {
     3580        global $wpdb;
     3581
     3582        $this->primary_table     = $primary_table;
     3583        $this->primary_id_column = $primary_id_column;
     3584
     3585        $sql = $this->get_sql_clauses();
     3586
     3587        /*
     3588         * If any JOINs are LEFT JOINs (as in the case of NOT EXISTS), then all JOINs should
     3589         * be LEFT. Otherwise posts with no metadata will be excluded from results.
     3590         */
     3591        if ( false !== strpos( $sql['join'], 'LEFT JOIN' ) ) {
     3592            $sql['join'] = str_replace( 'INNER JOIN', 'LEFT JOIN', $sql['join'] );
     3593        }
     3594
     3595        return $sql;
     3596    }
     3597
     3598    /**
     3599     * Generate SQL JOIN and WHERE clauses for a first-order query clause.
     3600     *
     3601     * "First-order" means that it's an array with a 'field' or 'value'.
     3602     *
     3603     * @since  BuddyPress (2.2.0)
     3604     * @access public
     3605     *
     3606     * @param array $clause       Query clause.
     3607     * @param array $parent_query Parent query array.
     3608     * @return array {
     3609     *     Array containing JOIN and WHERE SQL clauses to append to a first-order query.
     3610     *
     3611     *     @type string $join  SQL fragment to append to the main JOIN clause.
     3612     *     @type string $where SQL fragment to append to the main WHERE clause.
     3613     * }
     3614     */
     3615    public function get_sql_for_clause( &$clause, $parent_query ) {
     3616        global $wpdb;
     3617
     3618        $sql_chunks = array(
     3619            'where' => array(),
     3620            'join' => array(),
     3621        );
     3622
     3623        if ( isset( $clause['compare'] ) ) {
     3624            $clause['compare'] = strtoupper( $clause['compare'] );
     3625        } else {
     3626            $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
     3627        }
     3628
     3629        if ( ! in_array( $clause['compare'], array(
     3630            '=', '!=', '>', '>=', '<', '<=',
     3631            'LIKE', 'NOT LIKE',
     3632            'IN', 'NOT IN',
     3633            'BETWEEN', 'NOT BETWEEN',
     3634            'EXISTS', 'NOT EXISTS',
     3635            'REGEXP', 'NOT REGEXP', 'RLIKE'
     3636        ) ) ) {
     3637            $clause['compare'] = '=';
     3638        }
     3639
     3640        $field_compare = $clause['compare'];
     3641
     3642        // First build the JOIN clause, if one is required.
     3643        $join = '';
     3644
     3645        $data_table = buddypress()->profile->table_name_data;
     3646
     3647        // We prefer to avoid joins if possible. Look for an existing join compatible with this clause.
     3648        $alias = $this->find_compatible_table_alias( $clause, $parent_query );
     3649        if ( false === $alias ) {
     3650            $i = count( $this->table_aliases );
     3651            $alias = $i ? 'xpq' . $i : $data_table;
     3652
     3653            // JOIN clauses for NOT EXISTS have their own syntax.
     3654            if ( 'NOT EXISTS' === $field_compare ) {
     3655                $join .= " LEFT JOIN $data_table";
     3656                $join .= $i ? " AS $alias" : '';
     3657                $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.user_id AND $alias.field_id = %d )", $clause['field'] );
     3658
     3659            // All other JOIN clauses.
     3660            } else {
     3661                $join .= " INNER JOIN $data_table";
     3662                $join .= $i ? " AS $alias" : '';
     3663                $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.user_id )";
     3664            }
     3665
     3666            $this->table_aliases[] = $alias;
     3667            $sql_chunks['join'][] = $join;
     3668        }
     3669
     3670        // Save the alias to this clause, for future siblings to find.
     3671        $clause['alias'] = $alias;
     3672
     3673        // Next, build the WHERE clause.
     3674        $where = '';
     3675
     3676        // field_id.
     3677        if ( array_key_exists( 'field', $clause ) ) {
     3678            // Convert field name to ID if necessary.
     3679            if ( ! is_numeric( $clause['field'] ) ) {
     3680                $clause['field'] = xprofile_get_field_id_from_name( $clause['field'] );
     3681            }
     3682
     3683            // NOT EXISTS has its own syntax.
     3684            if ( 'NOT EXISTS' === $field_compare ) {
     3685                $sql_chunks['where'][] = $alias . '.user_id IS NULL';
     3686            } else {
     3687                $sql_chunks['where'][] = $wpdb->prepare( "$alias.field_id = %d", $clause['field'] );
     3688            }
     3689        }
     3690
     3691        // value.
     3692        if ( array_key_exists( 'value', $clause ) ) {
     3693            $field_value = $clause['value'];
     3694            $field_type = $this->get_cast_for_type( isset( $clause['type'] ) ? $clause['type'] : '' );
     3695
     3696            if ( in_array( $field_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
     3697                if ( ! is_array( $field_value ) ) {
     3698                    $field_value = preg_split( '/[,\s]+/', $field_value );
     3699                }
     3700            } else {
     3701                $field_value = trim( $field_value );
     3702            }
     3703
     3704            switch ( $field_compare ) {
     3705                case 'IN' :
     3706                case 'NOT IN' :
     3707                    $field_compare_string = '(' . substr( str_repeat( ',%s', count( $field_value ) ), 1 ) . ')';
     3708                    $where = $wpdb->prepare( $field_compare_string, $field_value );
     3709                    break;
     3710
     3711                case 'BETWEEN' :
     3712                case 'NOT BETWEEN' :
     3713                    $field_value = array_slice( $field_value, 0, 2 );
     3714                    $where = $wpdb->prepare( '%s AND %s', $field_value );
     3715                    break;
     3716
     3717                case 'LIKE' :
     3718                case 'NOT LIKE' :
     3719                    $field_value = '%' . $wpdb->esc_like( $field_value ) . '%';
     3720                    $where = $wpdb->prepare( '%s', $field_value );
     3721                    break;
     3722
     3723                default :
     3724                    $where = $wpdb->prepare( '%s', $field_value );
     3725                    break;
     3726
     3727            }
     3728
     3729            if ( $where ) {
     3730                $sql_chunks['where'][] = "CAST($alias.value AS {$field_type}) {$field_compare} {$where}";
     3731            }
     3732        }
     3733
     3734        /*
     3735         * Multiple WHERE clauses (`field` and `value` pairs) should be joined in parentheses.
     3736         */
     3737        if ( 1 < count( $sql_chunks['where'] ) ) {
     3738            $sql_chunks['where'] = array( '( ' . implode( ' AND ', $sql_chunks['where'] ) . ' )' );
     3739        }
     3740
     3741        return $sql_chunks;
     3742    }
     3743
     3744    /**
     3745     * Identify an existing table alias that is compatible with the current query clause.
     3746     *
     3747     * We avoid unnecessary table joins by allowing each clause to look for an existing table alias that is
     3748     * compatible with the query that it needs to perform. An existing alias is compatible if (a) it is a
     3749     * sibling of $clause (ie, it's under the scope of the same relation), and (b) the combination of
     3750     * operator and relation between the clauses allows for a shared table join. In the case of BP_XProfile_Query,
     3751     * this * only applies to IN clauses that are connected by the relation OR.
     3752     *
     3753     * @since  BuddyPress (2.2.0)
     3754     * @access protected
     3755     *
     3756     * @param  array       $clause       Query clause.
     3757     * @param  array       $parent_query Parent query of $clause.
     3758     * @return string|bool Table alias if found, otherwise false.
     3759     */
     3760    protected function find_compatible_table_alias( $clause, $parent_query ) {
     3761        $alias = false;
     3762
     3763        foreach ( $parent_query as $sibling ) {
     3764            // If the sibling has no alias yet, there's nothing to check.
     3765            if ( empty( $sibling['alias'] ) ) {
     3766                continue;
     3767            }
     3768
     3769            // We're only interested in siblings that are first-order clauses.
     3770            if ( ! is_array( $sibling ) || ! $this->is_first_order_clause( $sibling ) ) {
     3771                continue;
     3772            }
     3773
     3774            $compatible_compares = array();
     3775
     3776            // Clauses connected by OR can share joins as long as they have "positive" operators.
     3777            if ( 'OR' === $parent_query['relation'] ) {
     3778                $compatible_compares = array( '=', 'IN', 'BETWEEN', 'LIKE', 'REGEXP', 'RLIKE', '>', '>=', '<', '<=' );
     3779
     3780            // Clauses joined by AND with "negative" operators share a join only if they also share a key.
     3781            } else if ( isset( $sibling['field_id'] ) && isset( $clause['field_id'] ) && $sibling['field_id'] === $clause['field_id'] ) {
     3782                $compatible_compares = array( '!=', 'NOT IN', 'NOT LIKE' );
     3783            }
     3784
     3785            $clause_compare  = strtoupper( $clause['compare'] );
     3786            $sibling_compare = strtoupper( $sibling['compare'] );
     3787            if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) {
     3788                $alias = $sibling['alias'];
     3789                break;
     3790            }
     3791        }
     3792
     3793        return $alias;
     3794    }
     3795}

  • trunk/src/bp-xprofile/bp-xprofile-filters.php

    r9073 r9178  
    324324
    325325/**
     326 * Parse 'xprofile_query' argument passed to BP_User_Query.
     327 *
     328 * @since BuddyPress (2.2.0)
     329 *
     330 * @param BP_User_Query User query object.
     331 */
     332function bp_xprofile_add_xprofile_query_to_user_query( BP_User_Query $q ) {
     333    global $wpdb;
     334
     335    if ( empty( $q->query_vars['xprofile_query'] ) ) {
     336        return;
     337    }
     338
     339    $xprofile_query = new BP_XProfile_Query( $q->query_vars['xprofile_query'] );
     340    $sql = $xprofile_query->get_sql( 'u', $q->uid_name );
     341
     342    if ( ! empty( $sql['join'] ) ) {
     343        $q->uid_clauses['select'] .= $sql['join'];
     344        $q->uid_clauses['where'] .= $sql['where'];
     345    }
     346}
     347add_action( 'bp_pre_user_query', 'bp_xprofile_add_xprofile_query_to_user_query' );
     348
     349/**
    326350 * Filter meta queries to modify for the xprofile data schema.
    327351 *
Note: See TracChangeset for help on using the changeset viewer.