Changeset 9328 for trunk/src/bp-blogs/bp-blogs-classes.php

Timestamp:

01/09/2015 04:06:51 PM (11 years ago)

Author:

boonebgorges

Message:

Search consistently against blog title and description in BP_Blogs_Blog::get().

Previously, the description field was only being matched for the 'total' query,
with the result that pagination and other count-based results did not match the
blogs actually returned by the query.

This changeset reworks the syntax a bit, to avoid the duplicated logic when
using search terms, and to use proper JOIN syntax instead of commas.

Props jreeve.
Fixes #5858.

File:

• trunk/src/bp-blogs/bp-blogs-classes.php (modified) (1 diff)

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

@@ -174 +174 @@
         }
 
-        if ( !empty( $search_terms ) ) {
+        if ( ! empty( $search_terms ) ) {
             $search_terms_like = '%' . bp_esc_like( $search_terms ) . '%';
-            $search_terms_sql  = $wpdb->prepare( 'bm2.meta_value LIKE %s', $search_terms_like );
-            $paged_blogs = $wpdb->get_results( "SELECT b.blog_id, b.user_id as admin_user_id, u.user_email as admin_user_email, wb.domain, wb.path, bm.meta_value as last_activity, bm2.meta_value as name FROM {$bp->blogs->table_name} b, {$bp->blogs->table_name_blogmeta} bm, {$bp->blogs->table_name_blogmeta} bm2, {$wpdb->base_prefix}blogs wb, {$wpdb->users} u WHERE b.blog_id = wb.blog_id AND b.user_id = u.ID AND b.blog_id = bm.blog_id AND b.blog_id = bm2.blog_id AND wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$hidden_sql} AND bm.meta_key = 'last_activity' AND bm2.meta_key = 'name' AND {$search_terms_sql} {$user_sql} {$include_sql} GROUP BY b.blog_id {$order_sql} {$pag_sql}" );
-            $total_blogs = $wpdb->get_var( "SELECT COUNT(DISTINCT b.blog_id) FROM {$bp->blogs->table_name} b, {$wpdb->base_prefix}blogs wb, {$bp->blogs->table_name_blogmeta} bm, {$bp->blogs->table_name_blogmeta} bm2 WHERE b.blog_id = wb.blog_id AND bm.blog_id = b.blog_id AND bm2.blog_id = b.blog_id AND wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$hidden_sql} AND bm.meta_key = 'name' AND bm2.meta_key = 'description' AND {$search_terms_sql} {$user_sql} {$include_sql}" );
+            $search_terms_sql  = $wpdb->prepare( 'AND (bm_name.meta_value LIKE %s OR bm_description.meta_value LIKE %s)', $search_terms_like, $search_terms_like );
         } else {
-            $paged_blogs = $wpdb->get_results( "SELECT b.blog_id, b.user_id as admin_user_id, u.user_email as admin_user_email, wb.domain, wb.path, bm.meta_value as last_activity, bm2.meta_value as name FROM {$bp->blogs->table_name} b, {$bp->blogs->table_name_blogmeta} bm, {$bp->blogs->table_name_blogmeta} bm2, {$wpdb->base_prefix}blogs wb, {$wpdb->users} u WHERE b.blog_id = wb.blog_id AND b.user_id = u.ID AND b.blog_id = bm.blog_id AND b.blog_id = bm2.blog_id {$user_sql} AND wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$hidden_sql} AND bm.meta_key = 'last_activity' AND bm2.meta_key = 'name' {$include_sql} GROUP BY b.blog_id {$order_sql} {$pag_sql}" );
-            $total_blogs = $wpdb->get_var( "SELECT COUNT(DISTINCT b.blog_id) FROM {$bp->blogs->table_name} b, {$wpdb->base_prefix}blogs wb WHERE b.blog_id = wb.blog_id {$user_sql} AND wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$include_sql} {$hidden_sql}" );
-        }
+            $search_terms_sql = '';
+        }
+
+        $paged_blogs = $wpdb->get_results( "
+            SELECT b.blog_id, b.user_id as admin_user_id, u.user_email as admin_user_email, wb.domain, wb.path, bm.meta_value as last_activity, bm_name.meta_value as name
+            FROM
+              {$bp->blogs->table_name} b
+              LEFT JOIN {$bp->blogs->table_name_blogmeta} bm ON (b.blog_id = bm.blog_id)
+              LEFT JOIN {$bp->blogs->table_name_blogmeta} bm_name ON (b.blog_id = bm_name.blog_id)
+              LEFT JOIN {$bp->blogs->table_name_blogmeta} bm_description ON (b.blog_id = bm_description.blog_id)
+              LEFT JOIN {$wpdb->base_prefix}blogs wb ON (b.blog_id = wb.blog_id)
+              LEFT JOIN {$wpdb->users} u ON (b.user_id = u.ID)
+            WHERE
+              wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$hidden_sql}
+              AND bm.meta_key = 'last_activity' AND bm_name.meta_key = 'name' AND bm_description.meta_key = 'description'
+              {$search_terms_sql} {$user_sql} {$include_sql}
+            GROUP BY b.blog_id {$order_sql} {$pag_sql}
+        " );
+
+        $total_blogs = $wpdb->get_var( "
+            SELECT COUNT(DISTINCT b.blog_id)
+            FROM
+              {$bp->blogs->table_name} b
+              LEFT JOIN {$wpdb->base_prefix}blogs wb ON (b.blog_id = wb.blog_id)
+              LEFT JOIN {$bp->blogs->table_name_blogmeta} bm_name ON (b.blog_id = bm_name.blog_id)
+              LEFT JOIN {$bp->blogs->table_name_blogmeta} bm_description ON (b.blog_id = bm_description.blog_id)
+            WHERE
+              wb.archived = '0' AND wb.spam = 0 AND wb.mature = 0 AND wb.deleted = 0 {$hidden_sql}
+              AND
+              bm_name.meta_key = 'name' AND bm_description.meta_key = 'description'
+              {$search_terms_sql} {$user_sql} {$include_sql}
+        " );
 
         $blog_ids = array();