Changeset 7765

Timestamp:

01/29/2014 05:16:39 PM (13 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:

• trunk/bp-activity/bp-activity-classes.php (modified) (4 diffs)

trunk/bp-activity/bp-activity-classes.php

@@ -293 +293 @@
 
                 // Select conditions
-                $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";
-
-                $join_sql = '';
+                $select_sql = "SELECT DISTINCT a.id";
+
+                $from_sql   = " FROM {$bp->activity->table_name} a";
+
+                $join_sql   = '';
 
                 // Where conditions
@@ -378 +378 @@
                 }
 
-                if ( !empty( $per_page ) && !empty( $page ) ) {
-
-                        // Make sure page values are absolute integers
-                        $page     = absint( $page     );
-                        $per_page = absint( $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 ) );
+                // Sanitize page and per_page parameters
+                $page     = absint( $page     );
+                $per_page = absint( $per_page );
+
+                // 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 {
-                        $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 ) );
+
+                        // 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 ) ) {
+                                $activity_ids_sql .= $wpdb->prepare( " LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page );
+                        }
+
+                        $activity_ids = $wpdb->get_col( $activity_ids_sql );
+                        $activities   = self::get_activity_data( $activity_ids );
                 }
 
                 $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 );
-
-                $total_activities = $wpdb->get_var( $total_activities_sql );
+                $total_activities     = $wpdb->get_var( $total_activities_sql );
 
                 // Get the fullnames of users so we don't have to query in the loop
@@ -401 +419 @@
                         if ( !empty( $activity_user_ids ) ) {
                                 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})" ) ) {
-                                        foreach ( (array) $names as $name )
+
+                                        foreach ( (array) $names as $name ) {
                                                 $tmp_names[$name->user_id] = $name->user_fullname;
+                                        }
 
                                         foreach ( (array) $activities as $i => $activity ) {
-                                                if ( !empty( $tmp_names[$activity->user_id] ) )
+                                                if ( !empty( $tmp_names[$activity->user_id] ) ) {
                                                         $activities[$i]->user_fullname = $tmp_names[$activity->user_id];
+                                                }
                                         }
 
@@ -435 +456 @@
 
                 return array( 'activities' => $activities, 'total' => (int) $total_activities );
+        }
+
+        /**
+         * Convert activity IDs to activity objects, as expected in template loop.
+         *
+         * @since 2.0
+         *
+         * @param array $activity_ids Array of activity IDs.
+         * @return array
+         */
+        protected static function get_activity_data( $activity_ids = array() ) {
+                global $wpdb;
+
+                // Bail if no activity ID's passed
+                if ( empty( $activity_ids ) ) {
+                        return array();
+                }
+
+                // Get BuddyPress
+                $bp = buddypress();
+
+                // Format the activity ID's for use in the query below
+                $activity_ids_sql = implode( ',', wp_parse_id_list( $activity_ids ) );
+
+                // First fetch data from activity table, preserving order
+                $activities = $wpdb->get_results( "SELECT * FROM {$bp->activity->table_name} WHERE id IN ({$activity_ids_sql}) ORDER BY FIELD( id, {$activity_ids_sql} )");
+
+                // Then fetch user data
+                $user_query = new BP_User_Query( array(
+                        'user_ids'        => wp_list_pluck( $activities, 'user_id' ),
+                        'populate_extras' => false,
+                ) );
+
+                // Associated located user data with activity items
+                foreach ( $activities as $a_index => $a_item ) {
+                        $a_user_id = intval( $a_item->user_id );
+                        $a_user    = isset( $user_query->results[ $a_user_id ] ) ? $user_query->results[ $a_user_id ] : '';
+
+                        if ( !empty( $a_user ) ) {
+                                $activities[ $a_index ]->user_email    = $a_user->user_email;
+                                $activities[ $a_index ]->user_nicename = $a_user->user_nicename;
+                                $activities[ $a_index ]->user_login    = $a_user->user_login;
+                                $activities[ $a_index ]->display_name  = $a_user->display_name;
+                        }
+                }
+
+                return $activities;
         }