/**
* Find users who match on the value of an xprofile data.
*
* @global wpdb $wpdb WordPress database object.
*
* @param string $search_terms The terms to search the profile table
* value column for.
* @param integer $limit The limit of results we want.
* @param integer $page The page we are on for pagination.
* @param boolean $populate_extras Populate extra user fields?
* @return array Associative array.
*/
public static function search_users($search_terms, $limit = null, $page = 1, $populate_extras = true)
{
global $wpdb;
$bp = buddypress();
$user_ids = array();
$pag_sql = $limit && $page ? $wpdb->prepare(" LIMIT %d, %d", intval(($page - 1) * intval($limit)), intval($limit)) : '';
$search_terms_like = '%' . bp_esc_like($search_terms) . '%';
$status_sql = bp_core_get_status_sql('u.');
/**
* Filters the SQL used to query for searched users count.
*
* @since BuddyPress (1.0.0)
*
* @param string $value SQL statement for the searched users count query.
*/
$total_users_sql = apply_filters('bp_core_search_users_count_sql', $wpdb->prepare("SELECT COUNT(DISTINCT u.ID) as id FROM {$wpdb->users} u LEFT JOIN {$bp->profile->table_name_data} pd ON u.ID = pd.user_id WHERE {$status_sql} AND pd.value LIKE %s ORDER BY pd.value ASC", $search_terms_like), $search_terms);
/**
* Filters the SQL used to query for searched users.
*
* @since BuddyPress (1.0.0)
*
* @param string $value SQL statement for the searched users query.
*/
$paged_users_sql = apply_filters('bp_core_search_users_sql', $wpdb->prepare("SELECT DISTINCT u.ID as id, u.user_registered, u.user_nicename, u.user_login, u.user_email FROM {$wpdb->users} u LEFT JOIN {$bp->profile->table_name_data} pd ON u.ID = pd.user_id WHERE {$status_sql} AND pd.value LIKE %s ORDER BY pd.value ASC{$pag_sql}", $search_terms_like), $search_terms, $pag_sql);
$total_users = $wpdb->get_var($total_users_sql);
$paged_users = $wpdb->get_results($paged_users_sql);
/***
* Lets fetch some other useful data in a separate queries, this will be faster than querying the data for every user in a list.
* We can't add these to the main query above since only users who have this information will be returned (since the much of the data is in usermeta and won't support any type of directional join)
*/
foreach ((array) $paged_users as $user) {
$user_ids[] = $user->id;
}
// Add additional data to the returned results
if ($populate_extras) {
$paged_users = BP_Core_User::get_user_extras($paged_users, $user_ids);
}
return array('users' => $paged_users, 'total' => $total_users);
}
/**
* Prepare the query for user_ids.
*
* @since 1.7.0
*/
public function prepare_user_ids_query()
{
global $wpdb;
$bp = buddypress();
// Default query variables used here.
$type = '';
$per_page = 0;
$page = 1;
$user_id = 0;
$include = false;
$search_terms = false;
$exclude = false;
$meta_key = false;
$meta_value = false;
extract($this->query_vars);
// Setup the main SQL query container.
$sql = array('select' => '', 'where' => array(), 'orderby' => '', 'order' => '', 'limit' => '');
/* TYPE **************************************************************/
// Determines the sort order, which means it also determines where the
// user IDs are drawn from (the SELECT and WHERE statements).
switch ($type) {
// 'online' query happens against the last_activity usermeta key
// Filter 'bp_user_query_online_interval' to modify the
// number of minutes used as an interval.
case 'online':
$this->uid_name = 'user_id';
$this->uid_table = $bp->members->table_name_last_activity;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
$sql['where'][] = $wpdb->prepare("u.component = %s AND u.type = 'last_activity'", buddypress()->members->id);
/**
* Filters the threshold for activity timestamp minutes since to indicate online status.
*
* @since 1.8.0
*
* @param int $value Amount of minutes for threshold. Default 15.
*/
$sql['where'][] = $wpdb->prepare("u.date_recorded >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters('bp_user_query_online_interval', 15));
$sql['orderby'] = "ORDER BY u.date_recorded";
$sql['order'] = "DESC";
break;
// 'active', 'newest', and 'random' queries
// all happen against the last_activity usermeta key.
// 'active', 'newest', and 'random' queries
// all happen against the last_activity usermeta key.
case 'active':
case 'newest':
case 'random':
$this->uid_name = 'user_id';
$this->uid_table = $bp->members->table_name_last_activity;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
$sql['where'][] = $wpdb->prepare("u.component = %s AND u.type = 'last_activity'", buddypress()->members->id);
if ('newest' == $type) {
$sql['orderby'] = "ORDER BY u.user_id";
$sql['order'] = "DESC";
} elseif ('random' == $type) {
$sql['orderby'] = "ORDER BY rand()";
} else {
$sql['orderby'] = "ORDER BY u.date_recorded";
$sql['order'] = "DESC";
}
break;
// 'popular' sorts by the 'total_friend_count' usermeta.
// 'popular' sorts by the 'total_friend_count' usermeta.
case 'popular':
$this->uid_name = 'user_id';
$this->uid_table = $wpdb->usermeta;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
$sql['where'][] = $wpdb->prepare("u.meta_key = %s", bp_get_user_meta_key('total_friend_count'));
$sql['orderby'] = "ORDER BY CONVERT(u.meta_value, SIGNED)";
$sql['order'] = "DESC";
break;
// 'alphabetical' sorts depend on the xprofile setup.
// 'alphabetical' sorts depend on the xprofile setup.
case 'alphabetical':
// We prefer to do alphabetical sorts against the display_name field
// of wp_users, because the table is smaller and better indexed. We
// can do so if xprofile sync is enabled, or if xprofile is inactive.
//
// @todo remove need for bp_is_active() check.
if (!bp_disable_profile_sync() || !bp_is_active('xprofile')) {
$this->uid_name = 'ID';
$this->uid_table = $wpdb->users;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
$sql['orderby'] = "ORDER BY u.display_name";
$sql['order'] = "ASC";
// When profile sync is disabled, alphabetical sorts must happen against
// the xprofile table.
} else {
$this->uid_name = 'user_id';
$this->uid_table = $bp->profile->table_name_data;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
$sql['where'][] = $wpdb->prepare("u.field_id = %d", bp_xprofile_fullname_field_id());
$sql['orderby'] = "ORDER BY u.value";
$sql['order'] = "ASC";
}
// Alphabetical queries ignore last_activity, while BP uses last_activity
// to infer spam/deleted/non-activated users. To ensure that these users
// are filtered out, we add an appropriate sub-query.
$sql['where'][] = "u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE " . bp_core_get_status_sql('') . " )";
break;
// Any other 'type' falls through.
// Any other 'type' falls through.
default:
$this->uid_name = 'ID';
$this->uid_table = $wpdb->users;
$sql['select'] = "SELECT u.{$this->uid_name} as id FROM {$this->uid_table} u";
// In this case, we assume that a plugin is
// handling order, so we leave those clauses
// blank.
break;
}
/* WHERE *************************************************************/
// 'include' - User ids to include in the results.
$include = false !== $include ? wp_parse_id_list($include) : array();
$include_ids = $this->get_include_ids($include);
if (!empty($include_ids)) {
$include_ids = implode(',', wp_parse_id_list($include_ids));
$sql['where'][] = "u.{$this->uid_name} IN ({$include_ids})";
}
// 'exclude' - User ids to exclude from the results.
if (false !== $exclude) {
$exclude_ids = implode(',', wp_parse_id_list($exclude));
$sql['where'][] = "u.{$this->uid_name} NOT IN ({$exclude_ids})";
}
// 'user_id' - When a user id is passed, limit to the friends of the user
// @todo remove need for bp_is_active() check.
if (!empty($user_id) && bp_is_active('friends')) {
$friend_ids = friends_get_friend_user_ids($user_id);
$friend_ids = implode(',', wp_parse_id_list($friend_ids));
if (!empty($friend_ids)) {
$sql['where'][] = "u.{$this->uid_name} IN ({$friend_ids})";
// If the user has no friends, the query should always
// return no users.
} else {
$sql['where'][] = $this->no_results['where'];
}
}
/* Search Terms ******************************************************/
// 'search_terms' searches user_login and user_nicename
// xprofile field matches happen in bp_xprofile_bp_user_query_search().
if (false !== $search_terms) {
$search_terms = bp_esc_like(wp_kses_normalize_entities($search_terms));
if ($search_wildcard === 'left') {
$search_terms_nospace = '%' . $search_terms;
$search_terms_space = '%' . $search_terms . ' %';
} elseif ($search_wildcard === 'right') {
$search_terms_nospace = $search_terms . '%';
$search_terms_space = '% ' . $search_terms . '%';
} else {
$search_terms_nospace = '%' . $search_terms . '%';
$search_terms_space = '%' . $search_terms . '%';
}
$sql['where']['search'] = $wpdb->prepare("u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE ( user_login LIKE %s OR user_login LIKE %s OR user_nicename LIKE %s OR user_nicename LIKE %s ) )", $search_terms_nospace, $search_terms_space, $search_terms_nospace, $search_terms_space);
}
// Only use $member_type__in if $member_type is not set.
if (empty($member_type) && !empty($member_type__in)) {
$member_type = $member_type__in;
}
// Member types to exclude. Note that this takes precedence over inclusions.
if (!empty($member_type__not_in)) {
$member_type_clause = $this->get_sql_clause_for_member_types($member_type__not_in, 'NOT IN');
// Member types to include.
} elseif (!empty($member_type)) {
$member_type_clause = $this->get_sql_clause_for_member_types($member_type, 'IN');
}
if (!empty($member_type_clause)) {
$sql['where']['member_type'] = $member_type_clause;
}
// 'meta_key', 'meta_value' allow usermeta search
// To avoid global joins, do a separate query.
if (false !== $meta_key) {
$meta_sql = $wpdb->prepare("SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s", $meta_key);
if (false !== $meta_value) {
$meta_sql .= $wpdb->prepare(" AND meta_value = %s", $meta_value);
}
$found_user_ids = $wpdb->get_col($meta_sql);
if (!empty($found_user_ids)) {
$sql['where'][] = "u.{$this->uid_name} IN (" . implode(',', wp_parse_id_list($found_user_ids)) . ")";
} else {
$sql['where'][] = '1 = 0';
}
}
// 'per_page', 'page' - handles LIMIT.
if (!empty($per_page) && !empty($page)) {
$sql['limit'] = $wpdb->prepare("LIMIT %d, %d", intval(($page - 1) * $per_page), intval($per_page));
} else {
$sql['limit'] = '';
}
/**
* Filters the clauses for the user query.
*
* @since 2.0.0
*
* @param array $sql Array of SQL clauses to be used in the query.
* @param BP_User_Query $this Current BP_User_Query instance.
*/
$sql = apply_filters_ref_array('bp_user_query_uid_clauses', array($sql, &$this));
// Assemble the query chunks.
$this->uid_clauses['select'] = $sql['select'];
$this->uid_clauses['where'] = !empty($sql['where']) ? 'WHERE ' . implode(' AND ', $sql['where']) : '';
$this->uid_clauses['orderby'] = $sql['orderby'];
$this->uid_clauses['order'] = $sql['order'];
$this->uid_clauses['limit'] = $sql['limit'];
/**
* Fires before the BP_User_Query query is made.
*
* @since 1.7.0
*
* @param BP_User_Query $this Current BP_User_Query instance. Passed by reference.
*/
do_action_ref_array('bp_pre_user_query', array(&$this));
}
/**
* Retrieve a list of blogs whose names start with a given letter.
*
* Query will include hidden blogs if the logged-in user has the
* 'bp_moderate' cap.
*
* @param string $letter The letter you're looking for.
* @param int|null $limit Optional. The maximum number of items to return.
* Default: null (no limit).
* @param int|null $page Optional. The page of results to return. Default:
* null (no limit).
* @return array Multidimensional results array, structured as follows:
* 'blogs' - Array of located blog objects.
* 'total' - A count of the total blogs matching the query.
*/
public static function get_by_letter($letter, $limit = null, $page = null)
{
global $wpdb;
$bp = buddypress();
$letter_like = '%' . bp_esc_like($letter) . '%';
$letter_sql = $wpdb->prepare('bm.meta_value LIKE %s', $letter_like);
$hidden_sql = '';
if (!bp_current_user_can('bp_moderate')) {
$hidden_sql = "AND wb.public = 1";
}
$pag_sql = '';
if ($limit && $page) {
$pag_sql = $wpdb->prepare(" LIMIT %d, %d", intval(($page - 1) * $limit), intval($limit));
}
$paged_blogs = $wpdb->get_results("SELECT DISTINCT bm.blog_id FROM {$bp->blogs->table_name_blogmeta} bm LEFT JOIN {$wpdb->base_prefix}blogs wb ON bm.blog_id = wb.blog_id WHERE bm.meta_key = 'name' AND {$letter_sql} {$hidden_sql} AND wb.mature = 0 AND wb.spam = 0 AND wb.archived = '0' AND wb.deleted = 0 ORDER BY bm.meta_value ASC{$pag_sql}");
$total_blogs = $wpdb->get_var("SELECT COUNT(DISTINCT bm.blog_id) FROM {$bp->blogs->table_name_blogmeta} bm LEFT JOIN {$wpdb->base_prefix}blogs wb ON bm.blog_id = wb.blog_id WHERE bm.meta_key = 'name' AND {$letter_sql} {$hidden_sql} AND wb.mature = 0 AND wb.spam = 0 AND wb.archived = '0' AND wb.deleted = 0 ORDER BY bm.meta_value ASC");
return array('blogs' => $paged_blogs, 'total' => $total_blogs);
}
/**
* Fetch signups based on parameters.
*
* @since 2.0.0
*
* @param array $args the argument to retrieve desired signups.
* @return array {
* @type array $signups Located signups.
* @type int $total Total number of signups matching params.
* }
*/
public static function get($args = array())
{
global $wpdb;
$r = bp_parse_args($args, array('offset' => 0, 'number' => 1, 'usersearch' => false, 'orderby' => 'signup_id', 'order' => 'DESC', 'include' => false, 'activation_key' => '', 'user_login' => ''), 'bp_core_signups_get_args');
// @todo whitelist sanitization
if ($r['orderby'] !== 'signup_id') {
$r['orderby'] = 'user_' . $r['orderby'];
}
$r['orderby'] = sanitize_title($r['orderby']);
$sql = array();
$signups_table = buddypress()->members->table_name_signups;
$sql['select'] = "SELECT * FROM {$signups_table}";
$sql['where'] = array();
$sql['where'][] = "active = 0";
if (empty($r['include'])) {
// Search terms.
if (!empty($r['usersearch'])) {
$search_terms_like = '%' . bp_esc_like($r['usersearch']) . '%';
$sql['where'][] = $wpdb->prepare("( user_login LIKE %s OR user_email LIKE %s OR meta LIKE %s )", $search_terms_like, $search_terms_like, $search_terms_like);
}
// Activation key.
if (!empty($r['activation_key'])) {
$sql['where'][] = $wpdb->prepare("activation_key = %s", $r['activation_key']);
}
// User login.
if (!empty($r['user_login'])) {
$sql['where'][] = $wpdb->prepare("user_login = %s", $r['user_login']);
}
$sql['orderby'] = "ORDER BY {$r['orderby']}";
$sql['order'] = bp_esc_sql_order($r['order']);
$sql['limit'] = $wpdb->prepare("LIMIT %d, %d", $r['offset'], $r['number']);
} else {
$in = implode(',', wp_parse_id_list($r['include']));
$sql['in'] = "AND signup_id IN ({$in})";
}
// Implode WHERE clauses.
$sql['where'] = 'WHERE ' . implode(' AND ', $sql['where']);
/**
* Filters the Signups paged query.
*
* @since 2.0.0
*
* @param string $value SQL statement.
* @param array $sql Array of SQL statement parts.
* @param array $args Array of original arguments for get() method.
* @param array $r Array of parsed arguments for get() method.
*/
$paged_signups = $wpdb->get_results(apply_filters('bp_members_signups_paged_query', join(' ', $sql), $sql, $args, $r));
if (empty($paged_signups)) {
return array('signups' => false, 'total' => false);
}
// Used to calculate a diff between now & last
// time an activation link has been resent.
$now = current_time('timestamp', true);
foreach ((array) $paged_signups as $key => $signup) {
$signup->id = intval($signup->signup_id);
$signup->meta = !empty($signup->meta) ? maybe_unserialize($signup->meta) : false;
$signup->user_name = '';
if (!empty($signup->meta['field_1'])) {
$signup->user_name = wp_unslash($signup->meta['field_1']);
}
// Sent date defaults to date of registration.
if (!empty($signup->meta['sent_date'])) {
$signup->date_sent = $signup->meta['sent_date'];
} else {
$signup->date_sent = $signup->registered;
}
$sent_at = mysql2date('U', $signup->date_sent);
$diff = $now - $sent_at;
/**
* Add a boolean in case the last time an activation link
* has been sent happened less than a day ago.
*/
if ($diff < 1 * DAY_IN_SECONDS) {
$signup->recently_sent = true;
}
if (!empty($signup->meta['count_sent'])) {
$signup->count_sent = absint($signup->meta['count_sent']);
} else {
$signup->count_sent = 1;
}
$paged_signups[$key] = $signup;
}
unset($sql['limit']);
$sql['select'] = preg_replace("/SELECT.*?FROM/", "SELECT COUNT(*) FROM", $sql['select']);
/**
* Filters the Signups count query.
*
* @since 2.0.0
*
* @param string $value SQL statement.
* @param array $sql Array of SQL statement parts.
* @param array $args Array of original arguments for get() method.
* @param array $r Array of parsed arguments for get() method.
*/
$total_signups = $wpdb->get_var(apply_filters('bp_members_signups_count_query', join(' ', $sql), $sql, $args, $r));
return array('signups' => $paged_signups, 'total' => $total_signups);
}
/**
* Get a total count of all topics of a given status, across groups/forums.
*
* @since 1.5.0
*
* @param string $status Which group type to count. 'public', 'private',
* 'hidden', or 'all'. Default: 'public'.
* @param string|bool $search_terms Provided search terms.
* @return int The topic count
*/
public static function get_global_topic_count($status = 'public', $search_terms = false)
{
global $bbdb, $wpdb;
switch ($status) {
case 'all':
$status_sql = '';
break;
case 'hidden':
$status_sql = "AND g.status = 'hidden'";
break;
case 'private':
$status_sql = "AND g.status = 'private'";
break;
case 'public':
default:
$status_sql = "AND g.status = 'public'";
break;
}
$bp = buddypress();
$sql = array();
$sql['select'] = "SELECT COUNT(t.topic_id)";
$sql['from'] = "FROM {$bbdb->topics} AS t INNER JOIN {$bp->groups->table_name_groupmeta} AS gm ON t.forum_id = gm.meta_value INNER JOIN {$bp->groups->table_name} AS g ON gm.group_id = g.id";
$sql['where'] = "WHERE gm.meta_key = 'forum_id' {$status_sql} AND t.topic_status = '0' AND t.topic_sticky != '2'";
if (!empty($search_terms)) {
$search_terms_like = '%' . bp_esc_like($search_terms) . '%';
$sql['where'] .= $wpdb->prepare(" AND ( t.topic_title LIKE %s )", $search_terms_like);
}
return $wpdb->get_var(implode(' ', $sql));
}
/**
* Generate SQL JOIN and WHERE clauses for a first-order query clause.
*
* "First-order" means that it's an array with a 'field' or 'value'.
*
* @since 2.2.0
*
* @param array $clause Query clause.
* @param array $parent_query Parent query array.
* @return array {
* Array containing JOIN and WHERE SQL clauses to append to a first-order query.
*
* @type string $join SQL fragment to append to the main JOIN clause.
* @type string $where SQL fragment to append to the main WHERE clause.
* }
*/
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;
}
/**
* Get activity items, as specified by parameters.
*
* @since 1.0.0
* @since 2.4.0 Introduced the `$fields` parameter.
*
* @see BP_Activity_Activity::get_filter_sql() for a description of the
* 'filter' parameter.
* @see WP_Meta_Query::queries for a description of the 'meta_query'
* parameter format.
*
* @param array $args {
* An array of arguments. All items are optional.
* @type int $page Which page of results to fetch. Using page=1 without per_page will result
* in no pagination. Default: 1.
* @type int|bool $per_page Number of results per page. Default: 25.
* @type int|bool $max Maximum number of results to return. Default: false (unlimited).
* @type string $fields Activity fields to return. Pass 'ids' to get only the activity IDs.
* 'all' returns full activity objects.
* @type string $sort ASC or DESC. Default: 'DESC'.
* @type array $exclude Array of activity IDs to exclude. Default: false.
* @type array $in Array of ids to limit query by (IN). Default: false.
* @type array $meta_query Array of meta_query conditions. See WP_Meta_Query::queries.
* @type array $date_query Array of date_query conditions. See first parameter of
* WP_Date_Query::__construct().
* @type array $filter_query Array of advanced query conditions. See BP_Activity_Query::__construct().
* @type string|array $scope Pre-determined set of activity arguments.
* @type array $filter See BP_Activity_Activity::get_filter_sql().
* @type string $search_terms Limit results by a search term. Default: false.
* @type bool $display_comments Whether to include activity comments. Default: false.
* @type bool $show_hidden Whether to show items marked hide_sitewide. Default: false.
* @type string $spam Spam status. Default: 'ham_only'.
* @type bool $update_meta_cache Whether to pre-fetch metadata for queried activity items. Default: true.
* @type string|bool $count_total If true, an additional DB query is run to count the total activity items
* for the query. Default: false.
* }
* @return array The array returned has two keys:
* - 'total' is the count of located activities
* - 'activities' is an array of the located activities
*/
public static function get($args = array())
{
global $wpdb;
// Backward compatibility with old method of passing arguments.
if (!is_array($args) || func_num_args() > 1) {
_deprecated_argument(__METHOD__, '1.6', sprintf(__('Arguments passed to %1$s should be in an associative array. See the inline documentation at %2$s for more details.', 'buddypress'), __METHOD__, __FILE__));
$old_args_keys = array(0 => 'max', 1 => 'page', 2 => 'per_page', 3 => 'sort', 4 => 'search_terms', 5 => 'filter', 6 => 'display_comments', 7 => 'show_hidden', 8 => 'exclude', 9 => 'in', 10 => 'spam');
$func_args = func_get_args();
$args = bp_core_parse_args_array($old_args_keys, $func_args);
}
$bp = buddypress();
$r = wp_parse_args($args, array('page' => 1, 'per_page' => 25, 'max' => false, 'fields' => 'all', 'sort' => 'DESC', 'exclude' => false, 'in' => false, 'meta_query' => false, 'date_query' => false, 'filter_query' => false, 'filter' => false, 'scope' => false, 'search_terms' => false, 'display_comments' => false, 'show_hidden' => false, 'spam' => 'ham_only', 'update_meta_cache' => true, 'count_total' => false));
// Select conditions.
$select_sql = "SELECT DISTINCT a.id";
$from_sql = " FROM {$bp->activity->table_name} a";
$join_sql = '';
// Where conditions.
$where_conditions = array();
// Excluded types.
$excluded_types = array();
// Scope takes precedence.
if (!empty($r['scope'])) {
$scope_query = self::get_scope_query_sql($r['scope'], $r);
// Add our SQL conditions if matches were found.
if (!empty($scope_query['sql'])) {
$where_conditions['scope_query_sql'] = $scope_query['sql'];
}
// Override some arguments if needed.
if (!empty($scope_query['override'])) {
$r = self::array_replace_recursive($r, $scope_query['override']);
}
// Advanced filtering.
} elseif (!empty($r['filter_query'])) {
$filter_query = new BP_Activity_Query($r['filter_query']);
$sql = $filter_query->get_sql();
if (!empty($sql)) {
$where_conditions['filter_query_sql'] = $sql;
}
}
// Regular filtering.
if ($r['filter'] && ($filter_sql = BP_Activity_Activity::get_filter_sql($r['filter']))) {
$where_conditions['filter_sql'] = $filter_sql;
}
// Spam.
if ('ham_only' == $r['spam']) {
$where_conditions['spam_sql'] = 'a.is_spam = 0';
} elseif ('spam_only' == $r['spam']) {
$where_conditions['spam_sql'] = 'a.is_spam = 1';
}
// Searching.
if ($r['search_terms']) {
$search_terms_like = '%' . bp_esc_like($r['search_terms']) . '%';
$where_conditions['search_sql'] = $wpdb->prepare('a.content LIKE %s', $search_terms_like);
}
// Sorting.
$sort = $r['sort'];
if ($sort != 'ASC' && $sort != 'DESC') {
$sort = 'DESC';
}
// Hide Hidden Items?
if (!$r['show_hidden']) {
$where_conditions['hidden_sql'] = "a.hide_sitewide = 0";
}
// Exclude specified items.
if (!empty($r['exclude'])) {
$exclude = implode(',', wp_parse_id_list($r['exclude']));
$where_conditions['exclude'] = "a.id NOT IN ({$exclude})";
}
// The specific ids to which you want to limit the query.
if (!empty($r['in'])) {
$in = implode(',', wp_parse_id_list($r['in']));
$where_conditions['in'] = "a.id IN ({$in})";
}
// Process meta_query into SQL.
$meta_query_sql = self::get_meta_query_sql($r['meta_query']);
if (!empty($meta_query_sql['join'])) {
$join_sql .= $meta_query_sql['join'];
}
if (!empty($meta_query_sql['where'])) {
$where_conditions[] = $meta_query_sql['where'];
}
// Process date_query into SQL.
$date_query_sql = self::get_date_query_sql($r['date_query']);
if (!empty($date_query_sql)) {
$where_conditions['date'] = $date_query_sql;
}
// Alter the query based on whether we want to show activity item
// comments in the stream like normal comments or threaded below
// the activity.
if (false === $r['display_comments'] || 'threaded' === $r['display_comments']) {
$excluded_types[] = 'activity_comment';
}
// Exclude 'last_activity' items unless the 'action' filter has
// been explicitly set.
if (empty($r['filter']['object'])) {
$excluded_types[] = 'last_activity';
}
// Build the excluded type sql part.
if (!empty($excluded_types)) {
$not_in = "'" . implode("', '", esc_sql($excluded_types)) . "'";
$where_conditions['excluded_types'] = "a.type NOT IN ({$not_in})";
}
/**
* Filters the MySQL WHERE conditions for the Activity items get method.
*
* @since 1.9.0
*
* @param array $where_conditions Current conditions for MySQL WHERE statement.
* @param array $r Parsed arguments passed into method.
* @param string $select_sql Current SELECT MySQL statement at point of execution.
* @param string $from_sql Current FROM MySQL statement at point of execution.
* @param string $join_sql Current INNER JOIN MySQL statement at point of execution.
*/
$where_conditions = apply_filters('bp_activity_get_where_conditions', $where_conditions, $r, $select_sql, $from_sql, $join_sql);
// Join the where conditions together.
$where_sql = 'WHERE ' . join(' AND ', $where_conditions);
/**
* Filters the preferred order of indexes for activity item.
*
* @since 1.6.0
*
* @param array $value Array of indexes in preferred order.
*/
$indexes = apply_filters('bp_activity_preferred_index_order', array('user_id', 'item_id', 'secondary_item_id', 'date_recorded', 'component', 'type', 'hide_sitewide', 'is_spam'));
foreach ($indexes as $key => $index) {
if (false !== strpos($where_sql, $index)) {
$the_index = $index;
break;
// Take the first one we find.
}
}
if (!empty($the_index)) {
$index_hint_sql = "USE INDEX ({$the_index})";
} else {
$index_hint_sql = '';
}
// Sanitize page and per_page parameters.
$page = absint($r['page']);
$per_page = absint($r['per_page']);
$retval = array('activities' => null, 'total' => null, 'has_more_items' => null);
/**
* Filters if BuddyPress should use legacy query structure over current structure for version 2.0+.
*
* It is not recommended to use the legacy structure, but allowed to if needed.
*
* @since 2.0.0
*
* @param bool $value Whether to use legacy structure or not.
* @param BP_Activity_Activity $value Current method being called.
* @param array $r Parsed arguments passed into method.
*/
if (apply_filters('bp_use_legacy_activity_query', false, __METHOD__, $r)) {
// Legacy queries joined against the user table.
$select_sql = "SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name";
$from_sql = " FROM {$bp->activity->table_name} a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
if (!empty($page) && !empty($per_page)) {
$pag_sql = $wpdb->prepare("LIMIT %d, %d", absint(($page - 1) * $per_page), $per_page);
/** This filter is documented in bp-activity/bp-activity-classes.php */
$activities = $wpdb->get_results(apply_filters('bp_activity_get_user_join_filter', "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort} {$pag_sql}", $select_sql, $from_sql, $where_sql, $sort, $pag_sql));
} else {
$pag_sql = '';
/**
* Filters the legacy MySQL query statement so plugins can alter before results are fetched.
*
* @since 1.5.0
*
* @param string $value Concatenated MySQL statement pieces to be query results with for legacy query.
* @param string $select_sql Final SELECT MySQL statement portion for legacy query.
* @param string $from_sql Final FROM MySQL statement portion for legacy query.
* @param string $where_sql Final WHERE MySQL statement portion for legacy query.
* @param string $sort Final sort direction for legacy query.
*/
$activities = $wpdb->get_results(apply_filters('bp_activity_get_user_join_filter', "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}", $select_sql, $from_sql, $where_sql, $sort, $pag_sql));
}
} else {
// Query first for activity IDs.
$activity_ids_sql = "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}";
if (!empty($per_page) && !empty($page)) {
// We query for $per_page + 1 items in order to
// populate the has_more_items flag.
$activity_ids_sql .= $wpdb->prepare(" LIMIT %d, %d", absint(($page - 1) * $per_page), $per_page + 1);
}
/**
* Filters the paged activities MySQL statement.
*
* @since 2.0.0
*
* @param string $activity_ids_sql MySQL statement used to query for Activity IDs.
* @param array $r Array of arguments passed into method.
*/
$activity_ids_sql = apply_filters('bp_activity_paged_activities_sql', $activity_ids_sql, $r);
$activity_ids = $wpdb->get_col($activity_ids_sql);
$retval['has_more_items'] = !empty($per_page) && count($activity_ids) > $per_page;
// If we've fetched more than the $per_page value, we
// can discard the extra now.
if (!empty($per_page) && count($activity_ids) === $per_page + 1) {
array_pop($activity_ids);
}
if ('ids' === $r['fields']) {
$activities = array_map('intval', $activity_ids);
} else {
$activities = self::get_activity_data($activity_ids);
}
}
if ('ids' !== $r['fields']) {
// Get the fullnames of users so we don't have to query in the loop.
$activities = self::append_user_fullnames($activities);
// Get activity meta.
$activity_ids = array();
foreach ((array) $activities as $activity) {
$activity_ids[] = $activity->id;
}
if (!empty($activity_ids) && $r['update_meta_cache']) {
bp_activity_update_meta_cache($activity_ids);
}
if ($activities && $r['display_comments']) {
$activities = BP_Activity_Activity::append_comments($activities, $r['spam']);
}
// Pre-fetch data associated with activity users and other objects.
BP_Activity_Activity::prefetch_object_data($activities);
// Generate action strings.
$activities = BP_Activity_Activity::generate_action_strings($activities);
}
$retval['activities'] = $activities;
// If $max is set, only return up to the max results.
if (!empty($r['count_total'])) {
/**
* Filters the total activities MySQL statement.
*
* @since 1.5.0
*
* @param string $value MySQL statement used to query for total activities.
* @param string $where_sql MySQL WHERE statement portion.
* @param string $sort Sort direction for query.
*/
$total_activities_sql = apply_filters('bp_activity_total_activities_sql', "SELECT count(DISTINCT a.id) FROM {$bp->activity->table_name} a {$join_sql} {$where_sql}", $where_sql, $sort);
$total_activities = $wpdb->get_var($total_activities_sql);
if (!empty($r['max'])) {
if ((int) $total_activities > (int) $r['max']) {
$total_activities = $r['max'];
}
}
$retval['total'] = $total_activities;
}
return $retval;
}
/**
* Assemble the WHERE clause of a get() SQL statement.
*
* Used by BP_Notifications_Notification::get() to create its WHERE
* clause.
*
* @since BuddyPress (1.9.0)
*
* @param array $args See {@link BP_Notifications_Notification::get()}
* for more details.
* @return string WHERE clause.
*/
protected static function get_where_sql($args = array())
{
global $wpdb;
$where_conditions = array();
$where = '';
// id
if (!empty($args['id'])) {
$id_in = implode(',', wp_parse_id_list($args['id']));
$where_conditions['id'] = "id IN ({$id_in})";
}
// user_id
if (!empty($args['user_id'])) {
$user_id_in = implode(',', wp_parse_id_list($args['user_id']));
$where_conditions['user_id'] = "user_id IN ({$user_id_in})";
}
// item_id
if (!empty($args['item_id'])) {
$item_id_in = implode(',', wp_parse_id_list($args['item_id']));
$where_conditions['item_id'] = "item_id IN ({$item_id_in})";
}
// secondary_item_id
if (!empty($args['secondary_item_id'])) {
$secondary_item_id_in = implode(',', wp_parse_id_list($args['secondary_item_id']));
$where_conditions['secondary_item_id'] = "secondary_item_id IN ({$secondary_item_id_in})";
}
// component_name
if (!empty($args['component_name'])) {
if (!is_array($args['component_name'])) {
$component_names = explode(',', $args['component_name']);
} else {
$component_names = $args['component_name'];
}
$cn_clean = array();
foreach ($component_names as $cn) {
$cn_clean[] = $wpdb->prepare('%s', $cn);
}
$cn_in = implode(',', $cn_clean);
$where_conditions['component_name'] = "component_name IN ({$cn_in})";
}
// component_action
if (!empty($args['component_action'])) {
if (!is_array($args['component_action'])) {
$component_actions = explode(',', $args['component_action']);
} else {
$component_actions = $args['component_action'];
}
$ca_clean = array();
foreach ($component_actions as $ca) {
$ca_clean[] = $wpdb->prepare('%s', $ca);
}
$ca_in = implode(',', $ca_clean);
$where_conditions['component_action'] = "component_action IN ({$ca_in})";
}
// is_new
if (!empty($args['is_new']) && 'both' !== $args['is_new']) {
$where_conditions['is_new'] = "is_new = 1";
} elseif (isset($args['is_new']) && (0 === $args['is_new'] || false === $args['is_new'])) {
$where_conditions['is_new'] = "is_new = 0";
}
// search_terms
if (!empty($args['search_terms'])) {
$search_terms_like = '%' . bp_esc_like($args['search_terms']) . '%';
$where_conditions['search_terms'] = $wpdb->prepare("( component_name LIKE %s OR component_action LIKE %s )", $search_terms_like, $search_terms_like);
}
// Custom WHERE
if (!empty($where_conditions)) {
$where = 'WHERE ' . implode(' AND ', $where_conditions);
}
return $where;
}
/**
* Perform database operations that must take place before the general schema upgrades.
*
* `dbDelta()` cannot handle certain operations - like changing indexes - so we do it here instead.
*
* @since 2.3.0
*/
function bp_pre_schema_upgrade()
{
global $wpdb;
$raw_db_version = (int) bp_get_db_version_raw();
$bp_prefix = bp_core_get_table_prefix();
// 2.3.0: Change index lengths to account for utf8mb4.
if ($raw_db_version < 9695) {
// table_name => columns.
$tables = array($bp_prefix . 'bp_activity_meta' => array('meta_key'), $bp_prefix . 'bp_groups_groupmeta' => array('meta_key'), $bp_prefix . 'bp_messages_meta' => array('meta_key'), $bp_prefix . 'bp_notifications_meta' => array('meta_key'), $bp_prefix . 'bp_user_blogs_blogmeta' => array('meta_key'), $bp_prefix . 'bp_xprofile_meta' => array('meta_key'));
foreach ($tables as $table_name => $indexes) {
foreach ($indexes as $index) {
if ($wpdb->query($wpdb->prepare("SHOW TABLES LIKE %s", bp_esc_like($table_name)))) {
$wpdb->query("ALTER TABLE {$table_name} DROP INDEX {$index}");
}
}
}
}
}
/**
* Get activity items, as specified by parameters
*
* @see BP_Activity_Activity::get_filter_sql() for a description of the
* 'filter' parameter.
* @see WP_Meta_Query::queries for a description of the 'meta_query'
* parameter format.
*
* @param array $args {
* An array of arguments. All items are optional.
* @type int $page Which page of results to fetch. Using page=1
* without per_page will result in no pagination.
* Default: 1.
* @type int|bool $per_page Number of results per page. Default: 25.
* @type int|bool $max Maximum number of results to return.
* Default: false (unlimited).
* @type string $sort ASC or DESC. Default: 'DESC'.
* @type array $exclude Array of activity IDs to exclude.
* Default: false.
* @type array $in Array of ids to limit query by (IN).
* Default: false.
* @type array $meta_query An array of meta_query conditions.
* See WP_Meta_Query::queries for description.
* @type array $date_query An array of date_query conditions.
* See first parameter of WP_Date_Query::__construct()
* for description.
* @type array $filter See BP_Activity_Activity::get_filter_sql().
* @type string $search_terms Limit results by a search term.
* Default: false.
* @type bool $display_comments Whether to include activity comments.
* Default: false.
* @type bool $show_hidden Whether to show items marked hide_sitewide.
* Default: false.
* @type string $spam Spam status. Default: 'ham_only'.
* @type bool $update_meta_cache Whether to pre-fetch metadata for
* queried activity items. Default: true.
* @type string|bool $count_total If true, an additional DB query
* is run to count the total activity items for the query.
* Default: false.
* }
* @return array The array returned has two keys:
* - 'total' is the count of located activities
* - 'activities' is an array of the located activities
*/
public static function get($args = array())
{
global $wpdb, $bp;
// Backward compatibility with old method of passing arguments
if (!is_array($args) || func_num_args() > 1) {
_deprecated_argument(__METHOD__, '1.6', sprintf(__('Arguments passed to %1$s should be in an associative array. See the inline documentation at %2$s for more details.', 'buddypress'), __METHOD__, __FILE__));
$old_args_keys = array(0 => 'max', 1 => 'page', 2 => 'per_page', 3 => 'sort', 4 => 'search_terms', 5 => 'filter', 6 => 'display_comments', 7 => 'show_hidden', 8 => 'exclude', 9 => 'in', 10 => 'spam');
$func_args = func_get_args();
$args = bp_core_parse_args_array($old_args_keys, $func_args);
}
$defaults = array('page' => 1, 'per_page' => 25, 'max' => false, 'sort' => 'DESC', 'exclude' => false, 'in' => false, 'meta_query' => false, 'date_query' => false, 'filter' => false, 'search_terms' => false, 'display_comments' => false, 'show_hidden' => false, 'spam' => 'ham_only', 'update_meta_cache' => true, 'count_total' => false);
$r = wp_parse_args($args, $defaults);
extract($r);
// Select conditions
$select_sql = "SELECT DISTINCT a.id";
$from_sql = " FROM {$bp->activity->table_name} a";
$join_sql = '';
// Where conditions
$where_conditions = array();
// Excluded types
$excluded_types = array();
// Spam
if ('ham_only' == $spam) {
$where_conditions['spam_sql'] = 'a.is_spam = 0';
} elseif ('spam_only' == $spam) {
$where_conditions['spam_sql'] = 'a.is_spam = 1';
}
// Searching
if ($search_terms) {
$search_terms_like = '%' . bp_esc_like($search_terms) . '%';
$where_conditions['search_sql'] = $wpdb->prepare('a.content LIKE %s', $search_terms_like);
}
// Filtering
if ($filter && ($filter_sql = BP_Activity_Activity::get_filter_sql($filter))) {
$where_conditions['filter_sql'] = $filter_sql;
}
// Sorting
if ($sort != 'ASC' && $sort != 'DESC') {
$sort = 'DESC';
}
// Hide Hidden Items?
if (!$show_hidden) {
$where_conditions['hidden_sql'] = "a.hide_sitewide = 0";
}
// Exclude specified items
if (!empty($exclude)) {
$exclude = implode(',', wp_parse_id_list($exclude));
$where_conditions['exclude'] = "a.id NOT IN ({$exclude})";
}
// The specific ids to which you want to limit the query
if (!empty($in)) {
$in = implode(',', wp_parse_id_list($in));
$where_conditions['in'] = "a.id IN ({$in})";
}
// Process meta_query into SQL
$meta_query_sql = self::get_meta_query_sql($meta_query);
if (!empty($meta_query_sql['join'])) {
$join_sql .= $meta_query_sql['join'];
}
if (!empty($meta_query_sql['where'])) {
$where_conditions[] = $meta_query_sql['where'];
}
// Process date_query into SQL
$date_query_sql = self::get_date_query_sql($date_query);
if (!empty($date_query_sql)) {
$where_conditions['date'] = $date_query_sql;
}
// Alter the query based on whether we want to show activity item
// comments in the stream like normal comments or threaded below
// the activity.
if (false === $display_comments || 'threaded' === $display_comments) {
$excluded_types[] = 'activity_comment';
}
// Exclude 'last_activity' items unless the 'action' filter has
// been explicitly set
if (empty($filter['object'])) {
$excluded_types[] = 'last_activity';
}
// Exclude 'new_member' items if xprofile component is not active
if (!bp_is_active('xprofile')) {
$excluded_types[] = 'new_member';
}
// Build the excluded type sql part
if (!empty($excluded_types)) {
$not_in = "'" . implode("', '", esc_sql($excluded_types)) . "'";
$where_conditions['excluded_types'] = "a.type NOT IN ({$not_in})";
}
// Filter the where conditions
$where_conditions = apply_filters('bp_activity_get_where_conditions', $where_conditions, $r, $select_sql, $from_sql, $join_sql);
// Join the where conditions together
$where_sql = 'WHERE ' . join(' AND ', $where_conditions);
// Define the preferred order for indexes
$indexes = apply_filters('bp_activity_preferred_index_order', array('user_id', 'item_id', 'secondary_item_id', 'date_recorded', 'component', 'type', 'hide_sitewide', 'is_spam'));
foreach ($indexes as $key => $index) {
if (false !== strpos($where_sql, $index)) {
$the_index = $index;
break;
// Take the first one we find
}
}
if (!empty($the_index)) {
$index_hint_sql = "USE INDEX ({$the_index})";
} else {
$index_hint_sql = '';
}
// Sanitize page and per_page parameters
$page = absint($page);
$per_page = absint($per_page);
$retval = array('activities' => null, 'total' => null, 'has_more_items' => null);
// Filter and return true to use the legacy query structure (not recommended)
if (apply_filters('bp_use_legacy_activity_query', false, __METHOD__, $r)) {
// Legacy queries joined against the user table
$select_sql = "SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name";
$from_sql = " FROM {$bp->activity->table_name} a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
if (!empty($page) && !empty($per_page)) {
$pag_sql = $wpdb->prepare("LIMIT %d, %d", absint(($page - 1) * $per_page), $per_page);
$activities = $wpdb->get_results(apply_filters('bp_activity_get_user_join_filter', "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort} {$pag_sql}", $select_sql, $from_sql, $where_sql, $sort, $pag_sql));
} else {
$activities = $wpdb->get_results(apply_filters('bp_activity_get_user_join_filter', "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}", $select_sql, $from_sql, $where_sql, $sort));
}
} else {
// Query first for activity IDs
$activity_ids_sql = "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}";
if (!empty($per_page) && !empty($page)) {
// We query for $per_page + 1 items in order to
// populate the has_more_items flag
$activity_ids_sql .= $wpdb->prepare(" LIMIT %d, %d", absint(($page - 1) * $per_page), $per_page + 1);
}
$activity_ids_sql = apply_filters('bp_activity_paged_activities_sql', $activity_ids_sql, $r);
$activity_ids = $wpdb->get_col($activity_ids_sql);
$retval['has_more_items'] = !empty($per_page) && count($activity_ids) > $per_page;
// If we've fetched more than the $per_page value, we
// can discard the extra now
if (!empty($per_page) && count($activity_ids) === $per_page + 1) {
array_pop($activity_ids);
}
$activities = self::get_activity_data($activity_ids);
}
// Get the fullnames of users so we don't have to query in the loop
$activities = self::append_user_fullnames($activities);
// Get activity meta
$activity_ids = array();
foreach ((array) $activities as $activity) {
$activity_ids[] = $activity->id;
}
if (!empty($activity_ids) && $update_meta_cache) {
bp_activity_update_meta_cache($activity_ids);
}
if ($activities && $display_comments) {
$activities = BP_Activity_Activity::append_comments($activities, $spam);
}
// Pre-fetch data associated with activity users and other objects
BP_Activity_Activity::prefetch_object_data($activities);
// Generate action strings
$activities = BP_Activity_Activity::generate_action_strings($activities);
$retval['activities'] = $activities;
// If $max is set, only return up to the max results
if (!empty($r['count_total'])) {
$total_activities_sql = apply_filters('bp_activity_total_activities_sql', "SELECT count(DISTINCT a.id) FROM {$bp->activity->table_name} a {$join_sql} {$where_sql}", $where_sql, $sort);
$total_activities = $wpdb->get_var($total_activities_sql);
if (!empty($max)) {
if ((int) $total_activities > (int) $max) {
$total_activities = $max;
}
}
$retval['total'] = $total_activities;
}
return $retval;
}
/**
* Generate WHERE clauses for a first-order clause.
*
* @since BuddyPress (2.2.0)
* @access protected
*
* @param array $clause Array of arguments belonging to the clause.
* @param array $parent_query Parent query to which the clause belongs.
* @return array {
* @type array $where Array of subclauses for the WHERE statement.
* @type array $join Empty array. Not used.
* }
*/
protected function get_sql_for_clause($clause, $parent_query)
{
global $wpdb;
$sql_chunks = array('where' => array(), 'join' => array());
$column = isset($clause['column']) ? $this->validate_column($clause['column']) : '';
$value = isset($clause['value']) ? $clause['value'] : '';
if (empty($column) || !isset($clause['value'])) {
return $sql_chunks;
}
if (isset($clause['compare'])) {
$clause['compare'] = strtoupper($clause['compare']);
} else {
$clause['compare'] = isset($clause['value']) && is_array($clause['value']) ? 'IN' : '=';
}
// default 'compare' to '=' if no valid operator is found
if (!in_array($clause['compare'], array('=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'REGEXP', 'NOT REGEXP', 'RLIKE'))) {
$clause['compare'] = '=';
}
$compare = $clause['compare'];
$alias = !empty($this->table_alias) ? "{$this->table_alias}." : '';
// Next, Build the WHERE clause.
$where = '';
// value.
if (isset($clause['value'])) {
if (in_array($compare, array('IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN'))) {
if (!is_array($value)) {
$value = preg_split('/[,\\s]+/', $value);
}
}
// tinyint
if (!empty($column) && true === in_array($column, array('hide_sitewide', 'is_spam'))) {
$sql_chunks['where'][] = $wpdb->prepare("{$alias}{$column} = %d", $value);
} else {
switch ($compare) {
// IN uses different syntax
case 'IN':
case 'NOT IN':
$in_sql = BP_Activity_Activity::get_in_operator_sql("{$alias}{$column}", $value);
// 'NOT IN' operator is as easy as a string replace!
if ('NOT IN' === $compare) {
$in_sql = str_replace('IN', 'NOT IN', $in_sql);
}
$sql_chunks['where'][] = $in_sql;
break;
case 'BETWEEN':
case 'NOT BETWEEN':
$value = array_slice($value, 0, 2);
$where = $wpdb->prepare('%s AND %s', $value);
break;
case 'LIKE':
case 'NOT LIKE':
$value = '%' . bp_esc_like($value) . '%';
$where = $wpdb->prepare('%s', $value);
break;
default:
$where = $wpdb->prepare('%s', $value);
break;
}
}
if ($where) {
$sql_chunks['where'][] = "{$alias}{$column} {$compare} {$where}";
}
}
/*
* Multiple WHERE clauses should be joined in parentheses.
*/
if (1 < count($sql_chunks['where'])) {
$sql_chunks['where'] = array('( ' . implode(' AND ', $sql_chunks['where']) . ' )');
}
return $sql_chunks;
}
/**
* Assemble the WHERE clause of a get() SQL statement.
*
* Used by BP_Notifications_Notification::get() to create its WHERE
* clause.
*
* @since 1.9.0
*
* @param array $args See {@link BP_Notifications_Notification::get()}
* for more details.
* @param string $select_sql SQL SELECT fragment.
* @param string $from_sql SQL FROM fragment.
* @param string $join_sql SQL JOIN fragment.
* @param string $meta_query_sql SQL meta query fragment.
* @return string WHERE clause.
*/
protected static function get_where_sql($args = array(), $select_sql = '', $from_sql = '', $join_sql = '', $meta_query_sql = '')
{
global $wpdb;
$where_conditions = array();
$where = '';
// The id.
if (!empty($args['id'])) {
$id_in = implode(',', wp_parse_id_list($args['id']));
$where_conditions['id'] = "id IN ({$id_in})";
}
// The user_id.
if (!empty($args['user_id'])) {
$user_id_in = implode(',', wp_parse_id_list($args['user_id']));
$where_conditions['user_id'] = "user_id IN ({$user_id_in})";
}
// The item_id.
if (!empty($args['item_id'])) {
$item_id_in = implode(',', wp_parse_id_list($args['item_id']));
$where_conditions['item_id'] = "item_id IN ({$item_id_in})";
}
// The secondary_item_id.
if (!empty($args['secondary_item_id'])) {
$secondary_item_id_in = implode(',', wp_parse_id_list($args['secondary_item_id']));
$where_conditions['secondary_item_id'] = "secondary_item_id IN ({$secondary_item_id_in})";
}
// The component_name.
if (!empty($args['component_name'])) {
if (!is_array($args['component_name'])) {
$component_names = explode(',', $args['component_name']);
} else {
$component_names = $args['component_name'];
}
$cn_clean = array();
foreach ($component_names as $cn) {
$cn_clean[] = $wpdb->prepare('%s', $cn);
}
$cn_in = implode(',', $cn_clean);
$where_conditions['component_name'] = "component_name IN ({$cn_in})";
}
// The component_action.
if (!empty($args['component_action'])) {
if (!is_array($args['component_action'])) {
$component_actions = explode(',', $args['component_action']);
} else {
$component_actions = $args['component_action'];
}
$ca_clean = array();
foreach ($component_actions as $ca) {
$ca_clean[] = $wpdb->prepare('%s', $ca);
}
$ca_in = implode(',', $ca_clean);
$where_conditions['component_action'] = "component_action IN ({$ca_in})";
}
// If is_new.
if (!empty($args['is_new']) && 'both' !== $args['is_new']) {
$where_conditions['is_new'] = "is_new = 1";
} elseif (isset($args['is_new']) && (0 === $args['is_new'] || false === $args['is_new'])) {
$where_conditions['is_new'] = "is_new = 0";
}
// The search_terms.
if (!empty($args['search_terms'])) {
$search_terms_like = '%' . bp_esc_like($args['search_terms']) . '%';
$where_conditions['search_terms'] = $wpdb->prepare("( component_name LIKE %s OR component_action LIKE %s )", $search_terms_like, $search_terms_like);
}
// The date query.
if (!empty($args['date_query'])) {
$where_conditions['date_query'] = self::get_date_query_sql($args['date_query']);
}
// The meta query.
if (!empty($meta_query_sql['where'])) {
$where_conditions['meta_query'] = $meta_query_sql['where'];
}
/**
* Filters the MySQL WHERE conditions for the Notifications items get method.
*
* @since 2.3.0
*
* @param array $where_conditions Current conditions for MySQL WHERE statement.
* @param array $args Parsed arguments passed into method.
* @param string $select_sql Current SELECT MySQL statement at point of execution.
* @param string $from_sql Current FROM MySQL statement at point of execution.
* @param string $join_sql Current INNER JOIN MySQL statement at point of execution.
* @param string $meta_query_sql Current meta query WHERE statement at point of execution.
*/
$where_conditions = apply_filters('bp_notifications_get_where_conditions', $where_conditions, $args, $select_sql, $from_sql, $join_sql, $meta_query_sql);
// Custom WHERE.
if (!empty($where_conditions)) {
$where = 'WHERE ' . implode(' AND ', $where_conditions);
}
return $where;
}
/**
* Get a count of users who match a search term.
*
* @todo Why does this exist, and why is it in bp-friends?
*
* @param string $filter Search term.
*
* @return int Count of users matching the search term.
*/
public static function search_users_count($filter)
{
global $wpdb;
// Only search for matching strings at the beginning of the
// name (@todo - figure out why this restriction)
$search_terms_like = bp_esc_like($filter) . '%';
$usermeta_table = $wpdb->prefix . 'usermeta';
$users_table = $wpdb->base_prefix . 'users';
$bp = buddypress();
// filter the user_ids based on the search criteria.
if (bp_is_active('xprofile')) {
$sql = $wpdb->prepare("SELECT COUNT(DISTINCT d.user_id) FROM {$bp->profile->table_name_data} d, {$users_table} u WHERE d.user_id = u.id AND d.value LIKE %s", $search_terms_like);
} else {
$sql = $wpdb->prepare("SELECT COUNT(DISTINCT user_id) FROM {$usermeta_table} WHERE meta_value LIKE %s", $search_terms_like);
}
$user_count = $wpdb->get_col($sql);
if (empty($user_count)) {
return false;
}
return $user_count[0];
}
/**
* Get current message threads for a user.
*
* @since 1.0.0
*
* @param array $args {
* Array of arguments.
* @type int $user_id The user ID.
* @type string $box The type of mailbox to get. Either 'inbox' or 'sentbox'.
* Defaults to 'inbox'.
* @type string $type The type of messages to get. Either 'all' or 'unread'
* or 'read'. Defaults to 'all'.
* @type int $limit The number of messages to get. Defaults to null.
* @type int $page The page number to get. Defaults to null.
* @type string $search_terms The search term to use. Defaults to ''.
* @type array $meta_query Meta query arguments. See WP_Meta_Query for more details.
* }
* @return array|bool Array on success. Boolean false on failure.
*/
public static function get_current_threads_for_user($args = array())
{
global $wpdb;
// Backward compatibility with old method of passing arguments
if (!is_array($args) || func_num_args() > 1) {
_deprecated_argument(__METHOD__, '2.2.0', sprintf(__('Arguments passed to %1$s should be in an associative array. See the inline documentation at %2$s for more details.', 'buddypress'), __METHOD__, __FILE__));
$old_args_keys = array(0 => 'user_id', 1 => 'box', 2 => 'type', 3 => 'limit', 4 => 'page', 5 => 'search_terms');
$func_args = func_get_args();
$args = bp_core_parse_args_array($old_args_keys, $func_args);
}
$r = bp_parse_args($args, array('user_id' => false, 'box' => 'inbox', 'type' => 'all', 'limit' => null, 'page' => null, 'search_terms' => '', 'meta_query' => array()));
$pag_sql = $type_sql = $search_sql = $user_id_sql = $sender_sql = '';
$meta_query_sql = array('join' => '', 'where' => '');
if ($r['limit'] && $r['page']) {
$pag_sql = $wpdb->prepare(" LIMIT %d, %d", intval(($r['page'] - 1) * $r['limit']), intval($r['limit']));
}
if ($r['type'] == 'unread') {
$type_sql = " AND r.unread_count != 0 ";
} elseif ($r['type'] == 'read') {
$type_sql = " AND r.unread_count = 0 ";
}
if (!empty($r['search_terms'])) {
$search_terms_like = '%' . bp_esc_like($r['search_terms']) . '%';
$search_sql = $wpdb->prepare("AND ( subject LIKE %s OR message LIKE %s )", $search_terms_like, $search_terms_like);
}
$r['user_id'] = (int) $r['user_id'];
// Default deleted SQL
$deleted_sql = 'r.is_deleted = 0';
switch ($r['box']) {
case 'sentbox':
$user_id_sql = 'AND ' . $wpdb->prepare('m.sender_id = %d', $r['user_id']);
$sender_sql = 'AND m.sender_id = r.user_id';
break;
case 'inbox':
$user_id_sql = 'AND ' . $wpdb->prepare('r.user_id = %d', $r['user_id']);
$sender_sql = 'AND r.sender_only = 0';
break;
default:
// Omit user-deleted threads from all other custom message boxes
$deleted_sql = $wpdb->prepare('( r.user_id = %d AND r.is_deleted = 0 )', $r['user_id']);
break;
}
// Process meta query into SQL
$meta_query = self::get_meta_query_sql($r['meta_query']);
if (!empty($meta_query['join'])) {
$meta_query_sql['join'] = $meta_query['join'];
}
if (!empty($meta_query['where'])) {
$meta_query_sql['where'] = $meta_query['where'];
}
$bp = buddypress();
// set up SQL array
$sql = array();
$sql['select'] = 'SELECT m.thread_id, MAX(m.date_sent) AS date_sent';
$sql['from'] = "FROM {$bp->messages->table_name_recipients} r INNER JOIN {$bp->messages->table_name_messages} m ON m.thread_id = r.thread_id {$meta_query_sql['join']}";
$sql['where'] = "WHERE {$deleted_sql} {$user_id_sql} {$sender_sql} {$type_sql} {$search_sql} {$meta_query_sql['where']}";
$sql['misc'] = "GROUP BY m.thread_id ORDER BY date_sent DESC {$pag_sql}";
// get thread IDs
$thread_ids = $wpdb->get_results(implode(' ', $sql));
if (empty($thread_ids)) {
return false;
}
// adjust $sql to work for thread total
$sql['select'] = 'SELECT COUNT( DISTINCT m.thread_id )';
unset($sql['misc']);
$total_threads = $wpdb->get_var(implode(' ', $sql));
// Sort threads by date_sent
foreach ((array) $thread_ids as $thread) {
$sorted_threads[$thread->thread_id] = strtotime($thread->date_sent);
}
arsort($sorted_threads);
$threads = array();
foreach ((array) $sorted_threads as $thread_id => $date_sent) {
$threads[] = new BP_Messages_Thread($thread_id, 'ASC', array('update_meta_cache' => false));
}
/**
* Filters the results of the query for a user's message threads.
*
* @since 2.2.0
*
* @param array $value {
* @type array $threads Array of threads. Passed by reference.
* @type int $total_threads Number of threads found by the query.
* }
*/
return apply_filters('bp_messages_thread_current_threads', array('threads' => &$threads, 'total' => (int) $total_threads));
}
/**
* Get the IDs of the groups of which a specified user is a moderator.
*
* @param int $user_id ID of the user.
* @param int|bool $limit Optional. Max number of results to return.
* Default: false (no limit).
* @param int|bool $page Optional. Page offset of results to return.
* Default: false (no limit).
* @param string|bool $filter Optional. Limit results to groups whose name or
* description field matches search terms.
*
* @return array {
* @type array $groups Array of groups returned by paginated query.
* @type int $total Count of groups matching query.
* }
*/
public static function get_is_mod_of($user_id, $limit = false, $page = false, $filter = false)
{
global $wpdb;
$user_id_sql = $pag_sql = $hidden_sql = $filter_sql = '';
$user_id_sql = $wpdb->prepare('m.user_id = %d', $user_id);
if (!empty($limit) && !empty($page)) {
$pag_sql = $wpdb->prepare(" LIMIT %d, %d", intval(($page - 1) * $limit), intval($limit));
}
if (!empty($filter)) {
$search_terms_like = '%' . bp_esc_like($filter) . '%';
$filter_sql = $wpdb->prepare(" AND ( g.name LIKE %s OR g.description LIKE %s )", $search_terms_like, $search_terms_like);
}
if ($user_id != bp_loggedin_user_id()) {
$hidden_sql = " AND g.status != 'hidden'";
}
$bp = buddypress();
$paged_groups = $wpdb->get_results("SELECT g.*, gm1.meta_value as total_member_count, gm2.meta_value as last_activity FROM {$bp->groups->table_name_groupmeta} gm1, {$bp->groups->table_name_groupmeta} gm2, {$bp->groups->table_name_members} m, {$bp->groups->table_name} g WHERE g.id = m.group_id AND g.id = gm1.group_id AND g.id = gm2.group_id AND gm2.meta_key = 'last_activity' AND gm1.meta_key = 'total_member_count'{$hidden_sql}{$filter_sql} AND {$user_id_sql} AND m.is_confirmed = 1 AND m.is_banned = 0 AND m.is_mod = 1 ORDER BY m.date_modified ASC {$pag_sql}");
$total_groups = $wpdb->get_var("SELECT COUNT(DISTINCT m.group_id) FROM {$bp->groups->table_name_members} m, {$bp->groups->table_name} g WHERE m.group_id = g.id{$hidden_sql}{$filter_sql} AND {$user_id_sql} AND m.is_confirmed = 1 AND m.is_banned = 0 AND m.is_mod = 1 ORDER BY date_modified ASC");
return array('groups' => $paged_groups, 'total' => $total_groups);
}
/**
* When search_terms are passed to BP_User_Query, search against xprofile fields.
*
* @since BuddyPress (2.0.0)
*
* @param array $sql Clauses in the user_id SQL query.
* @param BP_User_Query User query object.
*/
function bp_xprofile_bp_user_query_search($sql, BP_User_Query $query)
{
global $wpdb;
if (empty($query->query_vars['search_terms']) || empty($sql['where']['search'])) {
return $sql;
}
$bp = buddypress();
$search_terms_clean = bp_esc_like(wp_kses_normalize_entities($query->query_vars['search_terms']));
if ($query->query_vars['search_wildcard'] === 'left') {
$search_terms_nospace = '%' . $search_terms_clean;
$search_terms_space = '%' . $search_terms_clean . ' %';
} elseif ($query->query_vars['search_wildcard'] === 'right') {
$search_terms_nospace = $search_terms_clean . '%';
$search_terms_space = '% ' . $search_terms_clean . '%';
} else {
$search_terms_nospace = '%' . $search_terms_clean . '%';
$search_terms_space = '%' . $search_terms_clean . '%';
}
// Combine the core search (against wp_users) into a single OR clause
// with the xprofile_data search
$search_xprofile = $wpdb->prepare("u.{$query->uid_name} IN ( SELECT user_id FROM {$bp->profile->table_name_data} WHERE value LIKE %s OR value LIKE %s )", $search_terms_nospace, $search_terms_space);
$search_core = $sql['where']['search'];
$search_combined = "( {$search_xprofile} OR {$search_core} )";
$sql['where']['search'] = $search_combined;
return $sql;
}
/**
* Get current message threads for a user.
*
* @since BuddyPress (1.0.0)
*
* @param int $user_id The user ID.
* @param string $box The type of mailbox to get. Either 'inbox' or 'sentbox'.
* Defaults to 'inbox'.
* @param string $type The type of messages to get. Either 'all' or 'unread'
* or 'read'. Defaults to 'all'.
* @param int $limit The number of messages to get. Defaults to null.
* @param int $page The page number to get. Defaults to null.
* @param string $search_terms The search term to use. Defaults to ''.
* @return array|bool Array on success. Boolean false on failure.
*/
public static function get_current_threads_for_user($user_id, $box = 'inbox', $type = 'all', $limit = null, $page = null, $search_terms = '')
{
global $wpdb, $bp;
$user_id_sql = $pag_sql = $type_sql = $search_sql = '';
if ($limit && $page) {
$pag_sql = $wpdb->prepare(" LIMIT %d, %d", intval(($page - 1) * $limit), intval($limit));
}
if ($type == 'unread') {
$type_sql = " AND r.unread_count != 0 ";
} elseif ($type == 'read') {
$type_sql = " AND r.unread_count = 0 ";
}
if (!empty($search_terms)) {
$search_terms_like = '%' . bp_esc_like($search_terms) . '%';
$search_sql = $wpdb->prepare("AND ( subject LIKE %s OR message LIKE %s )", $search_terms_like, $search_terms_like);
}
if ('sentbox' == $box) {
$user_id_sql = $wpdb->prepare('m.sender_id = %d', $user_id);
$thread_ids = $wpdb->get_results("SELECT m.thread_id, MAX(m.date_sent) AS date_sent FROM {$bp->messages->table_name_recipients} r, {$bp->messages->table_name_messages} m WHERE m.thread_id = r.thread_id AND m.sender_id = r.user_id AND {$user_id_sql} AND r.is_deleted = 0 {$search_sql} GROUP BY m.thread_id ORDER BY date_sent DESC {$pag_sql}");
$total_threads = $wpdb->get_var("SELECT COUNT( DISTINCT m.thread_id ) FROM {$bp->messages->table_name_recipients} r, {$bp->messages->table_name_messages} m WHERE m.thread_id = r.thread_id AND m.sender_id = r.user_id AND {$user_id_sql} AND r.is_deleted = 0 {$search_sql} ");
} else {
$user_id_sql = $wpdb->prepare('r.user_id = %d', $user_id);
$thread_ids = $wpdb->get_results("SELECT m.thread_id, MAX(m.date_sent) AS date_sent FROM {$bp->messages->table_name_recipients} r, {$bp->messages->table_name_messages} m WHERE m.thread_id = r.thread_id AND r.is_deleted = 0 AND {$user_id_sql} AND r.sender_only = 0 {$type_sql} {$search_sql} GROUP BY m.thread_id ORDER BY date_sent DESC {$pag_sql}");
$total_threads = $wpdb->get_var("SELECT COUNT( DISTINCT m.thread_id ) FROM {$bp->messages->table_name_recipients} r, {$bp->messages->table_name_messages} m WHERE m.thread_id = r.thread_id AND r.is_deleted = 0 AND {$user_id_sql} AND r.sender_only = 0 {$type_sql} {$search_sql}");
}
if (empty($thread_ids)) {
return false;
}
// Sort threads by date_sent
foreach ((array) $thread_ids as $thread) {
$sorted_threads[$thread->thread_id] = strtotime($thread->date_sent);
}
arsort($sorted_threads);
$threads = false;
foreach ((array) $sorted_threads as $thread_id => $date_sent) {
$threads[] = new BP_Messages_Thread($thread_id);
}
return array('threads' => &$threads, 'total' => (int) $total_threads);
}
