Changeset 9331

Timestamp:

01/09/2015 07:24:02 PM (10 years ago)

Author:

r-a-y

Message:

Messages: Do not duplicate SQL statements in BP_Messages_Thread::get_current_threads_for_user()

Previously, to query the thread IDs and the total threads, two separate SQL
statements were generated. Both of these statements are almost identical.
Therefore, this commit uses an array to house the SQL clauses, which is
later adjusted in the total thread query.

See #6063.

File:

• trunk/src/bp-messages/bp-messages-classes.php (modified) (2 diffs)

trunk/src/bp-messages/bp-messages-classes.php

@@ -364 +364 @@
         $r = wp_parse_args( $args, $defaults );
 
-        $pag_sql = $type_sql = $search_sql = '';
+        $pag_sql = $type_sql = $search_sql = $user_id_sql = $sender_sql = '';
 
         if ( $r['limit'] && $r['page'] ) {
@@ -381 +381 @@
         }
 
-        if ( 'sentbox' == $r['box'] ) {
-            $user_id_sql = $wpdb->prepare( 'm.sender_id = %d', $r['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', $r['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( $r['user_id'] ) ) {
+            if ( 'sentbox' == $r['box'] ) {
+                $user_id_sql = 'AND ' . $wpdb->prepare( 'm.sender_id = %d', $r['user_id'] );
+                $sender_sql  = ' AND m.sender_id = r.user_id';
+            } else {
+                $user_id_sql = 'AND ' . $wpdb->prepare( 'r.user_id = %d', $r['user_id'] );
+                $sender_sql  = ' AND r.sender_only = 0';
+            }
+        }
+
+        // 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, {$bp->messages->table_name_messages} m";
+        $sql['where']  = "WHERE m.thread_id = r.thread_id AND r.is_deleted = 0 {$user_id_sql} {$sender_sql} {$type_sql} {$search_sql}";
+        $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