Skip to:
Content

BuddyPress.org

Changeset 7765


Ignore:
Timestamp:
01/29/2014 05:16:39 PM (8 years ago)
Author:
johnjamesjacoby
Message:

Split primary Activity query up into separate, more performant queries. Introduces BP_Activity_Activity::get_activity_data() method to help with converting data for template output. Props boonebgorges. Fixes #5349.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/bp-activity/bp-activity-classes.php

    r7680 r7765  
    293293
    294294        // Select conditions
    295         $select_sql = "SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name";
    296 
    297         $from_sql = " FROM {$bp->activity->table_name} a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
    298 
    299         $join_sql = '';
     295        $select_sql = "SELECT DISTINCT a.id";
     296
     297        $from_sql   = " FROM {$bp->activity->table_name} a";
     298
     299        $join_sql   = '';
    300300
    301301        // Where conditions
     
    378378        }
    379379
    380         if ( !empty( $per_page ) && !empty( $page ) ) {
    381 
    382             // Make sure page values are absolute integers
    383             $page     = absint( $page     );
    384             $per_page = absint( $per_page );
    385 
    386             $pag_sql    = $wpdb->prepare( "LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page );
    387             $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 ) );
     380        // Sanitize page and per_page parameters
     381        $page     = absint( $page     );
     382        $per_page = absint( $per_page );
     383
     384        // Filter and return true to use the legacy query structure (not recommended)
     385        if ( apply_filters( 'bp_use_legacy_activity_query', false, __METHOD__, $r ) ) {
     386
     387            // Legacy queries joined against the user table
     388            $select_sql = "SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name";
     389            $from_sql   = " FROM {$bp->activity->table_name} a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
     390
     391            if ( ! empty( $page ) && ! empty( $per_page ) ) {
     392                $pag_sql    = $wpdb->prepare( "LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page );
     393                $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 ) );
     394            } else {
     395                $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 ) );
     396            }
     397
    388398        } else {
    389             $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 ) );
     399
     400            // Query first for activity IDs
     401            $activity_ids_sql = "{$select_sql} {$from_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}";
     402
     403            if ( ! empty( $per_page ) && ! empty( $page ) ) {
     404                $activity_ids_sql .= $wpdb->prepare( " LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page );
     405            }
     406
     407            $activity_ids = $wpdb->get_col( $activity_ids_sql );
     408            $activities   = self::get_activity_data( $activity_ids );
    390409        }
    391410
    392411        $total_activities_sql = apply_filters( 'bp_activity_total_activities_sql', "SELECT count(DISTINCT a.id) FROM {$bp->activity->table_name} a {$index_hint_sql} {$join_sql} {$where_sql} ORDER BY a.date_recorded {$sort}", $where_sql, $sort );
    393 
    394         $total_activities = $wpdb->get_var( $total_activities_sql );
     412        $total_activities     = $wpdb->get_var( $total_activities_sql );
    395413
    396414        // Get the fullnames of users so we don't have to query in the loop
     
    401419            if ( !empty( $activity_user_ids ) ) {
    402420                if ( $names = $wpdb->get_results( "SELECT user_id, value AS user_fullname FROM {$bp->profile->table_name_data} WHERE field_id = 1 AND user_id IN ({$activity_user_ids})" ) ) {
    403                     foreach ( (array) $names as $name )
     421
     422                    foreach ( (array) $names as $name ) {
    404423                        $tmp_names[$name->user_id] = $name->user_fullname;
     424                    }
    405425
    406426                    foreach ( (array) $activities as $i => $activity ) {
    407                         if ( !empty( $tmp_names[$activity->user_id] ) )
     427                        if ( !empty( $tmp_names[$activity->user_id] ) ) {
    408428                            $activities[$i]->user_fullname = $tmp_names[$activity->user_id];
     429                        }
    409430                    }
    410431
     
    435456
    436457        return array( 'activities' => $activities, 'total' => (int) $total_activities );
     458    }
     459
     460    /**
     461     * Convert activity IDs to activity objects, as expected in template loop.
     462     *
     463     * @since 2.0
     464     *
     465     * @param array $activity_ids Array of activity IDs.
     466     * @return array
     467     */
     468    protected static function get_activity_data( $activity_ids = array() ) {
     469        global $wpdb;
     470
     471        // Bail if no activity ID's passed
     472        if ( empty( $activity_ids ) ) {
     473            return array();
     474        }
     475
     476        // Get BuddyPress
     477        $bp = buddypress();
     478
     479        // Format the activity ID's for use in the query below
     480        $activity_ids_sql = implode( ',', wp_parse_id_list( $activity_ids ) );
     481
     482        // First fetch data from activity table, preserving order
     483        $activities = $wpdb->get_results( "SELECT * FROM {$bp->activity->table_name} WHERE id IN ({$activity_ids_sql}) ORDER BY FIELD( id, {$activity_ids_sql} )");
     484
     485        // Then fetch user data
     486        $user_query = new BP_User_Query( array(
     487            'user_ids'        => wp_list_pluck( $activities, 'user_id' ),
     488            'populate_extras' => false,
     489        ) );
     490
     491        // Associated located user data with activity items
     492        foreach ( $activities as $a_index => $a_item ) {
     493            $a_user_id = intval( $a_item->user_id );
     494            $a_user    = isset( $user_query->results[ $a_user_id ] ) ? $user_query->results[ $a_user_id ] : '';
     495
     496            if ( !empty( $a_user ) ) {
     497                $activities[ $a_index ]->user_email    = $a_user->user_email;
     498                $activities[ $a_index ]->user_nicename = $a_user->user_nicename;
     499                $activities[ $a_index ]->user_login    = $a_user->user_login;
     500                $activities[ $a_index ]->display_name  = $a_user->display_name;
     501            }
     502        }
     503
     504        return $activities;
    437505    }
    438506
Note: See TracChangeset for help on using the changeset viewer.