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

Timestamp:

01/09/2015 04:06:51 PM (12 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();